How to get SQL timestamp value using VB.NET

[Guest]

How do you get the actual value of a timestamp from a SQL Server 2000
database table using VB.Net? My recordset is returning a "System.Byte[]"
Type. How can I convert it into something usable?

 

[Jim Rand]

----------------
AccessFlagLookup
----------------

SELECT AccessFlagLookupID, AccessFlag, Description, LastUpdated,
LastUpdatedBy, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup

INSERT INTO [AgencyNET].[AccessFlagLookup] ([AccessFlag], [Description],
[LastUpdatedBy]) VALUES (@AccessFlag, @Description, @LastUpdatedBy);SELECT
AccessFlagLookupID, CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup
WHERE (AccessFlagLookupID = SCOPE_IDENTITY())

UPDATE [AgencyNET].[AccessFlagLookup] SET [AccessFlag] = @AccessFlag,
[Description] = @Description, [LastUpdated] = getutcdate(), [LastUpdatedBy]
= @LastUpdatedBy WHERE (([AccessFlagLookupID] =
@Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @Original_TS));SELECT
CAST(TS AS INT) AS TS FROM AgencyNET.AccessFlagLookup WHERE
(AccessFlagLookupID = @AccessFlagLookupID)

DELETE FROM [AgencyNET].[AccessFlagLookup] WHERE (([AccessFlagLookupID] =
@Original_AccessFlagLookupID) AND (CAST(TS AS INT) = @Original_TS))

-

 

[Guest]

M Lee,

What do you mean by "usable"?

Keep in mind that the timestamp value does not represent time in any way. It
is more accurately termed a rowversion value.

Kerry Moorman

 

[William Vaughn]

Kerry is right. It's just a value that's incremented each time the row is
changed. It has not been a "time" value for years thus the change of name.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant, Dad, Grandpa
Microsoft MVP
INETA Speaker
www.betav.com
www.betav.com/blog/billva
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------

M Lee,

What do you mean by "usable"?

Keep in mind that the timestamp value does not represent time in any way.
It
is more accurately termed a rowversion value.

Kerry Moorman

How do you get the actual value of a timestamp from a SQL Server 2000
database table using VB.Net? My recordset is returning a "System.Byte[]"
Type. How can I convert it into something usable?

 

[Brian Fuller]

I have bee trying to implement Optimistic Concurrency Control through Visual Studio 2008 and SQL Express 2005 using sql Timestamp function (rowversion does not work in my sql version).
Through trial and error I find the following works:
in VB
declare Private StoreTS as Object
define SqlParm = New SqlParameter("@TS", SqlDbType.Binary, 8)
in SQL
add new db field TS timestamp
inset parm as @TS binary(8) input/output

The timestamp can be retrieved on the read, stored in StoreTS, and returned in the update (this timestamp is really a rowversion and should never be changed by the user - simply used to ensure no other update has taken place between the read and update).