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

ID: Q196590

The information in this article applies to:

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 1.5 or 2.0 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): 

   ARTICLE-ID: Q170380
   TITLE     : HOWTO: Display/Pass TimeStamp Value from/to SQL Server
For additional information, please see the following article in the Microsoft Knowledge Base, which enumerates the datatype constants used when passing parameters and their string equivalents: 

   ARTICLE-ID: Q181199
   TITLE     : HOWTO: Determine How ADO Will Bind Parameters
(c) Microsoft Corporation 1998. All Rights Reserved. Contributions by Margery Simms, Microsoft Corporation.

Additional query words: 

Keywords          : kbADO kbVBp 
Version           : WINDOWS:1.5,2.0,2.01; WINNT:6.0,6.5,7.0
Platform          : WINDOWS winnt
Issue type        : kbhowto

Last Reviewed: December 15, 1998