Results 1 to 1 of 1
  1. #1
    Join Date
    Aug 2006
    Location
    Mumbai, India
    Posts
    26

    Unanswered: Invisible code, already evaluated recordset fields, recordset not supporting updation

    Hello All,
    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:-
    Code:
     '''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
                rsGRN.Update
                rsGRN.Close
                Set rsGRN = Nothing
            End If
        Next iCount
    '---------------------------------



    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:-
    Code:
    '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
                            End If
                            rsTemp.Update
                            rsTemp.Close
                            Set rsTemp = Nothing
                        Wend
    Last edited by loquin; 08-11-06 at 13:04.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •