Updating column with Null value

Question

I have a date field (smalldatetime) in a table and in particular cases I need to set the date field to null. Currently, when setting the sqlparameter.value property to a date

endDateParam.Value = "5/15/2011"

the field is updated with the proper date. However, setting it to

endDateParam.Value = System.DbNull.Value

doesn't update the field.

Code behind:

Protected Sub ...
    For Each r As GridViewRow In gvEmployees.Rows
        SqlDataSource1.UpdateCommand = "<stored proc>"
        SqlDataSource1.UpdateCommandType = SqlDataSourceCommandType.StoredProcedure
        setParameters(r)
        gvEmployees.UpdateRow(r.RowIndex, False)
    Next
End Sub

Private updateParameters As New List(Of SqlParameter)()

Protected Sub setParameters(ByVal r As GridViewRow)
    updateParameters.Clear()

    Dim endDate As TextBox = TryCast(r.FindControl("txtEndDate"), TextBox)

    Dim endDateParam As New SqlParameter("@enddate", SqlDbType.SmallDateTime)
    endDateParam.Direction = ParameterDirection.Input
    endDateParam.Value = System.DBNull.Value
    updateParameters.Add(endDateParam)
End Sub

Protected Sub Sqldatasource1_Updating(ByVal source As Object, ByVal e As SqlDataSourceCommandEventArgs)
    e.Command.Parameters.Clear()
    For Each p As SqlParameter In updateParameters
        e.Command.Parameters.Add(p)
    Next
End Sub

Update

endDateParam.Value = System.Data.SqlTypes.SqlDateTime.Null

Still doesn't seem to update the field. No errors are returned.

Just a thought, is it possible the stored procedure does something when it sees a NULL parameter? What ends up in the endDate field in the table? — Sparky, Jan 24 '11 at 22:20
@Sparky - You were right. Within that sproc, we execute 2 others, one of which, after looking into it, was exiting based on a null value. — , Jan 25 '11 at 15:39

score 1 · Accepted Answer · answered Jan 24 '11 at 19:33

1

Use SqlDateTime.Null instead.

answered Jan 24 '11 at 19:33

Joe Stefanelli

132,803
19
237
235

score 0 · Answer 2 · answered Jan 24 '11 at 19:33

0

Try SqlDateTime.Null instead of system.dbnull.value

answered Jan 24 '11 at 19:33

Tim

5,371
3
32
41

score 0 · Answer 3 · answered Jan 24 '11 at 22:30

0

I would recommend that you use Sql Profiler to see exactly what is getting sent to the server. That should help you narrow down the problem

answered Jan 24 '11 at 22:30

Chris Dunaway

10,974
4
36
48

Updating column with Null value

3 Answers3