How To Pass a Timestamp to/from a SQL Stored Procedure w/ ADO

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

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):
170380 How To 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:
181199 How To Determine How ADO Will Bind Parameters
(c) Microsoft Corporation 1998, All Rights Reserved. Contributions by Margery Simms, Microsoft Corporation.

Propiedades

Id. de artículo: 196590 - Última revisión: 03/02/2005 - Revisión: 1

Comentarios