From last two days I am facing three problems. I am using SQL server 2000 as backend and VB 6.0 as front end. I am using custom made controls like textbox, date control and combo box.
I am sending sections of code where I am facing problem. So please help me.1. Error message is coming while using recordset, the error message is "Current recordset doesn't support updating. This may be a limitation of the provider or of the selected locktype. "
2. When I try to add some value in a particular field of a table in database through recordset, while fetching that value through recordset it is coming with already done addition operation. So final result is twice the value added in a field.
i.e. if I try to add 5, then 10 get added to that field.
Lines of code for question 2:-
'''INSERT STOCK DATA INTO Inv_ItemMaster TABLE
For iCount = 1 To lvwInspection.ListItems.Count
If lvwInspection.ListItems(iCount).Checked = True Then
Set rsGRN = New ADODB.Recordset
strSql = "Select * from Inv_ItemMaster Where ItemCode='" & lvwInspection.ListItems(iCount).ListSubItems(1).Text & "' and CompanyId=" & iCompanyId
rsGRN.Open strSql, con, adOpenForwardOnly, adLockOptimistic
Dim stock As Double
stock = rsGRN!ItemStock 'here value of rsGRN!ItemStock is wrong.
'Error without adding actual qty in stock,, it get's added
'rsGRN!ItemStock = stock + Val(lvwInspection.ListItems(iCount).ListSubItems(7).Text)
rsGRN!ItemStock = stock
Set rsGRN = Nothing
3.Some lines of codes which I wrote some days back, is invisible now in a particular function, but it's implications are still reflecting on database.
There were no use of triggers or any such mechanisms of database.
Lines of code for question 3:-
'Logic for getting fifo rate.
Dim dbRemainderQty As Double
Dim dbFifoRate As Double
Dim iFlag As Integer
Dim rsTemp As ADODB.Recordset
dbRemainderQty = Val(lvwIssueDetails.ListItems(iLstCount).SubItems(6))
While iFlag = 0
Set rsTemp = New ADODB.Recordset
strSql = "Select Quantity,BasicRate From Inv_FifoRates " & _
"Where (TransactionMoment = " & _
"(SELECT MIN(TransactionMoment) From " & _
"Inv_FifoRates where ItemCode = '" & lvwIssueDetails.ListItems(iLstCount).SubItems(1) & "' and Quantity <> 0)) " & _
"AND (ItemCode = '" & lvwIssueDetails.ListItems(iLstCount).SubItems(1) & "') " & _
"AND (CompanyId = " & iCompanyId & ")"
rsTemp.Open strSql, con, adOpenDynamic, adLockOptimistic
If dbRemainderQty <= Val(rsTemp!Quantity) Then
dbFifoRate = dbFifoRate + (dbRemainderQty * Val(rsTemp!basicRate))
rsTemp!Quantity = Val(rsTemp!Quantity) - dbRemainderQty
iFlag = 1
ElseIf dbRemainderQty > Val(rsTemp!Quantity) Then
dbRemainderQty = dbRemainderQty - rsTemp!Quantity
dbFifoRate = dbFifoRate + (Val(rsTemp!Quantity) * Val(rsTemp!basicRate))
rsTemp!Quantity = 0
Set rsTemp = Nothing