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
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
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
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.While
or Do While
loop to call Read
and access the row that was just read inside the loop.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.