Pass a NULL in a parameter to a DateTime field in

问题:

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

The field I need to make NULL is a DateTime field

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

This gives me the error

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

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

I also tried

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

But it produces this error

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

Has anybody got any ideas?

回答1:

Try something like this, using Add rather than 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:

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

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

More details and background available at 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

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).

//change parameter to this

@SomeDate datetime = null (as suggested by chris)

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

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 ;
}