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)
If Not dr1.IsClosed Then dr1.Close()
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.
"There is already an open DataReader associated with this Command which must be closed first."
pls help me..
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.