Adding NULL values to SQL DateTime Columns

During a recent project I found that leaving a date parameter as NULL triggered an exception during the SQL Insert and Updates of my Data Access Layer routines. After some digging I found that apparently an empty string is not treated as Nothing by SQL. It actually interprets "" as 1/1/1900. So you have to specifically pass an sqldatenull value.

Here's an example to illustrate:

Let's say you have a calendar control on your aspx page where users have the option to select a date. While adding the parameters to the sqlcommand parameters collection you simply add a conditional statement which checks for a null value , i.e. whether the user selected a date on the calendar.

Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes

If (me.Calendar1.selecteddate = nothing ) Then
cmd.Parameters("@Date").Value = sqldatenull
Else
cmd.Parameters("@Date").Value = me.Calendar1.selecteddate
End If

Update. The above code won't work without the declaration. However the example below works as I've just used it this minute:

If theDate = Nothing Then
'declare sqldatenull as SqlDateTime value with the value NULL
Dim sqldatenull As SqlDateTime
sqldatenull = SqlDateTime.Null
cmd.Parameters.AddWithValue("@theDate", sqldatenull)
Else
cmd.Parameters.AddWithValue("@theDate", theDate)
End If