Auto filling textboxes based on an entry into another textbox

[samuelz]

Hello everyone, i have the code below, it works fine but whenever i insert a student number that does not exist in the table, the rest of the textboxes will keep displaying data from the previous existing entry. This happens even when the i delete everything in the student number textbox.
how can i change it such that the rest of the textboxes are cleared in case the student number textbox is blank or contains a student number that does not exist in the database?
Thanks in advance.

' Try

    Dim mycommand As SqlCommand = New SqlCommand()

    Dim datareader As SqlDataReader = Nothing

    myconnection.Open()

    Dim query As String

    query = " select StudentNo,Fullname,Year,Term,Class from StudentRegistration  where StudentNo = '" & TxtStudentNo.Text & "' and (class = 'Senior 5A' or Class ='Senior 5S' or Class='Senior 6A' or class='Senior1 6S')"
    mycommand = New SqlCommand(query, myconnection)
    datareader = mycommand.ExecuteReader()
    While datareader.Read
        If datareader IsNot Nothing Then
            '    TxtStudentNo.Text = datareader.Item("StudentNo")
            TxtName.Text = datareader.Item("FullName")
            TxtYear.Text = datareader.Item("Year")
            TxtTerm.Text = datareader.Item("Term")
            TxtClass.Text = datareader.Item("Class")
        End If

    End While
    myconnection.Close()
    ' Catch ex As Exception
    'MessageBox.Show(ex.Message)
    ' End Try`

[Mr.M]

Try to make your variables as Global and see, also try to add another IF statement inside the current IF statement. The IF statement you need to add will be for checking if the dataread student number matches the student number supplied in a textbox if these match then populate data to textboxes or else just clear the textboxes that will ensure that its populates data based on the current entered student number on a textbox.

[Reverend Jim]

You could clear the textboxes whenever you do a insert or delete. This is unrelated to the question but you might simplify the query as

query = "SELECT StudentNo,Fullname,Year,Term,Class " &
        "  FROM StudentRegistration  " &
        " WHERE StudentNo = '" & TxtStudentNo.Text & "'" &
        "   AND class IN ('Senior 5A', 'Senior 5S', 'Senior 6A', 'Senior1 6S')"

[samuelz]

I finally changed my code to the one below and it's doing exactly what i want.
thanks for the suggestions.

   Private Sub getData()
     Dim dt As New DataTable()
 myconnection.Open()

Dim Mycommand As New SqlCommand("select Fullname,Year,Term,Class from StudentRegistration  where StudentNo = '" & TxtStudentNo.Text & "'", myconnection)
Dim sqlDa As New SqlDataAdapter(Mycommand)
sqlDa.Fill(dt)
If dt.Rows.Count > 0 Then
    TxtName.Text = dt.Rows(0)("FullName").ToString()
    TxtYear.Text = dt.Rows(0)("Year").ToString()
Else
    TxtName.Text = ""
    TxtYear.Text = ""
End If
myconnection.Close()
End Sub