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.

What is the value of dateTime? I think .NET and Access have different min and max values. — user27414
– user27414, Commented Sep 27, 2010 at 21:37

Pavel Minaev · Accepted Answer · 2010-09-27 21:35:28Z

3

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.

answered Sep 27, 2010 at 21:35

Pavel Minaev

102k27 gold badges222 silver badges293 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

C Walker Over a year ago

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.

Collectives™ on Stack Overflow

Exception using DateTime in a parameterized OleDbCommand

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related