Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Question Unanswered: Help:key column info is insufficient or incorrect. Too many rows were updated

    Code:
    Dim strDatePoint As String, strJO As String
        Dim strID As String
        Dim nUPrice As Double
        Dim nIn As Integer
        
        strDatePoint = Format("12/31/2009", "mm/dd/yyyy")
        dbConnect
        rsInv.Open "Select * from items", dbConn, adOpenForwardOnly, adLockPessimistic
        If rsInv.RecordCount > 0 Then
            rsInv.MoveFirst
            Do While Not rsInv.EOF
                dbConnectArchive
                    rsInvArchive.Open "Insert into items (iteID,iteName,iteClass,iteUnit,iteEncoder) Values ('" _
                    & (rsInv!iteID) & "','" & (rsInv!iteName) & "','" _
                    & (rsInv!iteClass) & "','" & (rsInv!iteUnit) & "','" _
                    & (rsInv!iteEncoder) & "')", dbConnArchive, adOpenStatic, adLockPessimistic
                dbCloseArchive
                rsInv.MoveNext
            Loop
        End If
        
        'opens records for acquisition
        Set rsInv = New ADODB.Recordset
        rsInv.Open "Select * from acquisition where acqDate <='" & strDatePoint & "' and isProcess = 0", dbConn, adOpenForwardOnly, adLockPessimistic
        If rsInv.RecordCount > 0 Then
            rsInv.MoveFirst
            Do While Not rsInv.EOF
                'transfer data into acquisition
                dbConnectArchive
                strID = rsInv!acqID
                nUPrice = CDbl(rsInv!acqUPrice)
                
                Set rsInvArchive = New ADODB.Recordset
                rsInvArchive.Open "Insert Into acquisition (acqID,acqItemID,acqInvoice,acqPO,acqColor,acqSize,acqUPrice,acqIn,acqOut,acqDate,acqStorage,acqSupplier,acqEncoder,acqReturned,isReturn,acqOrderID,isRetSupplier,acqRetSupplier,acqRetDate) VALUES ('" _
                    & (strID) & "','" & (rsInv!acqItemID) & "','" _
                    & (rsInv!acqInvoice) & "','" & (rsInv!acqPO) & "','" _
                    & (rsInv!acqColor) & "','" & (rsInv!acqSize) & "','" _
                    & (nUPrice) & "','" & (rsInv!acqIn) & "','" _
                    & (rsInv!acqOut) & "','" & (rsInv!acqDate) & "','" _
                    & (rsInv!acqStorage) & "','" & (rsInv!acqSupplier) & "','" _
                    & (rsInv!acqEncoder) & "','" & (rsInv!acqReturned) & "','" _
                    & (rsInv!isReturn) & "','" & (rsInv!acqOrderID) & "','" _
                    & (rsInv!isRetSupplier) & "','" & (rsInv!acqRetSupplier) & "','" _
                    & (rsInv!acqRetDate) & "')", dbConnArchive, adOpenStatic, adLockPessimistic
                dbCloseArchive
                
                nIn = rsInv!acqIn - (rsInv!acqOut + rsInv!acqRetSupplier)
                If nIn = 0 Then
                    Set rsInv = New ADODB.Recordset
                    rsInv.Open "Delete from acquisition where acqID='" & strID & "'", dbConn, adOpenForwardOnly, adLockPessimistic
                Else
                    key column problem here...
                    Set rsInv = New ADODB.Recordset
                    rsInv.Open "Select acqIn,acqOut,isRetSupplier,acqRetSupplier, isReturn,acqReturned,isProcess from acquisition where acqID ='" & strID & "'", dbConn, adOpenForwardOnly, adLockPessimistic
                        rsInv!acqIn = nInhttp://www.dbforums.com/db_images_v3/images/smilies/beer.gif
                        rsInv!acqOut = 0
                        rsInv!isRetSupplier = 0
                        rsInv!acqRetSupplier = 0
                        rsInv!isReturn = 0
                        rsInv!acqReturned = 0
                        rsInv!isProcess = 1
                        rsInv.UpdateBatch
                End If
                rsInv.MoveNext
            Loop
        End If
        dbClose
    End Sub
    Last edited by loquin; 02-09-09 at 19:16. Reason: add [code] [/code] tags

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    So...

    Is there a question anywhere?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

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