Timestamp and SQL 2000

[henares]

I am using sql 2000 with VB.NET. I am trying to enforce data integrity by checking the timestamp field before updating records. My questions are:
1. I've created a table with a timestamp field. I've created records from EM and from a program, when I use EM to show the records, the timestamp field says <binary> for all records. I am not sure if values were generated. If not, what do I have to do say in EM so that values are generated for the timestamp field?
2. I can read in values (except the timestamp field) from my table using the sqldatareader but don't know how to tackle the timestamp field? Like what data type to use to receive the timestamp field and what statement to use to convert to that data type.

Thanks in advance.

 

[jitter]

1. The timestamp data type column is filled automatically by SQL Server. *You cannot update the timestamp column.

2. If the column allows nulls the type is varbinary(8) and binary(8) if no nulls.

This may or may not be what you had in mind when you selected timestamp. If you want to record the actual date or time the record was updated/inserted then you would want to use a datetime or smalldatetime type for the column.

Hope this helps.

 

[henares]

Thanks. But...

What I am trying to do to enforce data integrity is to keep the value of the timestamp (of the record for updating) before updating and to make sure that the timestamp is the same before updating (the record).

I really have 2 questions:

1. what variable type (no binary nor varbinary in VB.NET) to keep the timestamp?

2. what is the statement to use to transfer to that variable. I am using sqldataread, so for other fields I use a statement like - destinationvariable = reader.item(&quot;fieldname&quot.tostring

How do I do this if the field is a timestamp?

Thanks again in advance.

 

[Zarcom]

I am not sure but it looks like your field is of binary datatype (taken from post 1). You want your field to be of type datetime or smalldatetime depending on the accuracy that you need. Check your SQL2000 help for the exact precision of both.

Sql can take a string in and convert it to the datetime type as long as it is in the correct format. The other option is to use the date/datetime variable types in VB.

As far as displaying it is exactly the same as you have been doing so far.

HTH That'l do donkey, that'l do

 

[link9]

If you just want a smalldatetime field to have the time of INSERT, then put a trigger on the table for inserts that will populate the field for you with the sql function:

getdate()