尝试将 DateTime.Now 插入日期/时间字段会导致“数据类型不匹配"错误

问题描述

如果我尝试以简单的方式将日期时间写入 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);

我收到一条异常消息，提示条件表达式中的数据类型不匹配."

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?

推荐答案

条件表达式不匹配的问题是由于在调用 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 选择的 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 上搜索我发现了另一个有趣的提示.核心问题在于OleDbParameter 无法处理DateTime.Now 的毫秒部分.可能强制 OleDbType 为 Date 省略毫秒部分.我还发现，如果我们从日期中删除毫秒，则插入也适用于 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 插入日期/时间字段会导致“数据类型不匹配"错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！