CODEWITHSUNDEEP
asp.net.netvb.netvisual-studio
Brian.Scalabrine
Brian.Scalabrine

Reputation: 27

How to have a value of NULL for DateTime?

In my SQL database, I've declared DpsDate and AdmDate as DATE, also I've set them to allow nulls. When I run my application and leave these columns blank, I get this error:

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

This is where I'm a bit confused because I've set them to allow nulls so shouldn't the database accept no value? The application works if I set both dates to "01/01/1900". I've tried setting them to "00/00/0000" but I get the same error.

Here's what I have: 

        If tbNotifyDate.Text = "" Then
            cmd.Parameters.Add("@DpsDate", SqlDbType.Date, 50).Value = "01/01/1900"
        Else
            cmd.Parameters.Add("@DpsDate", SqlDbType.Date, 50).Value = tbNotifyDate.Text
        End If

        If tbAdmDate.Text = "" Then
            cmd.Parameters.Add("@AdmDate", SqlDbType.Date, 50).Value = "01/01/1900"
        Else
            cmd.Parameters.Add("@AdmDate", SqlDbType.Date, 50).Value = tbAdmDate.Text
        End If

Upvotes: 1

Views: 127

Answers (1)

jmcilhinney
jmcilhinney

Reputation: 54427

You need to use DBNull.Value to represent NULL in ADO.NET. Things like table adapters and Entity Framework, which are built on top of ADO.NET, can support nullable value types and thus use Nothing to represent NULL but ADO.NET itself predates nullable value types, so Microsoft had to invent a type specifically for the purpose of representing NULL.

I would suggest using the If operator to make the code more concise:

Dim value As Date

cmd.Parameters.Add("@AdmDate", SqlDbType.Date).Value = If(Date.TryParse(tbAdmDate.Text, value),
                                                          value,
                                                          CObj(DBNull.Value))

The CObj is required because the two possible results of If must be the same type and Date and DBNull are not the same type. By casting one possible result as type Object, they are both interpreted as type Object and the compiler is happy.

Note that, as I have written that example, this will save NULL if the TextBox contains anything that isn't a valid representation of a Date. You can use whatever validation is appropriate in your specific case or, if you've already validated, just check for an empty TextBox and use CDate.

Upvotes: 2

Related Questions