MS Access：如何将 NULL 插入 DateTime 字段
我有一个 MS Access 数据库（令人难以忍受），并通过 PHP (ODBC) 与其通信。
我必须在 INSERT 语句中包含一个 DateTime 字段。该字段在 Access 中未定义为“必需”，这意味着它确实可以为 NULL，并且事实上 Access 数据库中的某些行已经为 NULL。
我遇到的问题很简单：如何通过 SQL 插入 NULL？我在网上找到的所有结果都通过 Visual Basic 或 C# 等解决了这个问题，而我在 PHP 中通过 ODBC 使用 SQL。
我已经尝试过以下操作：（
INSERT INTO table_name (datetime_field) VALUES (NULL)
INSERT INTO table_name (datetime_field) VALUES (#NULL#)
INSERT INTO table_name (datetime_field) VALUES ('NULL')
INSERT INTO table_name (datetime_field) VALUES ('#NULL#')
INSERT INTO table_name (datetime_field) VALUES ('')
我的查询中还有大约 30 个其他列。）
当我尝试“”或 NULL 时，我得到的确切错误是“条件表达式中的数据类型不匹配”。其他返回解析错误（可以理解）。
请注意，我必须在 INSERT 语句中包含该字段。该字段具有默认值，但在许多情况下，我正在传输的原始数据具有 NULL，该数据在目标数据库中也必须是 NULL。
提前致谢！
I have an MS Access database (intolerably enough), and communicating with it through PHP (ODBC).
There is a DateTime field that I have to include in my INSERT statement. This field is NOT defined as "Required" in Access, meaning that it is indeed NULL-able, and in fact some of the rows in the Access database are already NULL.
The problem I'm having is simple: How to insert NULL through SQL? All the results I've found online have addressed it from something like Visual Basic or C#, whereas I'm using SQL through ODBC in PHP.
I have already tried the following:
INSERT INTO table_name (datetime_field) VALUES (NULL)
INSERT INTO table_name (datetime_field) VALUES (#NULL#)
INSERT INTO table_name (datetime_field) VALUES ('NULL')
INSERT INTO table_name (datetime_field) VALUES ('#NULL#')
INSERT INTO table_name (datetime_field) VALUES ('')
(There's about 30 other columns in my query.)
The exact error I get is 'Data type mismatch in criteria expression' when I try '' or NULL. The others return a parse error (understandably).
Please note that I have to include the field in the INSERT statement. The field has a default value, but in many cases the original data that I'm transporting has a NULL that must also be a NULL in the target database.
Thanks in advance!
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（7）
尝试将其留空
Try just leaving it blank
您使用哪些库来与 ODBC 通信？
这可能是空值语法（库解释它的方式）的问题吗？
看看此页面是否有帮助。
注意：我没有使用过 PHP。
What are the libraries, you are using in order to talk to ODBC?
Could it be a problem with the syntax for null values, the way library interprets it?
See, if this page helps.
Note: I have not worked with PHP.
我有这种类型的错误
试试这个。
它对我来说效果很好。
I have this type of error and
Try this.
It works fine for me.
我认为它区分大小写。我使用 NULL 并失败了。然后我用了Null。一切都好
I think it's case sensitive. I was using NULL and failing. Then I used Null. all good
尝试以下操作。它对我有用：
基本上，您将 null 包装在选择查询中，并让 SQL 弄清楚如何将其表示为插入。
-- 编辑 --
这也应该有效：
但由于某种原因，它不适用于我的默认安装。
根据我的预感，我将数据库从 ANSI-89 切换到 ANSI-92，VALUES 方法开始工作。我把它改回 ANSI-89，它仍然有效。不仅如此，在我创建的任何新数据库上，它现在也可以工作。奇怪...安装中的某些东西必须通过来回切换而改变（并坚持），而不仅仅是 ANSI-89/92。这似乎就是我们得到不同结果的原因。
您可以通过转到 Office 徽标 -> 访问选项 -> 对象设计器 -> 查询设计来切换数据库 ocwe。更改 SQL Server 兼容语法 (ANSI 92) - 并检查“此数据库”。
好吧，很奇怪。
Try the following. It works for me:
Basically, you are wrapping the null in the select query and letting SQL figure out how to represent it to the insert.
-- EDIT --
This SHOULD also work:
But for some reason it doesn't with my default install.
On I hunch, I switched my DB from ANSI-89 to ANSI-92, and the VALUES method started working. I switched it back to ANSI-89, and it still works. Not only that, on ANY new database I create, it now also works. Weird... something in the installation must be getting changed, (and sticking) by the switching back and forth that's not just ANSI-89/92. This seems to be why we were getting different results.
You can switch the database ocwe by going to Office Logo->Access Options->OBJECT DESIGNERS->QUERY DESIGN. Change SQL Server Compatible Syntax (ANSI 92) - and checking "This database".
Ok, very odd.
我知道您已经弄清楚了这一点，但还有
dbNullI know you've already figured this out but there is also
dbNullINSERT INTO table_name (datetime_field) VALUES (DbNull.Value)INSERT INTO table_name (datetime_field) VALUES (DbNull.Value)