试图插入DateTime.Now到日期/时间字段给出＆QUOT;数据类型不匹配＆QUOT;错误 [英] Trying to insert DateTime.Now into Date/Time field gives "Data type mismatch" error

问题描述

如果我尝试日期时间写在MS-Access数据库记录的简单方法，像这样

If I try to write a datetime to a record in an MS-Access database the easy way, like this

cmd.CommandText = "INSERT INTO [table] ([date]) VALUES (?)";
cmd.Parameters.AddWithValue("?", DateTime.Now);

我得到一个异常说：数据类型不匹配的标准前pression。

I get an exception saying "Data type mismatch in criteria expression."

谁能告诉我为什么？这里是什么出了问题？

Can anybody tell me why? What goes wrong here?

一个小实验后，我发现我可以，如果我写它的工作

After a little experimentation, I found that I can make it work if I write

OleDbParameter parm = new OleDbParameter("?", OleDbType.Date);
parm.Value = DateTime.Now;
cmd.Parameters.Add(parm);

但这样做它像这样似乎不太整齐，那么直接。为什么这个必要吗？我俯瞰简单的东西？

but doing it like this seems less neat, less straightforward. Why is this necessary? Am I overlooking something simple?

推荐答案

在标准前pression不匹配的问题是由于分配到用于重新present的DateTime.Now值的参数的OleDbType当你调用 AddWithValue 。

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.

由AddWithValue choosen的OleDbType是 DBTIMESTAMP ，但Access想要一个 OleDbType.Date 。

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

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

搜索在NET我已经找到另一种野趣提示。
核心问题在于无法处理DateTime.Now的毫秒部分OleDbParameter。可能迫使OleDbType是日期省略毫秒部分。
我还发现，插入作品也与DBTIMESTAMP类型，如果我们从日移除毫秒。

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.

这篇关于试图插入DateTime.Now到日期/时间字段给出＆QUOT;数据类型不匹配＆QUOT;错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！