Microsoft KB Archive/196590

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

Article ID: 196590

Article Last Modified on 3/2/2005

-

APPLIES TO


 * Microsoft ActiveX Data Objects 1.5
 * Microsoft ActiveX Data Objects 2.0
 * Microsoft ActiveX Data Objects 2.01
 * Microsoft ActiveX Data Objects 2.1 Service Pack 2
 * Microsoft ActiveX Data Objects 2.5
 * Microsoft ActiveX Data Objects 2.6
 * Microsoft ActiveX Data Objects 2.7

-



This article was previously published under Q196590



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).

Create the Table and Insert Records
 Open ISQL/ and then select the Pubs database.  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
 Open a new standard .exe project. Form1 is created by default. From the Project menu, choose References, and then select the Microsoft ActiveX Data Objects Library. Place three command buttons on Form1.  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 </li></ol>

<div class="references_section">