Trying to Insert Datetime.Now into Date/Time Field Gives "Data Type Mismatch" Error

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

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.

Why am I getting Data type mismatch in criteria expression with this code?

The link provided shows at least two fixes for this; I found the most straightforward one to be simply removing the DateTime parameter from the mix and directly injecting the dateTime value from within the Insert statement by replacing the "DTNow" param with a call to "Now()" like so:

cmd.CommandText = 
    @"INSERT INTO tx_tillermantea (tx, site_no, xmlfile, collect_dttm, ppt_user, tx_memo, file_beg, file_end) 
      VALUES(@txval, @siteNum, @xmlfileName, Now(), @PPTUser, @TXMemo, @strfile_beg, @strfile_end)";

DataType mismatch between C# and MS Access?

Yes, it is almost certainly the milliseconds that are causing the problem if you have derived the parameter values from an earlier call to DateTime.Now in .NET.

You can get rid of the milliseconds using the methods in the answers to the previous question you cited here, or you could just format the parameters as yyyy-MM-dd HH:mm:ss strings and let Access OLEDB convert them back to Access Date/Time values, i.e.,

cmd.Parameters.AddWithValue("@profile_id", ProfileID);
cmd.Parameters.AddWithValue("@start", Start.Value.ToString("yyyy-MM-dd HH:mm:ss"));
cmd.Parameters.AddWithValue("@start_log", StartLog.Value.ToString("yyyy-MM-dd HH:mm:ss"));

Troubleshooting Data type mismatch in criteria expression. during MS Access Insert.Into

Considering June7's comment about delimiters, it seems the issue lies in some issue inherent to the OleDbParameter type. In SQL Server terms, I do want DateTime (not Date), but representing it as a DBTimeStamp seems to make it unrecognizable by Access.

For the time being, I've sent the date as a VarChar and allowed Access to convert it however its internal engine sees fit. It feels/seems wrong, but it does, in fact, solve the problem.

Parameters =
                        {
                            new OleDbParameter("@Customer_ID", OleDbType.Integer),
                            new OleDbParameter("@Notes", OleDbType.LongVarChar),
                            new OleDbParameter("@NotesDate", OleDbType.VarChar)
                        }

EDIT: Just saw June7's latest comment, and there was in fact, an answer in another thread. OleDbType.DBDate doesn't do what I want, but OleDbType.Date does.

Data type mismatch when using date Parameters

I strongly feel that the problem is because the parameters are not in the same order when you are adding them to the parameter collection.

Make sure that they are in the same order and this applies to both sql statements or stored procedures.

ADO.NET does not support named parameters when using an OLEDB provider, and since you are connecting to an Access DB, you are actually using an OLEDB provider. So the order of the parameters does matter.

If they are in order and it's still not working, then I think that it might be an issue with the DateTime. Try converting it to string before adding it as a parameter.

C# Access OleDb Data type mismatch in criteria expression

So...[PROBLEM SOLVED] :D

From HERE I learnt that

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.

Meaning that the convenient AddWithValue pulled a fast one on me...

Thank you @LarsTech and @DJKraze for helping me out despite the confusion of the presentation!

---

Related Topics