How to pass a Null date variable to SQL Server database

Question

I am looking for the best practice, real solution, to send a Null to a SQL Server 2008 R2 database table, when a date is unknown.

I read some inputs from a formview, and a date field maybe unknown. The database allows Null values in the field but the VB to store the Null prior to a parameterized query update is not working/eludes me.

    Dim tb2 As TextBox = TryCast(FormView1.FindControl("tbPurchDate"), TextBox)
    Dim purDT As Date
    If tb2.Text = "" Then
        IsDBNull(purDT)    ' Tried this along with other possible code
    Else
        purDT = Convert.ToDateTime(tb2.Text)
    End If

Any help would be greatly appreciated.

If you mean this, I tried it....Dim purDT As Nullable(Of DateTime) — htm11h
– htm11h, Commented Mar 12, 2013 at 20:50

Andrew Morton · Accepted Answer · 2023-11-05 18:58:21Z

13

If the date is unknown, send DbNull.Value as the parameter's value:

If dateIsUnknown Then
    cmd.Parameters.Add(New SqlParameter _
                       With {.ParameterName = "@purDT", _
                             .SqlDbType = SqlDbType.Date, _
                             .Value = DBNull.Value})
Else
    cmd.Parameters.Add(New SqlParameter _
                       With {.ParameterName = "@purDT", _
                             .SqlDbType = SqlDbType.Date, _
                             .Value = theDateVariable})
End If

Or if you prefer,

cmd.Parameters.Add(New SqlParameter With {
                   .ParameterName = "@purDT",
                   .SqlDbType = SqlDbType.Date,
                   .Value = If(dateIsUnknown, DBNull.Value, DirectCast(theDateVariable, Object))}
                  )

It is necessary to cast the variable to be of type Object so that the If operator has a common type to return. The Value parameter expects an Object.

edited Nov 5, 2023 at 18:58

answered Mar 12, 2013 at 22:27

Andrew Morton

25.3k9 gold badges66 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

htm11h Over a year ago

Mortan, Works, clean, formatting is outside my convention but I can live with that, and it works great. Thank you very much!!!

Andrew Morton Over a year ago

@marc11h You're welcome :) I wouldn't normally format it like that either, but it did fit into the available width.

rheitzman · Accepted Answer · 2013-03-12 21:03:04Z

1

It depends on the data method you are using to send the data to the server.

purDate is a variable of DateTime type and it cannot be set to null.

I suggest you use IsDate instead of testing length.

    Dim purDT As Date
    If Not IsDate(TextBox1.Text) Then
        purDT = Nothing
    Else
        purDT = Convert.ToDateTime(TextBox1.Text)
    End If

answered Mar 12, 2013 at 21:03

rheitzman

2,3053 gold badges20 silver badges36 bronze badges

Comments

Cristian Lupascu · Accepted Answer · 2013-03-12 22:35:30Z

1

You could use the Nullable(Of Date) to allow your purDT variable to also be Nothing:

Dim purDT As Nullable(Of Date) = Nothing

If tb2.Text <> "" Then
    purDT = Convert.ToDateTime(tb2.Text)
End If

However, the magic happens when you define the SQL parameter that will hold this value. The parameter should either be DBNull.Value or a valid (non-null) Date:

' initialize connection, command...

Dim param = New SqlParameter()
param.ParameterName = "NullableDate"
param.Value = IIf(purDT Is Nothing, DBNull.Value, purDT)

cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()

edited Mar 12, 2013 at 22:35

answered Mar 12, 2013 at 20:58

Cristian Lupascu

40.8k18 gold badges107 silver badges139 bronze badges

Comments

SolarBear · Accepted Answer · 2013-03-12 21:00:22Z

-1

Try this :

Dim purDT As Nullable(Of Date)

If tb2.Text = "" Then
    purDT = DBNull.Value
Else
    purDT = Convert.ToDateTime(tb2.Text)
End If

edited Mar 12, 2013 at 21:00

answered Mar 12, 2013 at 20:52

SolarBear

4,6174 gold badges42 silver badges53 bronze badges

Collectives™ on Stack Overflow

How to pass a Null date variable to SQL Server database

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related