How to have a value of NULL for DateTime?

Question

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

You use `DBNull.Value` to represent `NULL` in ADO.NET regardless of the data type. — jmcilhinney, Jan 09 '19 at 04:43
Why are you specifying the size of a `date` parameter as 50? Either use the actual size, zero or don't set the size. You only need to specify a size for variable-width data types, e.g. `varchar`. 50 is often used in examples because that's the default for `varchar` and the like but to use it for `date` fields is misleading. — jmcilhinney, Jan 09 '19 at 04:45
Possible duplicate of [How do I set a nullable DateTime to null in VB.NET?](https://stackoverflow.com/questions/12595775/how-do-i-set-a-nullable-datetime-to-null-in-vb-net) — Vishwa Ratna, Jan 09 '19 at 04:46

score 2 · Answer 1 · answered Jan 09 '19 at 04:52

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.

How to have a value of NULL for DateTime?

1 Answers1