Search Record

[kimlong2012]

Dear All;

I have a form contain with two TextBox control and one ComboBox. There are StaffCode(txtStaffCode),StaffName(txtStaffName),Gender(cboGender). After I insert data into table, I want to retrive it back by using Reader method. The code I had done as below:
Private Sub btnSearch_Click(-----)

dim cnn as new SqlClient.SqlConnection
dim cmd as new SqlClient.SqlCommand
dim dr as SqlClient.SqlDataAdapter

cmd=SqlClient.SqlCommand("SELECT StaffCode,StaffName,Gender FROM Staff WHERE StaffCode='" & Me.txtStaffCode & "'", cnn)
dr=cmd.ExecuteReader()

While dr.Read

If Me.txtStaffCode=dr.Item("StaffCode") Then
    Me.txtStaffName.Text=dr.Item("StaffName")
    me.cboGender.Text=dr.Item("Gender")
Else
    'Here is the code I need to check.
    'record with the text enter (staffcode)
    'If it doesn't match, I want to show message

    MessageBox.Show("No record found.","Not Found")
End IF

End While
dr.Close
cmd.Dispose

End Sub

Anyone can help me please,

Thank!

[G_Waddell]

Your Code has defined dr as a SqlDataAdapter not a SqlDataReader...

[jezguitarist30]

Yeah, G Waddell was right you need to Change your dr to a Reader not an Adapter.

Dim dr as SqlDataReader


if dr.hasrows then                      ======> 'This will be responsible for checking
                                        ======> 'If there's any data found.

    While dr.read

    Me.txtStaffCode = dr.Item("StaffCode")         =====> 'Inserting Data from your DB
    Me.txtStaffName.Text = dr.Item("StaffName")    =====> 'Into your Textboxes.
    me.cboGender.Text = dr.Item("Gender")

    End While

else

    MessageBox.Show("No record found.","Not Found")

End if

dr.close

[poojavb]

Dim myCommand As SqlCommand
myCommand = New SqlCommand("SELECT StaffCode,StaffName,Gender FROM Staff WHERE StaffCode='" & Me.txtStaffCode & "'", cnn)
Dim reader As SqlDataReader = myCommand.ExecuteReader
If reader.HasRows()
    While reader.Read
                    'txtStaffCode.Text = reader("StaffCode")
                    txtStaffName.Text = reader("StaffName")
                    cboGender.Text = reader("Gender")
    End While
Else
    MsgBox("No record found.","Not Found")
End If
reader.Close()