Data type mismatch when retrieving records from an access database using a DateTimePicker

Question

I get a Data type mismatch criteria expression error when i try to retrieve records from an access database between two dates using a DateTimePicker in C#.

This is the Select statement

else if (dtpDOBFrom.Value < dtpDOBTo.Value)
{
    cmdSearch.CommandText = "SELECT [First Name], [Surname], [Contact Type], [Birthdate] FROM [Contacts] WHERE [Birthdate] >= '" + dtpDOBFrom.Value +"' AND [Birthdate] <= '" + dtpDOBTo.Value +"'";
}

Answer 1

dtpDOBFrom.Value is a DateTime, which you are trying to paste into a string. Thus, the DateTime is converted into a string, but the format seems to be different from the format that Access expects. You could play with the parameters of DateTime.ToString (and enclose the date in # instead of ', since this is what Access wants), but that would not be the right way to do it.

The right way, which avoids such type cast problems in general (and SQL injection, among other things), is to use parameters:

In your case, it would look something like this (untested):

cmdSearch.CommandText = "SELECT [First Name], [Surname], [Contact Type], [Birthdate] FROM [Contacts] WHERE [Birthdate] >= ? AND [Birthdate] <= ?";
cmdSearch.Parameters.AddWithValue("DOBFrom", dtpDOBFrom.Value);
cmdSearch.Parameters.AddWithValue("DOBTo", dtpDOBTo.Value);

With OLEDB (which is used for MS Access database access), the name of the parameters ("DOBFrom") is not important, but the order in which you add them must match the order of the question marks in the SQL.