Timestamp Value - please help

[Guest]

I am using a class to store values from a query, but I have found no documentation to show how to capture the actual value of a timestamp from a SQL Server 2000 database table using VB.Net. I need to use the timestamp in the Where clause of an update statement, but the value returned is "System.Byte[]", not the actual value. Here is some of my code

'Assume I've pulled these fields from table (TS is timestamp
Select EmpID, FirstName, LastName, TS From Employee

Private mintEmpID As Intege
Private mstrFN As Strin
Private mstrLN As Strin
'I'm using a byte array to store the TS. Is that the right way to do it
Private mbytTS(8) As Byt

'Here's my data
With ds.Tables(0).Rows(0
mintEmpID = .Item("EmpID"
mstrFN = .Item("FirstName"
mstrLN = .Item("LastName"
mbytTS = .Item("TS"
End Wit

When the user changes the data and saves, the update query using the TS in the Where clause fails because mbytTS reads 'System.Byte[]' instead of the actual timestamp

Private sql as Strin
sql = "Update Employees Set FirstName='" & mstrFN & "', LastName='" & mstrLN & "' Where EmpID = " & mintEmpID & " And TS =" & mybtT

I'm missing the key thing here, which is the actual timestamp value. Can someone please tell me how to capture the timestamp into a variable, and reference it correctly in the update query? Thank you in advance for your help

 

[DalePres]

A timestamp value isn't really designed to be retrieved or queried against.
It is not even guaranteed to remain the same over time since each time you
change or update a row, the timestamp changes. Rows updated later will
always have a higher timestamp value than rows updated earlier. That's the
only assumption you should make. Timestamps are intended to allow you to
sort based on update time but do not represent actual times.

If you want to record row update/creation times for later retrieval, use a
datetime column with GETDATE() so that all the times are in server time.
Though not guaranteed to give as reliable of a timestamp as a timestamp
column would, in all but the most rare of situations, it would be completely
reliable and/or acceptable.

Dale

I am using a class to store values from a query, but I have found no

documentation to show how to capture the actual value of a timestamp from a
SQL Server 2000 database table using VB.Net. I need to use the timestamp in
the Where clause of an update statement, but the value returned is
"System.Byte[]", not the actual value. Here is some of my code:

'Assume I've pulled these fields from table (TS is timestamp)
Select EmpID, FirstName, LastName, TS From Employees

Private mintEmpID As Integer
Private mstrFN As String
Private mstrLN As String
'I'm using a byte array to store the TS. Is that the right way to do it?
Private mbytTS(8) As Byte

'Here's my data.
With ds.Tables(0).Rows(0)
mintEmpID = .Item("EmpID")
mstrFN = .Item("FirstName")
mstrLN = .Item("LastName")
mbytTS = .Item("TS")
End With

When the user changes the data and saves, the update query using the TS in

the Where clause fails because mbytTS reads 'System.Byte[]' instead of the
actual timestamp.

Private sql as String
sql = "Update Employees Set FirstName='" & mstrFN & "', LastName='" &

mstrLN & "' Where EmpID = " & mintEmpID & " And TS =" & mybtTS

I'm missing the key thing here, which is the actual timestamp value. Can

someone please tell me how to capture the timestamp into a variable, and
reference it correctly in the update query? Thank you in advance for your
help.

 

[Guest]

Thank you for your reply. My apologies for being confusing. The column is in SQL Server 2000, and its datatype is Timestamp. I'm aware it is not an actual time, but in every .Net/SS book I've read, they recommended the timestamp for multiuser environments, because it updates automatically when a row is updated and is guaranteed (?) to be unique. By comparing the value in a datatable against the one in the database, I would be assured that if they don't match, the database was already changed (and I can take steps to inform the user). Unfortunately, none of the books go so far as to show how to go from datacentric to object-oriented, thus my question

----- DalePres wrote: ----

A timestamp value isn't really designed to be retrieved or queried against
It is not even guaranteed to remain the same over time since each time yo
change or update a row, the timestamp changes. Rows updated later wil
always have a higher timestamp value than rows updated earlier. That's th
only assumption you should make. Timestamps are intended to allow you t
sort based on update time but do not represent actual times

If you want to record row update/creation times for later retrieval, use
datetime column with GETDATE() so that all the times are in server time
Though not guaranteed to give as reliable of a timestamp as a timestam
column would, in all but the most rare of situations, it would be completel
reliable and/or acceptable

Dal

 

[DalePres]

BOL indicates that the Timestamp column is syntactically identical to a
binary(8). A nullable timestamp is equal to a varbinary. The topic "Cast
and Convert" in BOL describes the implicit conversions possible for a
timestamp.

Dale

Thank you for your reply. My apologies for being confusing. The column is

in SQL Server 2000, and its datatype is Timestamp. I'm aware it is not an
actual time, but in every .Net/SS book I've read, they recommended the
timestamp for multiuser environments, because it updates automatically when
a row is updated and is guaranteed (?) to be unique. By comparing the value
in a datatable against the one in the database, I would be assured that if
they don't match, the database was already changed (and I can take steps to
inform the user). Unfortunately, none of the books go so far as to show how
to go from datacentric to object-oriented, thus my question.

 

[Guest]

Thank you. The following topic in MSDN gives an example of converting hex to string. I tweaked the function given in the following link slightly and so far it has worked. I'm including it if it helps anyone else

http://msdn.microsoft.com/library/d...n-us/cpref/html/frlrfSystemByteClassTopic.asp

From what I've read, Timestamp is an array of byte, so returning it from a datatable would look like this

Private mintEmpID As Intege
Private mstrFN As Strin
Private mstrLN As Strin
Private mbytTS(8) As Byt

'Here's my data
With ds.Tables(0).Rows(0
mintEmpID = .Item("EmpID"
mstrFN = .Item("FirstName"
mstrLN = .Item("LastName"
mbytTS = .Item("TS"
End Wit

Now, to format the timestamp for use in a Where clause

Private sql as Strin
Private str as Strin
'Pass the timestamp to conversion function
sql = "Update Employees Set FirstName='" & mstrFN & "', LastName='" & mstrLN & "' Where EmpID = " & mintEmpID & " And TS =" & ToHexString(mbytTS

Public Shared Function ToHexString(ByVal bytes() As Byte) As Strin
'Documentation states timestamp must begin with 0x
Dim hexStr As String = "0x
Dim i As Intege

For i = 0 To bytes.Length -
'If it's a single digit, append a zero in front of it
If Hex(bytes(i)).Length = 1 The
hexStr &= 0 & Hex(bytes(i)
Els
hexStr = hexStr + Hex(bytes(i)
End I
Next

Return hexSt

End Functio

Once the record is updated, I retrieve the updated TS for future updates

----- DalePres wrote: ----

BOL indicates that the Timestamp column is syntactically identical to
binary(8). A nullable timestamp is equal to a varbinary. The topic "Cas
and Convert" in BOL describes the implicit conversions possible for
timestamp

Dal

Thank you for your reply. My apologies for being confusing. The column i

in SQL Server 2000, and its datatype is Timestamp. I'm aware it is not a
actual time, but in every .Net/SS book I've read, they recommended th
timestamp for multiuser environments, because it updates automatically whe
a row is updated and is guaranteed (?) to be unique. By comparing the valu
in a datatable against the one in the database, I would be assured that i
they don't match, the database was already changed (and I can take steps t
inform the user). Unfortunately, none of the books go so far as to show ho
to go from datacentric to object-oriented, thus my question