Setting Dates to Null?

Using VB5 (RDO) and MS SQL Server, how do you set a datetime field on the database from a valid date to Null?
ZvAsked:
Who is Participating?
 
percosolatorConnect With a Mentor Commented:
There are two ways that I can immediately think of, one using an action query, the second, opening up a recordset and setting the field to null.

--------------------------------------
Option Explicit

Dim env As rdoEnvironment
Dim rdc As rdoConnection

Private Sub Command1_Click()

    'action query
    Dim SQL As String
   
    SQL = "UPDATE tblCustomers SET DateEntered = NULL WHERE CustomerID = 1061"
    rdc.Execute SQL, rdExecDirect
   
   
End Sub

Private Sub Command2_Click()

    Dim SQL As String
   
    Dim rd As rdoResultset
   
    SQL = "SELECT *" & _
        " FROM tblCustomers" & _
        " WHERE CustomerID = 1061"
       
    Set rd = rdc.OpenResultset(SQL, rdOpenDynamic, rdConcurLock)
   
    If (Not (rd.BOF And rd.EOF)) Then
   
        rd.Edit
        rd!DateEntered = Null
        rd.Update
       
    End If
   
    rd.Close
   
End Sub

Private Sub Form_Load()


    Set env = rdoEnvironments(0)
   
    Set rdc = env.OpenConnection("ODBC", , , "DSN=TEST;UID=SA;PWD=;DATABASE=TEST")
       
End Sub
------------------------------

which ever method you choose is up to you

advantages:

          Command1's method will be faster, but if the field you are updating (not in your case) might have apostrophes or quotation marks in it, then you are going to have a problem with unclosed quotes.

          Command2's method has no problem with quotations, but is going to be a hair slower.


0
 
ZvAuthor Commented:
Edited text of question
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.