HOWTO: Pass a Timestamp to/from a SQL Stored Procedure w/ ADO

• ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.01, 2.1 SP2
• Microsoft SQL Server versions 6.0, 6.5, 7.0

SUMMARY

A SQL Server timestamp column automatically updates every time a row is inserted or updated. Despite the name, a SQL Server timestamp column is not based on the system time and does not contain a date or time value. Instead, the timestamp column is an array of binary values. The ActiveX Data Objects (ADO) adDBTimeStamp datatype is used for dates. Since a SQL Server timestamp is neither a date nor a time, do not use adDBTimeStamp to retrieve or pass SQL Server timestamp values. Use adVarBinary with a size of eight (8).

MORE INFORMATION

You can retrieve a row using a timestamp as criteria. The timestamp must be stored in a Variant variable. Use an ADO Command object. Then you can explicitly pass the timestamp as an ADO parameter of type adVarBinary and size eight (8).

Although you may retrieve records based on a timestamp value, never change the value of a SQL Server timestamp. Allow SQL Server to update timestamps automatically.

The following sample code creates a test table. The test table has a datetime column and a timestamp column.

The Visual Basic code in Command1 retrieves a record based on a datetime value. The timestamp is then stored in a Variant variable.

In Command2, the timestamp retrieved in Command1 is used to retrieve the same record. The ADO datatype for a timestamp is adVarBinary with a size of eight (8).

Steps to Accomplish Task

Create the Table and Insert Records

1. Open ISQL/ and then select the Pubs database.



2. Run the following code, to create the table and insert the records:

   
         CREATE TABLE whatime
        (
            id integer identity constraint p1 primary key nonclustered,
            aname char(10),
            tdate datetime,
            tstamp timestamp
        )
        Insert into  whatime(aname,tdate) values('Happy','10/31/98')
        Insert into  whatime(aname,tdate) values('Go','11/01/98')
        Insert into  whatime(aname,tdate) values('Lucky','11/02/98')
   
   
        select * from whatime  /* Just checking to see if it worked. */  

Create the Visual Basic Application

1. Open a new standard .exe project. Form1 is created by default.



2. From the Project menu, choose References, and then select the Microsoft ActiveX Data Objects Library.



3. Place three command buttons on Form1.



4. Paste the following code in the form code window:

   
         Option Explicit
   
         Private con As New ADODB.Connection
   
         'A SQL Server timestamp column is a binary array.
         ' We can store a timestamp in a Visual Basic Variant variable.
         Private varTStamp As Variant
   
         Private Sub Form_Load()
   
           Command1.Caption = "Retrieve by Date"
           Command2.Caption = "Retrieve by TimeStamp"
           Command2.Enabled = False
           Command3.Caption = "Quit"
   
          'This example uses the ODBC Provider with a Pubs DSN.
          'Modify your connect string as needed.
           con.CursorLocation = adUseClient
           con.Open ("DSN=Pubs;UID=sa;PWD=;")
   
         End Sub
   
         Private Sub Command1_Click()
   
           'Retrieve a row based on date
           'then store the retrieved timestamp column in a Variant.
   
           Dim rs As New ADODB.Recordset
   
           rs.ActiveConnection = con
           rs.Open "select * from whatime where tdate = '10/31/1998'"
   
           Debug.Print rs("id"), rs("aname"), rs("tdate"), rs("tstamp")
           Debug.Print rs("tstamp").Type   '128: timestamp is type adBinary
   
           ' Store the timestamp value, to retrieve the record in Command2.
           ' The timestamp must be stored in a Variant.
           varTStamp = rs("tstamp")
   
           Command2.Enabled = True
   
           rs.Close
           Set rs = Nothing
   
         End Sub
   
         Private Sub Command2_Click()
   
           'Retrieve a row using the timestamp value from Command1.
           'Use a Command object and a Parameter object to explicitly pass
           'the timestamp as adVarBinary, size 8.
   
           Dim cmd As New ADODB.Command
           Dim param As New ADODB.Parameter
           Dim rs As New ADODB.Recordset
   
           cmd.ActiveConnection = con
           cmd.CommandType = adCmdText
           cmd.CommandText = "select * from whatime where tstamp = ?"
   
          'The parameter must be type adVarBinary, size 8 to pass a timestamp.
   
           Set param = cmd.CreateParameter(, adVarBinary, adParamInput)
           param.Size = 8
   
           cmd.Parameters.Append param
   
           'Retrieve based on the Variant from Command1.
           param.Value = varTStamp
   
           Set rs = cmd.Execute()
   
           Debug.Print rs("id"), rs("aname"), rs("tdate"), rs("tstamp")
           Debug.Print rs("tstamp").Type     'Type 128: adBinary
   
           rs.Close
           Set rs = Nothing
           Set cmd = Nothing
   
         End Sub
   
         Private Sub Command3_Click()
   
            Unload Me
            End
   
         End Sub
   
         Private Sub Form_Unload(Cancel As Integer)
   
            con.Close
            Set con = Nothing
   
         End Sub 

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base, which discusses timestamp values and Remote Data Objects (RDO):

Q170380 HOWTO: Display/Pass TimeStamp Value from/to SQL Server

Q181199 HOWTO: Determine How ADO Will Bind Parameters

Additional query words:

Keywords : kbADO kbADO200 kbDatabase kbVBp kbVBp500 kbVBp600 kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2
Version : WINDOWS:1.5,2.0,2.01,2.1 SP2; winnt:6.0,6.5,7.0
Platform : WINDOWS winnt
Issue type : kbhowto