Exception using DateTime in a parameterized OleDbCommand

Question

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.

Answer 1

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.