imsome1 imsome1 - 4 years ago 539
Vb.net Question

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

I get this error message


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


Please help me

My code is:



Public Sub update_qty(ByVal qry1 As String)
Dim dr As MySqlDataReader 'SQLiteDataReader
Dim comm As MySqlCommand 'SQLiteCommand


Try

comm = New MySqlCommand(qry1, conn)
dr = comm.ExecuteReader()
Catch ex As Exception
MsgBox(ex.Message)
End Try

Do While dr.Read()
exe_query("call cargosys.paymentsAdd('" & var1 & "', " & dr("inNo") & ")")
Loop

dr.Close()
End Sub

Public Sub exe_query(ByVal qry As String) As String

Dim cmd As MySqlCommand

Try
cmd = New MySqlCommand(qry, conn)
cmd.ExecuteNonQuery()


Catch ex As MySqlException
MessageBox.Show(ex.ToString)
End Try

End Sub

Answer Source

Your problem is that your code open a DataReader and then execute the SqlCommand when the DataReader read

Try to change this line:

 dr = comm.ExecuteReader()

to:

 dr = comm.ExecuteReader(CommandBehavior.CloseConnection)

More: DataReader CommandBehavior

Or change your connection string to enable MARS (Multiple Active Result Sets). This setting will allow for the retrieval of multiple forward-only, read-only result sets on the same connection.

For example :

connectionString=
"Data Source=.\SQLEXPRESS;
AttachDbFilename=|DataDirectory|Northwind.MDF;
Integrated Security=True;
User Instance=True;
MultipleActiveResultSets=True"

More: MARS

EDIT

Since MARS keyword is not supported, try to change your code to this:

Public Sub update_qty(ByVal qry1 As String) 
        Dim dr As MySqlDataReader 'SQLiteDataReader 
        Dim comm As MySqlCommand 'SQLiteCommand 


        Try

            comm = New MySqlCommand(qry1, conn)
            dr = comm.ExecuteReader()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

        Dim myList As New List(Of String)

        Do While dr.Read()
            myList.Add("call cargosys.paymentsAdd('" & var1 & "', " & dr("inNo") & ")")
        Loop

        dr.Close()
End Sub

Public Sub exe_query(myList As List(Of String)) 

        Dim cmd As MySqlCommand 

        For Each query As String In myList
            Try
                cmd = New MySqlCommand(query, conn)
                cmd.ExecuteNonQuery()

            Catch ex As MySqlException
                MessageBox.Show(ex.ToString)      
            End Try
        Next

 End Sub

Instead to doing DataReader.Read->SqlCommand.ExecuteNonQuery simultaneously, this code will be read all the data first and then run SqlCommand.ExecuteNonQuery.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download