how to clear the previous search from the textbox?

Question

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

Like `TextBox1.Text = "" : TextBox2.Text = "No record found"`? — Olivier Jacot-Descombes, Jan 16 '19 at 22:17
hi @OlivierJacot-Descombes, what i need to fix is not to display the previous record of the previous search query if the next search is not in the record. — Veck, Jan 16 '19 at 22:22
`dr.HasRows` is a Boolean, which can have a value of `True` or `False`. Comparing it to 0 may have unexpected results. Now, it shouldn't really let you write code like that, but there is a setting which you should change known as [`Option Strict On`](https://stackoverflow.com/a/29985039/1115360) - setting it to On will let Visual Studio help you to write code which works. — Andrew Morton, Jan 16 '19 at 22:22
You should do the check `If dr.HasRows Then` before the `While dr.Read` loop. — Andrew Morton, Jan 16 '19 at 22:24
Always use parameters to avoid sql injection and formatting errors. — LarsTech, Jan 16 '19 at 22:35

jmcilhinney · Accepted Answer · 2019-01-16T22:33:04.867

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.

score 0 · Answer 2 · answered Jan 16 '19 at 22:26

0

Assuming that txtBGC1 and txtBGC2 are TextBoxes, you could do something like this, assuming that the query can at most return one employee

...
If dr.Read Then ' There is an employee
    txtBGC1.Text = dr("PreStartChecks").ToString
    txtBGC2.Text = dr("EmpName").ToString
Else ' There is no employee
    txtBGC1.Text = ""
    txtBGC2.Text = "No record found"
End If
myConnection.Close()

answered Jan 16 '19 at 22:26

Olivier Jacot-Descombes

104,806
13
138
188

hi @oliver Jacot-Descombes, Appereciate this one, i've tested this code and works perfectly fine however my only problem now is, if the employee is not in the records(database), the textboxes is populated by the previous search which is in the database. i want to come up with blank textbox if not found in the database. i added a message box which works fine if record is not found. – Veck Jan 18 '19 at 01:03
The `Else` case clears the textbox if the record is missing. – Olivier Jacot-Descombes Jan 18 '19 at 13:39

how to clear the previous search from the textbox?

2 Answers2