Pass a NULL in a parameter to a DateTime field in a stored procedure

Question

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?

Answer 1

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/

Answer 2

Try something like this, using Add rather than AddWithValue:

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