in my if statement in the else part i have this error please tell me how to resolve it

Question

i am getting this error in my coding can you please tell me what might be wrong? and how to rectify it

There is already an open DataReader associated with this Connection which must be closed first. here in this sub i am retrieve data in the if part and in the else part i am inserting data. if a condition is not satisfied. so in the else part i am getting the above error

code is :

 Dim con As MySql.Data.MySqlClient.MySqlConnection = New MySqlClient.MySqlConnection("server=localhost;user=root;database=zzz;port=3306;password;")
    con.Open()
    Dim cmd As MySqlClient.MySqlCommand
    Dim dr As MySqlClient.MySqlDataReader
    cmd = con.CreateCommand()
    cmd.CommandText = "select sino FROM customers WHERE sino  =('" + serialno.Text + "')"
    cmd.ExecuteNonQuery()
    dr = cmd.ExecuteReader()
    If dr.HasRows = True Then
        MsgBox("number already exists")
        dr.Close()

    ElseIf dr.HasRows = False Then

        Dim sqlc As String
        sqlc = "insert into customers values('" + serialno.Text + "','" + custname.Text + "','" + address.Text + "','" + phno.Text + "','" + eid.Text + "','" + event_type.Text + " ')"
        Dim command As MySqlClient.MySqlCommand = New MySqlClient.MySqlCommand(sqlc, con)
        command.ExecuteNonQuery()
        MessageBox.Show(sqlc)
        con.Close()
    End If

I think a simple google search would've solved your problem http://stackoverflow.com/questions/5440168/c-sharp-mysql-there-is-already-an-open-datareader-associated-with-this-connectio — spullen, Mar 22 '13 at 15:06
I reccomend, just after resolving the problem with the DataReader, that you study immediately the Sql Injection problem. Your query has a huge security hole. — Steve, Mar 22 '13 at 15:19

score 2 · Accepted Answer · answered Mar 22 '13 at 15:09

2

A DataReader can only be associated with one open connection. When you are finished with the DataReader be sure you Close() it before reusing it again.

From MSDN: http://msdn.microsoft.com/en-us/library/haa3afyz(v=vs.80).aspx

answered Mar 22 '13 at 15:09

PlantationGator

815
1
13
21

Yes, calling Close() is all that is needed. You can put it in a finally block to make sure that it is called – PlantationGator Mar 22 '13 at 15:17
I see your code now, you should close the DataReader after the ElseIf before trying to execute the Command. – PlantationGator Mar 22 '13 at 15:22

Robert Beaubien · Answer 2 · 2013-03-22T16:21:54.113

1) Remove the cmd.ExecuteNonQuery(). The ExecuteReader immediately after is the only execute you need. 2) You have to use a different connection object for your insert command or close the previous reader. You can't reuse the same connection object while you are looping through a reader attached to it. So try this:

Dim con As MySql.Data.MySqlClient.MySqlConnection = New MySqlClient.MySqlConnection("server=localhost;user=root;database=zzz;port=3306;password;")
con.Open()
Dim cmd As MySqlClient.MySqlCommand
Dim dr As MySqlClient.MySqlDataReader
cmd = con.CreateCommand()
cmd.CommandText = "select sino FROM customers WHERE sino  =('" + serialno.Text + "')"
dr = cmd.ExecuteReader()
If dr.Read = False Then
  'we have no existing record
  dr.Close()  'close reader first
   Dim sqlc As String
    sqlc = "insert into customers values('" + serialno.Text + "','" + custname.Text + "','" + address.Text + "','" + phno.Text + "','" + eid.Text + "','" + event_type.Text + " ')"
    Dim command As MySqlClient.MySqlCommand = New MySqlClient.MySqlCommand(sqlc, con)
    command.ExecuteNonQuery()
    MessageBox.Show(sqlc)
    con.Close()

Else

    MsgBox("number already exists")
    dr.Close()

End If
con.Close()  'close connection before leaving

in my if statement in the else part i have this error please tell me how to resolve it

2 Answers2