Search code examples
.netdatetimesqlparameter

Dealing with potentially null datetime value in a SQLParameter

I am trying to do what I thought was a pretty straightforward thing, but am having issue. I have code in a .NET application to insert a new record into a table using SQLCommand.

I am passing in what could either be a datetime value or it could be null. The code below doesn't seem to work though:

command.Parameters.AddWithValue("@SettleDate", 
IIf(String.IsNullOrEmpty(SettleDate), Nothing, DateTime.Parse(SettleDate)))

Where "SettleDate" is a string containing a date that could be null. I would think this code would cover the condition of not attempting to parse a null value, however when it is ran I get the following error which looks to be from the Datetime.Parse method:

String reference not set to an instance of a String.
Parameter name: s

How could this be? If "SettleDate" is null then it should never reach the parse method.

Solution

  • You should use the short circuit opertator of VB.NET together with DbNull.Value

    If(String.IsNullOrEmpty(SettleDate), DBNull.Value, DateTime.Parse(SettleDate)))

    The IF operator, contrary to IIF function doesn't try to evaluate both sides of the expression if the first one is true. Note also that to pass a null value to an SqlParameter you use DbNull.Value.