How set date field to NULL in code

[MichaelaLee]

Hi everyone.
I wanted to know the best way to set a datetime field to null in VB code. We are using SQL Server. Take the following code
Set mCommand = New ADODB.Command
mCommand.CommandType = adCmdStoredProc
mCommand.CommandText = "nf_UpdateTermination_Import"
mCommand.ActiveConnection = connSQL
mCommand.Parameters.Refresh
mCommand.Parameters("@PatientId").Value = 1
mCommand.Parameters("@DOA").Value = NULL
mCommand.Parameters("@DOT").Value = VBNull
mCommand.Execute

Ok, with the above code I always get a Invalid use of null when I use NULL, if I use vbnull VB/ADO takes over and writes out 12/31/1899. I know that I could whatch for this value to tell if its a null, but in my option, we should not have to do this. What ways are you all doing this. THanks.
Michael

 

[Sheco]

Why not set your stored procedure so that the default value is null... then just don't pass anything for that param.

 

[SonOfEmidec1100]

I would use something like this, untested but NULLs should work.

Dim param As ADODB.Parameter

Set mcommand = New ADODB.Command
With mcommand
  .CommandType = adCmdStoredProc
  .CommandText = "nf_UpdateTermination_Import"
  .ActiveConnection = connSQL
  .Parameters.Refresh
  Set param = .CreateParameter("PatientId", adInteger, adParamInput, 1)
  .Parameters.Append param
  Set param = .CreateParameter("DOA", adDate, adParamInput, Null)
  .Parameters.Append param
  Set param = .CreateParameter("DOT", adDate, adParamInput, Null)
  .Parameters.Append param
  .Execute
End With
Set param = Nothing
Set mcommand = Nothing