-
January 25th, 2003, 11:42 AM
#1
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
-
January 26th, 2003, 03:31 AM
#2
need your help badly
Any comment guys? HELP!!!!
Thanks!!!
Kristine
-
January 26th, 2003, 05:23 AM
#3
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?
-
January 26th, 2003, 09:59 AM
#4
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
-
January 26th, 2003, 07:17 PM
#5
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?
-
May 25th, 2003, 04:15 AM
#6
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
-
May 25th, 2003, 07:42 AM
#7
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?
-
May 25th, 2003, 08:47 AM
#8
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
-
May 25th, 2003, 08:58 AM
#9
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?
-
May 26th, 2003, 09:37 PM
#10
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|