Save Date from dateTimePicker into ms access database and search records between two dates

Question

I have ms access table 'AddDate'. Two columns 'Id' -Autonumber, 'AddDate' -DateTime.

In VS 2015 I have a form where I have a dateTimePicker to insert date value.

I use the following query to insert the date:

       conn.Open();string str = "Insert into AddDate ([AddDate]) Values(@AddDate)";
        OleDbCommand cmd = new OleDbCommand(str, conn);
        cmd.Parameters.AddWithValue("@AddDate", dateTimePicker1.Value.ToShortDateString());
        cmd.ExecuteScalar();
        conn.Close();

and when I have records entered I want search these records between two dates. For that I have two datetimepicker and I use the following code to get in a datagridview :

        OleDbDataAdapter odb = new OleDbDataAdapter("Select * from AddDate  where AddDate Between '"+dateTimePicker1.Value.ToShortDateString() + "' and '"+ dateTimePicker2.Value.ToShortDateString() + "' ", conn);
        DataTable dt = new DataTable();
        odb.Fill(dt);

        dataGridView1.DataSource = dt;

Now if the ms access table AddDate column is set to DateTime the select query will give error 'Datatype Missmatch in criteria expression'. And if I change the column DataType property to text in ms access table the query works but it collect all the records from the table between two dates regardless of month.

Please explain with example and full code. Also specify DataType property in MS Access 2010.

Thank in advance.

Regards Manoj Yadwad.

Answer 1

Dates are not strings, thus:

cmd.Parameters.AddWithValue("@AddDate", dateTimePicker1.Value);

but to concatenate you must format as valid date expressions:

OleDbDataAdapter odb = new OleDbDataAdapter("Select * from AddDate where AddDate Between #" + dateTimePicker1.Value.ToString("yyyy'/'MM'/'dd") + "# and #" + dateTimePicker2.Value.ToString("yyyy'/'MM'/'dd") + "#", conn);

Better would be to use parameters here as well.