executing sql statement with a non closed executereader.

[arnoldl]

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.

        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

[dglienna]

rd2 or tracecom2 ?

[DataMiser]

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.

[Shuja Ali]

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.

[arnoldl]

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.