DBNull.value doesn't CLEAR a date

[Guest]

I have effectively managed to get SQL server to stop complaining when I
provide the DBNULL.Value to my parameter, but it doesn't replace (I would
like it to ) the date currently in that field with the desired NULL value:

If IsDate(NewCP.StartAuthLift) Then
pStartAuthLift.Direction = ParameterDirection.Input
pStartAuthLift.Value = NewCP.StartAuthLift
.Parameters.Add(pStartAuthLift)
Else
pStartAuthLift.Direction = ParameterDirection.Input
pStartAuthLift.Value = DBNull.Value
.Parameters.Add(pStartAuthLift)
End If


'=======if you desire to see it in context= - there is more code below====

Public Shared Sub UpdateCP(ByVal NewCP As ControlPanel, ByVal OldCP As
ControlPanel) ' As DBResult
Dim conMembers As SqlConnection = GetMembershipConnection()
Dim sSqlCommand As String = "Update CP " _
& "Set StartAuthLift = @NewStartAuthLift, " _
& "EndAuthLift = @NewEndAuthLift, " _
& "LiftAuthReason = @NewLiftAuthReason, " _
& "AdditionalAssistance = @NewAdditionalAssistance, " _
& "RefreshDate = @NewRefreshDate, " _
& "TimeAvailable = @NewTimeAvailable"


conMembers.Open()
Dim cmdCP As New SqlCommand(sSqlCommand, conMembers)
Dim sqldatenull As DateTime


Dim pStartAuthLift As New SqlParameter("@NewStartAuthLift",
SqlDbType.DateTime)
Dim pEndAuthLift As New SqlParameter("@NewEndAuthLift",
SqlDbType.DateTime)
Dim pRefreshDate As New SqlParameter("@NewRefreshDate",
SqlDbType.DateTime)
Dim pTimeAvailable As New SqlParameter("@NewTimeAvailable",
SqlDbType.DateTime)



With cmdCP
If IsDate(NewCP.StartAuthLift) Then
pStartAuthLift.Direction = ParameterDirection.Input
pStartAuthLift.Value = NewCP.StartAuthLift
.Parameters.Add(pStartAuthLift)
Else
pStartAuthLift.Direction = ParameterDirection.Input
pStartAuthLift.Value = DBNull.Value
.Parameters.Add(pStartAuthLift)
End If

 

[Lonifasiko]

I think your should specify pStartAuthLift parameter as Nullable. Taht
could be the problem.

Try this it out and let us know:

pStartAuthLift.IsNullable = true

Regards.

 

[Lonifasiko]

You could also take a look at this article:

http://www.c-sharpcorner.com/Code/2003/Sept/EnterNullValuesForDateTime.asp

Regards.