views:

52

answers:

1

I'm trying to insert a System.DateTime into an Access database using a parameterized OleDbCommand in C#. However, it throws a Data type mismatch in criteria expression exception.

Here is my code:

string statement = "INSERT INTO Log (SCTID, LogDateTime, Type, Message, Visible)" +
    "VALUES (?, ?, ?, ?, ?);";

OleDbCommand insertCommand = new OleDbCommand(statement, connection);

// Add parameters to the command            
insertCommand.Parameters.AddWithValue("@SCTID", OleDbType.Integer).Value = SCTID;
insertCommand.Parameters.AddWithValue("@LogDateTime", OleDbType.DBTime).Value = dateTime;
insertCommand.Parameters.AddWithValue("@Type", OleDbType.Integer).Value = (int)logType;
insertCommand.Parameters.AddWithValue("@Message", OleDbType.BSTR).Value = message;
insertCommand.Parameters.AddWithValue("@Visible", OleDbType.Boolean).Value = visible;

insertCommand.ExecuteNonQuery();

When I comment out the LogDateTime line, the rest of it works. My problem is that no matter what I use for the DateTime type, it doesn't work. I've tried:

OleDbType.Date, OleDbType.DBDate, OleDBType.DBTimeStamp, DbType.Date, DbType.DateTime, DbType.DateTime2

I've also tried:

insertCommand.Parameters.AddWithValue("@LogDateTime", dateTime);

It doesn't work either. Nothing I've read through Google or SO works. Also, note that I do need both date and time, not just a date alone.

+2  A: 
insertCommand.Parameters.AddWithValue("@SCTID", OleDbType.Integer).Value = SCTID;
...

That's a very strange way to use AddWithValue. Its second parameter is not the type - it's the value that you want it to have. As given, you just end up using the integral value of enumeration member OleDbType.Integer, and then immediately overwrite it by assigning to Value property. It should be either:

insertCommand.Parameters.AddWithValue("@SCTID", SCTID);

or:

insertCommand.Parameters.Add("@SCTID", OleDbType.Integer).Value = SCTID;

Regarding the statement itself - why do you use ? for placeholders in command text, but then use names when adding parameters to the collection?

Regarding the actual problem - looks like it's a known bug, and the workaround is to truncate milliseconds in your DateTime value before assigning, and to use OleDbType.Date.

Pavel Minaev
Ah yes, forgive my weird parameters. The first tutorial I found about parameterization did it that way, and although I had an inkling that it was weird, I haven't gotten around to changing them yet. Thanks very much for the tip. Also, I'll give the truncating milliseconds a shot, seems like it might work.
recoisiche