Search code examples
vb.netvb.net-2010c#-to-vb.net

how to clear the previous search from the textbox?


I am new in vb.net. i have a database and i am running a search query of employee records when search button clicked and display that information in textbox, however when a user is not in the database, the search output is displaying the information of the previous search. the information in textbox should display blank or say "No record found" if the user is not in the record. not sure what is wrong in my code.

Try
    myConnection.Open()  
    Dim str As String
    str = "SELECT * FROM tblEmp WHERE (EmpID = '" & ADS.UserEmpID & "')"
    Dim cmd As OleDbCommand = New OleDbCommand(str, myConnection)
    dr = cmd.ExecuteReader
    While dr.Read
        If dr.HasRows > 0 Then
            MessageBox.Show("user already in the system", "Warning", MessageBoxButtons.OK)
        ElseIf dr.HasRows = 0 Then
            MessageBox.Show("Not Onboarded", "Warning", MessageBoxButtons.OK)
        End If
        BGC1 = dr("PreStartChecks").ToString
        BGC2 = dr("EmpName").ToString
        myConnection.Close()
    End While
Catch ex As Exception
    MsgBox("Unable to Connect to BGC DB. You may not have access or DB not available." &
        ex.ToString)
End Try

Solution

  • Your While loop and If statement don't make sense. Firstly, HasRows is type Boolean so testing whether it is greater than zero is nonsensical. Secondly, Read returns False if there are no rows so the only way you can get to that If statement is if there is at least one row to read so testing HasRows when you already know that there are rows is also nonsensical. The proper option here is to use just an If statement and test Read only:

    If dr.Read() Then
        'There is a row to read and it was just read, so you can now get the data from the reader.
    Else
        'There is no row to read.
    End If
    

    If you want to clear a control when there's no data, you do so in the Else block.

    The "rules" about when and how to use HasRows and Read are very simple and logical:

    • If all you care about is whether the query result set contains data or not but you don't care what that data is, just use an If statement to test HasRows. The HasRows property is type Boolean so there's no need to compare it to anything. It already is True or False.
    • If there can only be zero or one row in the result set, just use an If statement to call Read and test the result. Again, it's type Boolean so there's no need to compare it to anything. If it returns True then you can access the data for the row you just read.
    • If there can be multiple rows and you don't want to do anything special if there are no rows then just use a While or Do While loop to call Read and access the row that was just read inside the loop.
    • If there can be multiple rows and you do want to do something special if there are no rows, use an If statement to test HasRows and then a While or Do While loop inside the If block to call Read. You would handle the case where there are no rows in the Else block.