ThisIsABird ThisIsABird - 6 months ago 30
SQL Question

mysql vb.net There is already an open Data Reader associated with this connection

I've already added reader.dispose() but it's still showing the exception


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


Please don't mind the Sql injection. I'll be fixing that soon.

Private Sub ItemStore(ByVal itemname)
mydbcon = New MySqlConnection
mydbcon.ConnectionString = "server=localhost;userid=root;password=;database=sdudb"
Dim reader As MySqlDataReader
Try
mydbcon.Open()
Dim Query As String
Query = "select * from inventory where itemname = '" & itemname & "'"
COMMAND = New MySqlCommand(Query, mydbcon)
reader = COMMAND.ExecuteReader()
While reader.Read
AddItemstoDatabase(reader.GetString("itemcategory"), reader.GetString("itemname"), reader.GetString("price"), reader.GetString("stockcount"))
End While
reader.Close()
reader.Dispose()
mydbcon.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Private Sub AddItemstoDatabase(ByVal itemcategory, ByVal itemname, ByVal price, ByVal stock)
mydbcon = New MySqlConnection
mydbcon.ConnectionString = "server=localhost;userid=root;password=;database=sdudb"
Try
mydbcon.Open()
Dim Query As String
Query = "INSERT into itemstored(itemcategory, itemname, price, stockcount) VALUES('" & itemcategory & "', '" & itemname & "', '" & price & "', '" & stock & "')"
Dim rowsadded = COMMAND.ExecuteNonQuery()
If rowsadded = 0 Then
MessageBox.Show("Failed to update database")
End If
mydbcon.Close()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub

Answer

Change:

mydbcon = New MySqlConnection 

to:

Dim mydbcon = New MySqlConnection

and it will work.

You are using a globally declared connection variable there.
And as the error states, you can't have 2 data readers open in 1 connection at the same time.