Pass null date value to SQL via VB

[TheBigO]

How do you pass a null date value to a SQL server table through a recordset in VB?

 

[jlbsloan]

Typically you can test if the date is NULL and just don't write the date field on that record and it will result in a NULL value in SQL

 

[Foada]

I am not sure if I understand the question completely but you could just use the constant vbNullChar. Am I missing something else? Anything is possible, the problem is I only have one lifetime.

 

[TheBigO]

Let me try to explain better:

I have a vb form. On that form is a masked edit box called mebClaimEndDate. THis date might not be filled in by the user so that it will be null. I have a class called Claim. When the user enters all information and hits Okay, I pass the form's information to the class...which then saves that information to the database table. The problem is that I get an error when I try to save a null value to a date field in the SQL Server table via the recClaim recordset object. I hope I am making some sense here. Thanks.


Step 1 (In the VB Form)
-------
strResponse = claim.Add


Step 2 (In the "Claim" Class Module)
-------

Public Function Add() As String

Add = ""
GetClaim (gstrEmpSSN)
With recClaim
.AddNew
Call SetRecordset(recClaim)
.Update
End With
Add = "OK"
End Function

With recClaim
.AddNew
Call SetRecordset(recClaim)
.Update
End With


Step 3(In the "Claim" Class Module)

---------

Private Sub SetRecordset(recE As Recordset)

With recE
...
!EmpSSN = mvarstrEmpSSN
If IsNull(mvardtClaimEndDate) = False Then
!ClaimEndDate = mvardtClaimEndDate
Else
!ClaimEndDate = ""
End If
...
End With
End Sub


In the "Claim" Class Module
---------------------------

Public Function GetClaim(sEmpSSN As String) As String
' This functions retrieves Claims data from the
' tblClaim table for a single employee
Dim strSql As String
mvarstrEmpSSN = sEmpSSN
GetClaim = ""

Set recClaim = New ADODB.Recordset
strSql = "SELECT * FROM tblClaim WHERE EmpSSN = '" & mvarstrEmpSSN & "' "
recClaim.Open strSql, gcon, adOpenStatic, adLockOptimistic, adCmdText

End Function

 

[jlbsloan]

Check the Table in SQL and see if it will take NULL values. If not then change your NULL date value to an empty string (VBNULLSTRING) before you pass it in or you may have to specify a default date value if that doesn't work