CODEWITHSUNDEEP
vb.netsqldatareader
Hari
Hari

Reputation: 137

already an open DataReader associated with this Command when am chekcing with invalid data

Private Sub txt_sname_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_sname.GotFocus
        Dim fcs As String
        fcs = "select fname,dept from nstudent where stid = '" & txt_sid.Text & "'"
        scmd1 = New SqlCommand(fcs, con)
        dr1 = scmd1.ExecuteReader
        If dr1.HasRows Then
            Do While (dr1.Read)
                txt_sname.Text = dr1.Item(0)
                cmb_dept.Text = dr1.Item(1)
            Loop
        Else
            MsgBox("Not Found")
        End If
        scmd1.Dispose()
        If Not dr1.IsClosed Then dr1.Close()
End Sub

The above code for data from database and pass to textbox. When am running the program and checking with data which already present in database, it working properly. but checking with some other data(which not present in db)following error is occurring and exiting.

error: "There is already an open DataReader associated with this Command which must be closed first."

pls help me..

Upvotes: 0

Views: 4519

Answers (1)

scott.korin
scott.korin

Reputation: 2597

Some observations:

Instead of using a global command object, use a local one. Especially since you are creating a new command anyway. And it looks like this is true of the dr1 as well.

You are not preventing SQL injection, so someone can type text in txt_sid that causes security issues by deleting data, dropping tables, or getting access to other data in the database.

You are looping and setting the same variables multiple times. If there is only going to be one record, don't bother looping.

Wrap the entire thing around a try/catch

Private Sub txt_sname_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt_sname.GotFocus
    Dim cmd1 As SqlCommand = New SqlCommand(fcs, "select fname,dept from nstudent where stid = @stid")
    cmd1.Parameters.Parameters.AddWithValue("@stid", txt_sid.Text)

    Dim studentReader as SqlDataReader

    Try
        studentReader = scmd1.ExecuteReader
        If studentReader.Read Then
            txt_sname.Text = studentReader.Item(0)
            cmb_dept.Text = studentReader.Item(1)
        Else
            MsgBox("Not Found")
        End If
    Finally
        studentReader.Close()
        cmd1.Dispose()
    End Try
End Sub

Finally, I think you might want to actually do this when txt_sid changes, not when txt_sname gets focus.

Upvotes: 1

Related Questions