Smith Smith - 3 months ago 11
MySQL Question

.net exception There is already an open DataReader associated with this Connection which must be closed first

I am trying to save multiple rows of data using mysql transaction, but I get an error when i execute this code at the indicated line

Using conn As New MySqlConnection(connStr)
conn.Open()

Using sqlTrans As MySqlTransaction = conn.BeginTransaction()

Try
Dim bHasRows As Boolean
dim sSql as string

For Each kv As KeyValuePair(Of String, String) In dicOpts
If String.IsNullOrEmpty(kv.Key) Then Continue For
String.format("Select * FROM Options WHERE name = '{0}';",kv.Key)
Using cmd As New MySqlCommand(qb.GetQuery, conn)
bHasRows = cmd.ExecuteReader().HasRows
End Using

If bHasRows Then
ssql=String.format("UPDATE Options SET value ='{0}' WHERE name = '{1}';", kv.Value,kv.Key)
Else
ssql=String.format("INSERT INTO Options(name,Value) values('{0}','{1}';",kv.Key, kv.Value)
End If
Using cmd As New MySqlCommand(qb.GetQuery, conn)
cmd.ExecuteNonQuery() // <----- This is the error line
End Using
Next

sqlTrans.Commit()
Catch ex As Exception
sqlTrans.Rollback()
Throw
Finally

End Try
End Using


The error i am getting


There is already an open DataReader associated with this Connection
which must be closed first


As you can see, the reader is closed with every using

Answer

Here is where you step wrong:

bHasRows = cmd.ExecuteReader().HasRows

use Using:

Using dr = cmd.ExecuteReader()
    bHasRows = dr.HasRows
End Using