CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2002
    Location
    asian
    Posts
    116

    error during update method

    Im adding a record using the addnew method but when it reached the update method this error appeared,

    "Connection is busy with results for another command". What is the cause of this problem?

    This is my code:
    Code:
    Do While Not rst.EOF  ''' this is tblinv_det 
                        
     	‘’’’some more codes here
                    
                    Set rstCrdLimit = cnn.Execute(mCrdLimit)
                    mRTotal = (IIf(IsNull(rstCrdlimit!amount), 0, rstCrdlimit!amount) + rst!orders )
                    
                     
                    If rst!acct_no <> invalidacctno Then
                        Set rstcustomer = cnn.Execute("select * from customer where acct_no ='" & mAcctNo & "'")
                        If rstcustomer.EOF Then
                            mContact = rst!contactname
                            mNAME = rst!customername
                            mSTATE = rst!stete
                            mPostal = rst!postal
                       
                            
                        Else
                            mContact = rstcustomer!contactname
                            mNAME = rstcustomer!customername
                            mSTATE = rstcustomer!address1 & " " & rstcustomer!address2
                            mPostal = rstcustomer!postal
                            mCity = rstcustomer!city
                        End If
                    Else
                        mContact = rst!contactname
                        mNAME = rst!customername
                        mSTATE = rst!state
                        mPostal = rst!postal
                        mCity = rst!city
                            
                    End If
                    With Invoice  '''' this is the tblinv_head 
                            .AddNew
                            !ac_no = mAcctNo
                            !inv_no = mInvoice
                            !inv_Date = Date
                             .Update <<====== this is where the error  
                    End With
                   
                    cnn.Execute ("update tblinv_det set  field1= ......" )
                    cnn.Execute ("update tblinv_head set field1 = ....... ")
               
           rst.MoveNext
    Loop
    Thanks!!!
    Kristine

  2. #2
    Join Date
    Oct 2002
    Location
    asian
    Posts
    116

    need your help badly

    Any comment guys? HELP!!!!
    Thanks!!!
    Kristine

  3. #3
    Join Date
    Dec 2001
    Posts
    6,332
    What happens when the account number is not valid as opposed to valid? What are the two execute statements at the end? Does the error happen on the first time through the loop?

  4. #4
    Join Date
    Oct 2002
    Location
    asian
    Posts
    116
    i dont think the condition in the acct no is causing the problem. Yes, the error happened on the first run on the loop. I need the two statement to update the two tables invoice header and invoice details. Logic is like this, I need to generate a over payment invoice and the amount generated should be updated back to the original invoice. That is the use of the last execute statement.

    Thanks for the time.
    Thanks!!!
    Kristine

  5. #5
    Join Date
    Dec 2001
    Posts
    6,332
    Well, the IIF statement is not part of vbscript, so that can't work (if vbscript is what you are using). If you are doing any sort of error trapping or something, perhaps this has something to do with it. Not seeing the rest of the code, I can only guess. Other than that, my other guess would be if you are opening the database such that other changes can't occur, then maybe...

    Any other relavent code?
    Last edited by WizBang; January 26th, 2003 at 07:20 PM.
    Please remember to rate the posts and threads that you find useful.
    How can something be both new and improved at the same time?

  6. #6
    Join Date
    Oct 2002
    Location
    asian
    Posts
    116
    hi Im encountering the same error again." Connection is busy with result from another command".

    Below the exact code im using. the error occured after several pass on the do while loop

    Code:
    Private Sub Command1_Click()
    Dim cnn As ADODB.Connection
    Dim rstInvoice As ADODB.Recordset, rstPayment As ADODB.Recordset
    Dim rstAdj As ADODB.Recordset, rstsetup As ADODB.Recordset
    Dim rstinvcount As ADODB.Recordset
    
    Dim mctr As Long
    
    Set cnn = New ADODB.Connection
    cnn.Open "File name=c:\CONN.udl"
    cnn.BeginTrans
    
    Set rstInvoice = New ADODB.Recordset
    Set rstSETUP = New ADODB.Recordset
    
    rstsetup.Open "MISC", cnn, adOpenDynamic, adLockOptimistic
    rstInvoice.Open "select * from vwInvoice", cnn, adOpenDynamic, adLockOptimistic
    Set rstinvcount = cnn.Execute("Select count(*) as mtotal from vwInvoice")
    
    With ProgressBar1
        .Visible = True
        .Min = 1
        .Max = rstinvcount!mtotal
    End With
    Set rstinvcount = Nothing
    
    
    Do While Not rstInvoice.EOF
        mctr = mctr + 1
        ProgressBar1.Value = mctr
    
        Set rstPayment = cnn.Execute("select cdate from payment where invoice_no='" & rstInvoice!inv_no & "' order by cdate desc")
        If Not rstPayment.EOF Then
            If (Date - rstPayment!cdate) >= rstsetup!days Then
                Set rstPayment = Nothing
                cnn.Execute ("insert into dispute (acct_no,cust_name,inv_no,inv_Date,inv_amt,OS,adj_Date,amt)" & _
                            "values ('" & rstInvoice!ac_no & "','" & Replace(rstInvoice!bname, "'", "''") & "','" & rstInvoice!inv_no & "','" & rstInvoice!inv_Dt & "','" & rstInvoice!bamt & "'," & rstInvoice!os_amt & ",'" & Date & "'," & _
                            rstInvoice!os & "')")
                            
                rstInvoice!os = 0   '=====> THE ERROR APPEARED HERE
                rstInvoice.Update
            End If
        End If
        rstInvoice.MoveNext
    Loop
    end sub

    Any idea on how i can correct my code?
    Thanks!!!
    Kristine

  7. #7
    Join Date
    Dec 2001
    Posts
    6,332
    Well, my first impression is that there is nothing wrong with the code, but that the database driver can't keep up with the changes. You are saying it goes through the loop several times before the error, right? Does the error always happen on the same record, or with a record that always contains a certain value? I would try putting in a delay after each update, and see what happens.
    Please remember to rate the posts and threads that you find useful.
    How can something be both new and improved at the same time?

  8. #8
    Join Date
    Jun 2001
    Location
    Mi
    Posts
    1,249
    kristine,

    My impression of your code is that you instantiate the rstInvoice recordset and perform a loop mechanism on it right? The only thing missing I see is a MoveFirst call to actually logically be on the 1st record. Opening a recordset does not put you on the 1st record ... I think that is why your assignment and subsequent update blows chunks ...

    Something like:

    if rstInvoice.BOF=false then rstInvoice.MoveFirst
    Do While Not rstInvoice.EOF
    ...

    Try that 1st off ...

    - Mike

  9. #9
    Join Date
    Dec 2001
    Posts
    6,332
    Good catch M Owen. I missed that.
    Please remember to rate the posts and threads that you find useful.
    How can something be both new and improved at the same time?

  10. #10
    Join Date
    Oct 2002
    Location
    asian
    Posts
    116
    Thank you so much.
    Thanks!!!
    Kristine

Posting Permissions

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





Click Here to Expand Forum to Full Width

Featured