CODEWITHSUNDEEP
vb.netvisual-studio-2010
user1795999
user1795999

Reputation: 301

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

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

Upvotes: 1

Views: 313

Answers (2)

Robert Beaubien
Robert Beaubien

Reputation: 3156

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

Upvotes: 0

PlantationGator
PlantationGator

Reputation: 875

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

Upvotes: 2

Related Questions