Data problem

[dakota97]

I have a form consisting of a textbox, two datepickers, and three listboxes. What I'm trying to do is search the db to find records which include the text from the textbox that are between two dates chosen from the datepickers. I am able to retrieve the data and display it in the listboxes with no problem.

 

The problem comes when I want to display a message if there are no records meeting the criteria. I've tried using an IF....THEN statement, but I must be putting it in the wrong place or something, because when I run the code, it doesn't display the data. If I take out the IF...THEN, it works fine, but doesn't display the message if no records are found. The code that works is as follows:

 

 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

 

Dim strdate As String = DateTimePicker1.Value.ToShortDateString

Dim enddate As String = DateTimePicker2.Value.ToShortDateString

 

'Create the database connection

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb")

MyConnection.Open()

 

'Query the database to find matching record

Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between #" & strdate & "# and #" & enddate & "# AND EmpID='" & TextBox1.Text & "'", MyConnection)

 

'Declare a variable to read the database information

Dim MyReader As OleDbDataReader = MyCommand.ExecuteReader()

 

Do While MyReader.Read

 

Dim datein As Date = MyReader("Datein")

Dim stime As Date = MyReader("StartTime")

Dim etime As Date = MyReader("EndTime")

Dim din As String = datein.ToShortDateString

Dim intime As String = stime.ToShortTimeString

Dim otime As String = etime.ToShortTimeString

 

'Add items to the listboxes with the data from the datareader

ListBox1.Items.Add(din)

ListBox2.Items.Add(intime)

ListBox3.Items.Add(otime)

 

Loop

 

MyConnection.Close()

MyReader.Close()

MyCommand.Dispose()

 

End Sub

 

 

 

Any ideas on how I can display a message if no records are found matching the criteria?

 

Thanks in advance,

 

Chris

[techmanbd]

try this. I think it is this. but I don't have my computer on so tghis is from memory

 

if myreader.hasrows then
     Do While MyReader.Read
          Dim datein As Date = MyReader("Datein")
          Dim stime As Date = MyReader("StartTime")
          Dim etime As Date = MyReader("EndTime")
          Dim din As String = datein.ToShortDateString
          Dim intime As String = stime.ToShortTimeString
          Dim otime As String = etime.ToShortTimeString

    'Add items to the listboxes with the data from the datareader
     ListBox1.Items.Add(din)
     ListBox2.Items.Add(intime)
     ListBox3.Items.Add(otime)
  Loop
ELSE
'enter code here for message if no data
END IF

[Roey]

or

 

Try

Dim MyReader As OleDbDataReader=MyCommand.ExecuteReader

 

Dim i As Integer

 

While MyReader.Read

 

Dim i As Integer

 

Dim datein As Date = MyReader("Datein")

Dim stime As Date = MyReader("StartTime")

Dim etime As Date = MyReader("EndTime")

Dim din As String = datein.ToShortDateString

Dim intime As String = stime.ToShortTimeString

Dim otime As String = etime.ToShortTimeString

 

'Add items to the listboxes with the data from the datareader

ListBox1.Items.Add(din)

ListBox2.Items.Add(intime)

ListBox3.Items.Add(otime)

 

i +=1 'count the number of records

 

End While

MyReader.close

 

Finally

con.Close()

 

End Try

 

 

Test for i = 0

 

 

Hope this helps

[Roey]

Sorry the count integer declaration should be outside the loop only.

 

 

Try

Dim MyReader As OleDbDataReader=MyCommand.ExecuteReader

 

Dim i As Integer

 

While MyReader.Read

 

Dim datein As Date = MyReader("Datein")

Dim stime As Date = MyReader("StartTime")

Dim etime As Date = MyReader("EndTime")

Dim din As String = datein.ToShortDateString

Dim intime As String = stime.ToShortTimeString

Dim otime As String = etime.ToShortTimeString

 

'Add items to the listboxes with the data from the datareader

ListBox1.Items.Add(din)

ListBox2.Items.Add(intime)

ListBox3.Items.Add(otime)

 

i +=1 'count the number of records

 

End While

MyReader.close

 

Finally

con.Close()

 

End Try

[dakota97]

Worked perfectly. Thanks Roey. It was driving me crazy.