Insert NULL DateTime with SQL Server stored procedure from C#

Question

I have written a SQL Server stored procedure that includes a DateTime parameter. This parameter could be null or contain a valid date value.

So the definition of my variable in my procedure is below:

@piPurchase_Date DATETIME = NULL

From C# I add the parameter as follows:

SqlParameter parameter = new SqlParameter("@piPurchase_Date", SqlDbType.DateTime);
parameter.Value=datePurchaseIssueDate.Checked ? datePurchaseIssueDate.Value : SqlDateTime.Null;

So far all seems OK but when I call the Update method on the DataAdaptor I receive the following error:

Failed to convert parameter value from a SqlDateTime to a DateTime.

Any ideas?

I'm using SQL Server 2008 and C#4.0

Thanks in advance

Answer 1

Yes - use DBNull.Value in C#:

SqlParameter parameter = new SqlParameter("@piPurchase_Date", SqlDbType.DateTime);
parameter.Value = datePurchaseIssueDate.Checked ? datePurchaseIssueDate.Value : DBNull.Value;

to pass a NULL to a SQL Server stored procedure