php MS Access：如何将 NULL 插入 DateTime 字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3535025/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MS Access: How does one insert NULL into DateTime field
提问by Teekin
I have an MS Access database (intolerably enough), and communicating with it through PHP (ODBC).
我有一个 MS Access 数据库（足够令人难以忍受），并通过 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.
我必须在 INSERT 语句中包含一个 DateTime 字段。此字段在 Access 中未定义为“必需”，这意味着它确实可以为 NULL，实际上 Access 数据库中的某些行已经为 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.
我遇到的问题很简单：如何通过 SQL 插入 NULL？我在网上找到的所有结果都是从 Visual Basic 或 C# 之类的东西中解决的，而我在 PHP 中通过 ODBC 使用 SQL。
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.)
（我的查询中还有大约 30 个其他列。）
The exact error I get is 'Data type mismatch in criteria expression' when I try '' or NULL. The others return a parse error (understandably).
当我尝试 '' 或 NULL 时，我得到的确切错误是“条件表达式中的数据类型不匹配”。其他人返回解析错误（可以理解）。
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.
请注意，我必须在 INSERT 语句中包含该字段。该字段具有默认值，但在许多情况下，我正在传输的原始数据具有 NULL，目标数据库中的该数据也必须是 NULL。
Thanks in advance!
提前致谢！
回答by BIBD
Try the following. It works for me:
请尝试以下操作。这个对我有用：
INSERT INTO sometable ( somedate, somethingelse )
SELECT Null AS Expr1, "foo" AS Expr2;
Basically, you are wrapping the null in the select query and letting SQL figure out how to represent it to the insert.
基本上，您将空值包装在选择查询中，并让 SQL 找出如何将它表示到插入中。
-- EDIT --
- 编辑 -
This SHOULD also work:
这也应该有效：
INSERT INTO sometable ( somedate, somethingelse )
values (Null , "foo");
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.
根据我的直觉，我将我的数据库从 ANSI-89 切换到 ANSI-92，然后 VALUES 方法开始工作。我将其切换回 ANSI-89，它仍然有效。不仅如此，在我创建的任何新数据库上，它现在也可以工作。奇怪......安装中的某些东西必须通过来回切换而改变（并坚持），而不仅仅是ANSI-89/92。这似乎就是我们得到不同结果的原因。
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".
你可以通过Office Logo->Access Options->OBJECT DESIGNERS->QUERY DESIGN来切换数据库。更改 SQL Server 兼容语法 (ANSI 92) - 并选中“此数据库”。
Ok, very odd.
好吧，很奇怪。
回答by masfenix
I know you've already figured this out but there is also dbNull
我知道你已经想通了，但还有 dbNull
回答by ramesh kumar
INSERT INTO table_name (datetime_field) VALUES (DbNull.Value)
INSERT INTO table_name (datetime_field) VALUES (DbNull.Value)
回答by mjb
I have this type of error and Try this.
我有这种类型的错误，试试这个。
INSERT INTO table_name (datetime_field) VALUES (DBNull.value)
It works fine for me.
这对我来说可以。
回答by shahkalpesh
回答by Beth
Try just leaving it blank
尝试将其留空
(values fld1,,fld2)
