Query by date

[sackymatt]

Hi all,
I have a small problem that i cant figure out. i have a program that i want when a user select a date from a datetimepicker, it filters evrything from the datagridview that are for that date..
Hare is the code i have

Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim oledb As System.Data.OleDb.OleDbConnection
oledb = New System.Data.OleDb.OleDbConnection()

oledb.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CMRS.mdb;Persist Security Info=True"
oledb.Close()
oledb.Open()

Try
Dim da As New OleDb.OleDbDataAdapter("Select * FROM Medical WHERE DateAttended= " & DtDate.Text, oledb)
Dim ds As New DataSet
da.Fill(ds)

'set filter to view only medical records of certain Student
medicalFilter(ds.Tables(0).Rows(0)(0))
oledb.Close()

Catch ex As Exception
MessageBox.Show(ex.Message)

End Try
End Sub

it returns an error. No data at position 0

PLEASE help ASAP

[TomW]

If you use the Code icon at the text area toolbar it will format your code for easier understanding here on the site.

Im not sure why your closing your connection before you even open it but either way both open & close calls are not needed. When you use a DataAdapter, it will automatically open & close the connection when you call the fill or update methods.

With Access you want to enclose your dates with the # symbol as you would quotes for strings. Since this is a date I would also suggest converting the text value of the textbox to an actual Date datatype. However I would suggest not concatenating your query string and instead use Parameter's to pass your value.

[kplcjl]

I second Tom's suggestion to use parameters. This assures the types are compatible and takes care of formatting as well.

Are you sure the datetimepicker will pick an exact date and time that you want? It's possible the query will never find the record even if it is formatted correctly.

[sweber25]

I realize that this is probably old, but go to your database designer (under data sources, click on database, click on Blue Icon with tool tip "Edit Database in Designer) and find your database in the new window. Right Click on it and use the following code IN the query builder

SELECT *
FROM yourdatabase name
WHERE Date=@Date

(Date is replaced by the column name you have set for the date)

In the form, double click on the button, and replace the newly inserted code (usually at the bottom) in the button event. Make sure at the end of that code you change the ".text" element to the text box you want to query. It will put in something like "datetoolstriptextbox.text" or something like that and you change it to "Datetextbox.text" or what ever the name of the box is.