Querying Access 2007 DateTime with C#

Question

I've just spent the last few hours trying do something which I thought would be very simple. I'm encountering a "Data type mismatch in criteria expression" error when I try to run a parameterized query with dates on an Access 2007 Database. The problem column in the Database is is of a "Date/Time" DataType.

In a simplified example -

dataCommand.CommandText "SELECT * FROM tblData WHERE IDate > #24/07/2013 16:54:51#"

This works. But as soon as I try to parametrize the date I encounter the error. So..

dataCommand.CommandText = "SELECT * FROM tblData WHERE IDate > @BackSearchDate"
....
dataCommand.Parameters.Add(new OleDbParameter("@BackSearchDate", backSearchDate))

Will encounter the error. I've tried to provide the backSearchDate variable in numerous ways. DateTime, strings of various formats etc do not work.

I found this page which would indicate it's a known bug - but I've not been able to get any of the workarounds on the page to work either. I'm pretty stumped.

Thanks in advance for any help.

When you are sending the parameter you are not encapsulating the date time in #DATETIME#, but if you are please share. — Elias, Jul 29 '13 at 13:20
Also, just a shot in the dark, if you are putting the ## in the parameter, then maybe it is reading it as string? — Elias, Jul 29 '13 at 13:22
Have you tried `dataCommand.Parameters.AddWithValue("@BackSearchDate", backSearchDate)`? — Gord Thompson, Jul 29 '13 at 13:40

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

So what I am saying is:

dataCommand.CommandText = "SELECT * FROM tblData WHERE IDate > @BackSearchDate"

EDIT

Checking out Gord Thompson's answer suggests that you should try:

var conn = new OleDbConnection(@"ConnString");
conn.Open();
var cmd = new OleDbCommand(
                "SELECT * FROM tblData WHERE IDate > @BackSearchDate", 
                conn);
cmd.Parameters.AddWithValue("@BackSearchDate", new DateTime(backSearchDate));
OleDbDataReader rdr = cmd.ExecuteReader();

edited May 23 '17 at 11:57

Community

1
1

answered Jul 29 '13 at 13:23

Elias

2,602
5
28
57

Hi Elias. Thanks for the reply. I'd read that #'s were not needed when the DateTime value was in a paramater, so I hadn't been doing that. I just tried the above and got a syntax error. – KorbenDallas Jul 29 '13 at 13:29
Okay, I wasn't sure if it would fail or not, sorry I can't be of more help. Good luck! – Elias Jul 29 '13 at 13:31
A bit late, but I am retrying to answer this question @user2598782 – Elias Aug 19 '14 at 16:01

score 0 · Answer 2 · answered Jul 29 '13 at 15:27

To anyone encountering this problem in the future - It appears to work if you add the parameter in the following way:

DbParameter p = dataCommand.CreateParameter();
p.ParameterName = "@BackSearchDate";
p.Value = backSearchDate;
p.DbType = DbType.Date;
dataCommand.Parameters.Add(p);

Querying Access 2007 DateTime with C#

2 Answers2