将参数中的NULL传递给存储过程中的DateTime字段

I have a stored procedure which updates a database using the parameters I supply but I'm having trouble passing a NULL to the stored procedure

我有一个存储过程使用我提供的参数更新数据库，但我无法将NULL传递给存储过程

The field I need to make NULL is a DateTime field

我需要使NULL的字段是DateTime字段

DB.Parameters.AddWithValue("@date", NULL)

This gives me the error

这给了我错误

'NULL' is not declared. 'Null' constant is no longer supported; use 'System.DBNull' instead

'NULL'未声明。不再支持'Null'常量;请改用“System.DBNull”

So I tried

所以我试过了

DB.Parameters.AddWithValue("@date", DBNull.Value.ToString())

But this produces the value 1900-01-01 00:00:00.000 in the column as it's passing a "" to the field

但是这会在列中生成值1900-01-01 00：00：00.000，因为它将“”传递给字段

I also tried

我也试过了

DB.Parameters.AddWithValue("@date", DBNull.Value)

But it produces this error

但它产生了这个错误

Value of type 'System.DBNull' cannot be converted to 'String'.

“System.DBNull”类型的值无法转换为“String”。

Has anybody got any ideas?

有没有人有任何想法？

4 个解决方案

#1

Try something like this, using Add rather than AddWithValue:

尝试这样的事情，使用Add而不是AddWithValue：

DB.Parameters.Add("@date", SqlDbType.DateTime).Value = DBNull.Value;

#2

Or you can add your parameter like this, which gives it a null value in the database if your variable is null:

或者您可以像这样添加您的参数，如果您的变量为null，则在数据库中为其赋予空值：

DB.Parameters.AddWithValue("@date", myDateTime ?? (object)DBNull.Value);

you need to set it as a nullable type as Amit mentioned.

你需要将它设置为Amit提到的可空类型。

More details and background available at http://evonet.com.au/overview-of-c-nullable-types/

更多详情和背景，请访问http://evonet.com.au/overview-of-c-nullable-types/

#3

Try this

尝试这个

If you are using class and its property and those property values are used as parameter then you can do this

如果您正在使用类及其属性，并且这些属性值用作参数，那么您可以执行此操作

chnage the signature of the property to

chnage属性的签名

public DateTime? myDateTime = null;// Here ? is used to make the property nullable.

Also if your date column is of type varchar then correct it to Date (Sql2008) / DateTime(2005).

此外，如果您的日期列是varchar类型，则将其更正为Date（Sql2008）/ DateTime（2005）。

//change parameter to this

//将参数更改为此

@SomeDate datetime = null (as suggested by chris)

Allow the field to accept null and then pass the value as

允许该字段接受null，然后将值传递为

DB.Parameters.Add("@date", DBNull.Value)

#4

This is an example. It's work for me

这是一个例子。这对我有用

if(obj.myDate == DateTime.MinValue)
{
    aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = DBNull.Value;
}
else
{
    aCommand.Parameters.Add("dateParameter", SqlDbType.Date).Value = obj.myDate ;
}

#1