C# 如何在sql server的日期时间类型列中插入空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18098659/
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
How to insert null in datetime type column in sql server
提问by social
I have a table in which there are some datetime type columns. I use a stored procedure to insert values into the table. In the stored procedure I have variables that accept null for inserting into the table. My problem is when I try to insert a null value into my table's datetime column it throws error. my code is as.
我有一个表，其中有一些日期时间类型的列。我使用存储过程将值插入表中。在存储过程中，我有一些变量接受 null 以插入到表中。我的问题是当我尝试将一个空值插入到我的表的日期时间列时，它会引发错误。我的代码是。
System.Data.SqlTypes.SqlDateTime getDate;
//set DateTime null
getDate = SqlDateTime.Null;
if (InsertDate.Text == "")
{
cmd1.Parameters["@InsertDate"].Value = getDate;
}
else
{
cmd1.Parameters["@InsertDate"].Value = InsertDate.Text;
}
回答by zxc
In the stored procedure do something like this:
在存储过程中做这样的事情：
insert into table(date)
values(case when @InsertDate ='' then
null
else
@insertDate
end)
回答by Lloyd
You should just be able to supply nulldirectly as a parameter value:
您应该能够null直接作为参数值提供：
if (String.IsNullOrWhitespace(InsertDate.Text)) {
cmd1.Parameters["@InsertDate"].Value = null;
} else {
cmd1.Parameters["@InsertDate"].Value = InsertDate.Text;
}
Also switched to using String.IsNullOrWhitespace()to check for an empty string.
还切换到String.IsNullOrWhitespace()用于检查空字符串。
回答by Dr Schizo
cmd1.Parameters["@InsertDate"].Value = getDate ?? DBNull.Value;
The key being DBNull.Value. I imagine you have a nullable DateTime variable, i.e. DateTime? someVariable. You can test it for null and if it is use DBNull.Value(as shown above).
关键是 DBNull.Value。我想你有一个可为空的 DateTime 变量，即DateTime? someVariable. 您可以测试它是否为空以及是否使用DBNull.Value（如上所示）。
回答by beastieboy
Try using:
尝试使用：
if (string.IsNullOrWhiteSpace(InsertDate.Text))
{
cmd1.Parameters["@InsertDate"].Value = getDate;
}
else
{
cmd1.Parameters["@InsertDate"].Value = InsertDate.Text;
}
回答by SWeko
First of all, to be able to insert nullinto a database column, the column must accept nulls. Open the table in the designer, and see if the relevant column is nullable or not. If it's not and you try to insert a null value, you'll get the error:
首先，为了能够插入null到数据库列中，该列必须接受nulls。在设计器中打开表，查看相关列是否可以为空。如果不是，并且您尝试插入空值，则会收到错误消息：
Cannot insert the value NULL into column 'InsertDate', table 'TableName'; column does not allow nulls.
无法将值 NULL 插入列“InsertDate”、表“TableName”；列不允许空值。
That said, since you are using stored procedures to insert data, I would recommend using default values for the parameters on the procedure level, instead of burdening the aplication code. Than, you can just not set the parameter on the application side, and it would work correctly:
也就是说，由于您使用存储过程来插入数据，我建议在过程级别为参数使用默认值，而不是增加应用程序代码的负担。比，你不能在应用程序端设置参数，它会正常工作：
create procedure InsertIntoTable
-- ....other parameters here...
@InsertDate datetime = null -- this means that if the value is not supplied,
-- null is used by default
as begin
insert into TableName (InsertDate) values (@InsertDate)
end
and on the C# side, just do:
在 C# 方面，只需执行以下操作：
if (!string.IsNullOrWhitespace(InsertDate.Text)) {
// check to see if the entered text is actually a date
DateTime insertDate = ... some parsing/verification code...;
cmd1.Parameters["@InsertDate"].Value = insertDate;
}
Note that there is no elsebranch, so if the InsertDateis empty, the parameter is not sent at all.
注意这里没有else分支，所以如果InsertDate为空，则根本不发送参数。
回答by RAJESH KUMAR
To pass null value to db you can use DBNull.Value. Please try this
要将空值传递给 db，您可以使用 DBNull.Value。请试试这个
if (string.IsNullOrWhiteSpace(InsertDate.Text))
{
cmd1.Parameters["@InsertDate"].Value =DBNull.Value;
}
else
{
cmd1.Parameters["@InsertDate"].Value = InsertDate.Text;
}
回答by Rahul
if (//some condition)
{
cmd.Parameters.AddWithValue("@dateofBirth", txtdob.text);
}
else
{
cmd.Parameters.AddWithValue("@dateofBirth", DBNull.Value);
}
回答by Cuteboy_Max
Try something like this, using Add rather than AddWithValue..
尝试这样的事情，使用 Add 而不是 AddWithValue ..
DB.Parameters.Add("@date", SqlDbType.DateTime).Value = DBNull.Value;
回答by Samad Kakkad
try using this
尝试使用这个
System.Data.SqlTypes.SqlDateTime.Null
回答by Shanmuga priya
Try this logic if DBNull.Value is not worked .. use DateTime? null
如果 DBNull.Value 不起作用，请尝试此逻辑.. 使用 DateTime？空值
Ensure that ur database field should allow null
确保您的数据库字段应允许为空
dtexpiry!=null? dtexpiry :(DateTime?) null
dtexpiry！=空？dtexpiry :(日期时间？）空
