Trying to insert DateTime.Now into Date/Time field gives “Data type mismatch” error

by

The problem of the mismatch in criteria expression is due to the OleDbType assigned to the parameter used to represent the DateTime.Now value when you call AddWithValue.

The OleDbType choosen by AddWithValue is DBTimeStamp, but Access wants a OleDbType.Date.

http://support.microsoft.com/kb/320435

Searching on the NET I have found another intersting tip.
The core problem lies in the OleDbParameter that cannot handle the milliseconds part of the DateTime.Now. Probably forcing the OleDbType to be Date the milliseconds part is omitted.
I have also found that the insert works also with the DBTimeStamp type if we remove the milliseconds from the date. 

cmd.Parameters.AddWithValue("?", GetDateWithoutMilliseconds(DateTime.Now));

private DateTime GetDateWithoutMilliseconds(DateTime d)
{
    return new DateTime(d.Year, d.Month, d.Day, d.Hour, d.Minute, d.Second);
}

oh, well, waiting for someone that explain this better.

Leave a Comment