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

---

Any comment guys? HELP!!!!

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?

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.

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?

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?

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.

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

Good catch M Owen. I missed that.

Thank you so much.:) :)