Search filter

[Infiniums]

Hello, i'm trying to make a searchfilter where i can search records on the date i give in the textbox. What is the best way to do this? I've tried somethings but nothing works very well.

Private Sub cmdSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
  	  Handles cmdSearch.Click
  		lblError.Text = ""
  		Dim strSQL As String
  		strSQL = "SELECT adress, phoneNumber FROM mydatabase WHERE name='" & txtName.Text & "'"
  		Dim cmd As OleDbCommand = New OleDbCommand(strSQL, cnn)
  		Try
  			cnn.Open()
  			'build the dataReader
  			Dim reader As OleDbDataReader = cmd.ExecuteReader
  			'read the first row
  			If reader.Read() Then
  			    'set the textBoxs with appropriate data
  			    Me.txtAddress.Text = reader.GetValue(0)
  			    Me.txtPhoneNo.Text = reader.GetValue(1)
  			Else
  				Me.txtAddress.ResetText()
  				Me.txtPhoneNo.ResetText()
  				lblError.Text = "No Data Found"
  			End If
  			reader.Close()
  		Finally
  			Try
  				cnn.Close()
  			Catch : End Try
  		End Try
  	End Sub
  End Class

I've found this script and i tried to modify it to search at the ID but then I'll get a error:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

I know it has something to do with the string declaration of the sql(I think).
How can i make it work and can somebody please tell me how i can make the searchfilter best for searching a date?

 

[TPM]

Well for one I'd recomend using a DateTimePicker instead of a textbox if you want an exact match. If you want to just narrow the list down to say the month/year use 'Like'. Here's an article about using like FYI:
http://www.sql-tutorial.net/SQL-LIKE.asp

TPM

 

[Infiniums]

kay but how can i make it work in my search filter for search records on date, the date will be given in like this: dd/mm/yyyy

I'm trying a new code and it works if i search on the id but still not on date

 Private Sub cmdOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOk.Click
 		'MsgBox("Je hebt het nummer: " & txtzoek.Text & ".")
 
 		objrow = DtsChef.Tables("tblAankooplijsten").Select("AankooplijstID = " & txtzoek.Text)
 		'MsgBox(objrow.Length)
 
 		txtproductnummer.DataBindings.Clear()
 		txtproductnaam.DataBindings.Clear()
 		txtprijs.DataBindings.Clear()
 		txtvoorraad.DataBindings.Clear()
 
 		txtproductnummer.DataBindings.Add("text", objrow, "Datum")
 		txtproductnaam.DataBindings.Add("text", objrow, "BereidingID")
 		txtprijs.DataBindings.Add("text", objrow, "AantalPersonenIngave")
 		txtvoorraad.DataBindings.Add("text", objrow, "AantalPersonen")
 	End Sub

Does anyone know how to make it work on searching a date? Please need help on this.

 

[TPM]

Where date = '" & datetimepicker.selecteddate & "'

 

[reinier]

search filter

Why not add a WHERE clasule to narrow to search?

Or didn't I read I well, and is is the problem ONLY(!) your
date format?


Regards,
Reinier
(PS. groeten uit brabant )

 

[Paszt]

With dates, instead of enclosing the value in single quotes, use the pound sign #.
WHERE date = #" & dateTimePicker.SelectedDate & "#"

 

[Infiniums]

Ok, Paszt is right

Here is the code that works with searching afther a date

Private Sub cmdOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOk.Click
 		'MsgBox("Je hebt het nummer: " & txtzoek.Text & ".")
 
		objrow =DtsChef.Tables("tblAankooplijsten").Select("Date = #" &txtzoek.Text & "#")
 		'MsgBox(objrow.Length)
 
 		txtproductnummer.DataBindings.Clear()
 		txtproductnaam.DataBindings.Clear()
 		txtprijs.DataBindings.Clear()
 		txtvoorraad.DataBindings.Clear()
 
 		txtproductnummer.DataBindings.Add("text", objrow, "AankooplijstID")
 		txtproductnaam.DataBindings.Add("text", objrow, "BereidingID")
 		txtprijs.DataBindings.Add("text", objrow, "AantalPersonenIngave")
 		txtvoorraad.DataBindings.Add("text", objrow, "AantalPersonen")
 	End Sub

Thx for all your help people
Greetz (van limburg )