Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 418
Jamie Taylor Avatar asked Mar 03 '11 14:03

Jamie Taylor


2 Answers

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/

like image 90
Robbie Mills Avatar answered Sep 28 '22 13:09

Robbie Mills


Try something like this, using Add rather than AddWithValue:

DB.Parameters.Add("@date", SqlDbType.DateTime).Value = DBNull.Value;
like image 40
LukeH Avatar answered Sep 28 '22 14:09

LukeH