Dealing with DateTime and Null values

Question

I have variable called modifieddate of type DateTime, which can be set to null.
I fill in the variable using a datareader and set the value to nothing if the reader is empty
when I use the variable further down, the store procedure complaints that I am not providing the value. "Procedure or function 'tHistory_Insert' expects parameter '@modifieddate', which was not supplied"

Question: Any ideas on how to pass null values into the store procedure when the date in empty?

Step 1

Public modifieddate As Nullable(Of DateTime)

Step 2

If IsDBNull(dr("modifieddate")) = False Then

     modifieddate = DateTime.Parse(dr("modifieddate"))
Else

     modifieddate = Nothing
End If

Step 3

command.Parameters.Add("@modifieddate", SqlDbType.DateTime).Value = modifieddate
command.ExecuteNonQuery()

change stored procedure parameter to `@modifieddate datetime = null` — rs., Aug 15 '12 at 14:36

score 5 · Accepted Answer · answered Aug 15 '12 at 14:50

5

If it's nothing, I think you have to pass the DBNull.Value. Something like this:

If modifieddate Is Nothing then
  command.Parameters.Add(...).Value = DBNull.Value
Else
  command.Parameters.Add(...).Value = modifieddate
End If

answered Aug 15 '12 at 14:50

LarsTech

And you don't need to use Nullable(Of DateTime), you can just use DateTime and test for Nothing or use Date.MinValue as your nothing. – Robert Beaubien Aug 15 '12 at 15:09
@rs. I just tried it. My stored procedure worked without setting the parameter with `= null` and I passed it the `DBNull.Value`. Are you referring to the table itself? – LarsTech Aug 15 '12 at 15:18

1 Answers1