Question How to get proper value from DateTimePicker

[TechniMan]

Hi,
I'm using a DateTimePicker so a user can pick a date and then data can be retrieved from a database based on this date (by concatenating strings from the day, month and year parts of the date because the dtp.value.date returns a time reference on the end). However, the database stores the date with preceding zeros (eg 23/04/2011) but when I query the database, the query string shows the month represented as 4 rather than 04, and the DataReader can't find any matches because of this.
Though I'm not sure how this will make much difference, as I am using a separate string so that when the day or month are only 1 digit, a 0 is attached to the front. The command string still uses the 1 significant digit.

How can this be fixed/Am I doing something wrong?
Thanks in advance,
-TechniMan

 

[JohnH]

It sounds as you are saving the date value improperly in database as some other representation. A Date value does not have any format, it is just a numeric date value, in database stored in a date field and in VB.Net handled as Date data type (in CLR as DateTime structure), formats is something you encounter only when presenting/parsing values as strings. Datevalue.Date property returns the date value with time part reset to 0, which is convenient for comparing date values where time part is not relevant. ADO.Net providers will handle transmitting date value between database and application, for example when you use date type parameters in commands when interacting with a database date column. All this is configured automatically if you hook up the database using the data source/dataset wizard.
As for DateTimePicker it of course return a proper Date value.

 

[TechniMan]

Sorry, I'm not sure what you're trying to say.

Using the Date value of the DateTimePicker (dateDate) gives the date as eg 22/04/2011 now, but my query still doesn't return any results even though the data exists. Sample code:

cmd = New OleDbCommand("SELECT Start_Time, Length, PatientID, Checked FROM Appointments WHERE Start_Date = " + dateDate.Value.Date + " ORDER BY Start_Time;", cn)
            MsgBox(cmd.CommandText)
            dr = cmd.ExecuteReader()
            MsgBox(dr.GetValue(0))

I can't see anything wrong with this. I'm sure it's all spelt right. I'm using MsgBox(dr.GetValue(0)) to test for data retrieval, but it gives an error for no data in that column.
(Checked is a binary value to display the appointments in a checked list box)

Also, TimeOfDay.Date is returning 00:00:00 for some reason.

 

[JohnH]

Use parameters, not strings.

Dim cmd As New OleDb.OleDbCommand("SELECT Start_Time, Length, PatientID, Checked FROM Appointments WHERE Start_Date = ? ORDER BY Start_Time;", cn)
cmd.Parameters.AddWithValue("startdate", dateDate.Value.Date)

 

[TechniMan]

This still doesn't return any results, using that code as is or with the parameter name as Start_Date instead of startdate.

Thought: could I just use a text value to store the dates, and pass them to the database as a string?

 

[JohnH]

using that code as is or with the parameter name as Start_Date instead of startdate.

The name of the .Net Parameter object doesn't matter for OleDB, that is why only the ? parameter placeholder is used in the query.

Thought: could I just use a text value to store the dates, and pass them to the database as a string?

No, use date values in VB and date values in database.

This still doesn't return any results,

Are you sure Start_Date field is type date? Also, when that is confirmed, are you sure your database date values has the time part value set to 0? A date value will not equal another date value if the time part differ.

 

[TechniMan]

Are you sure Start_Date field is type date? ... are you sure your database date values has the time part value set to 0? A date value will not equal another date value if the time part differ.

It is the Date/Time type in Access 2010. There is no plain Date type (or similar), and I can't find the time parts of the values, let alone set them to 0.

 

[JohnH]

If you have various time values set you can do a query for all the time in that day, from 00:00 to 23:59 if you will. Probably like this:
... WHERE Start_Date >= ? AND Start_Date < ?
Then add two parameters and set first to the selected Date as shown and the second to same selected Date.AddDays(1).