Solved

Setting Dates to Null?

Posted on 1998-09-03
2
180 Views
Last Modified: 2010-04-30
Using VB5 (RDO) and MS SQL Server, how do you set a datetime field on the database from a valid date to Null?
0
Comment
Question by:Zv
2 Comments
 

Author Comment

by:Zv
ID: 1432834
Edited text of question
0
 
LVL 2

Accepted Solution

by:
percosolator earned 200 total points
ID: 1432835
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question