-
August 4th, 2009, 05:09 PM
#1
executing sql statement with a non closed executereader.
hello all,
i have app that connects to a mysql db.
i get data from the db with executereader.
but i want to execute some sql depending on the result from a other db.
but i get a error when doing ExecuteNonQuery() when the reader is still open.
what's the best solution for this...
Here's a part of my code where the problem occures.
Code:
tracecom.CommandText = "select * from testsms left join customer on customer.id=testsms.id where date_sub(now(),interval 1 minute) > testsend"
tracerd = tracecom.ExecuteReader()
If tracerd.HasRows Then
While tracerd.Read
If tracerd("retries") = 0 Then
'first time resend message
'****
addlog(Now() + " Resent test sms for: " + tracerd("login"))
tracecom.CommandText = "UPDATE testsms SET retries=retries+1 Where id=" + CStr(tracerd("id"))
tracecom.ExecuteNonQuery()
Else
'second time offender
'**** send owner a message about the fail.
addlog(Now() + " Fail on testsms for : " + tracerd("login"))
tracecom.CommandText = "UPDATE customer SET lasttestresult=0 Where id=" + CStr(tracerd("id"))
tracecom.ExecuteNonQuery()
tracecom.CommandText = "DELETE FROM testsms WHERE id=" + CInt(tracerd("id"))
tracecom.ExecuteNonQuery()
End If
End While
End If
tracerd.Close()
thans
-
August 4th, 2009, 09:09 PM
#2
Re: executing sql statement with a non closed executereader.
-
August 5th, 2009, 08:27 AM
#3
Re: executing sql statement with a non closed executereader.
Have you tried using 2 objects? One for the reader and a different one for you updates or whatever sql you want to execute? It seems like the error is likely due to the fact that both your reader and your sql commands are working on the tracecom object.
-
August 5th, 2009, 02:00 PM
#4
Re: executing sql statement with a non closed executereader.
As MARS (Multiple Active Resultsets) is not available with MYSQl, you will have to use two different connections to perform an operation while a datareader is open. or else you will first have to close the datareader and then execute another query.
-
August 5th, 2009, 02:37 PM
#5
Re: executing sql statement with a non closed executereader.
ok thanks.
will use a second connection for the updates..
i was thinking it would be possible within one connection...
but no prob then..
thanks again.
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
|