How to assign timestamp value to a record

One more thing, I'm able to assign values to other fields, string, integer, etc.

What type of field is your "timestamp" ? On what database are you updating ?
When you try this, is it succesfull or not ?
rs.fields("timestamp").value = format(now(),"mm/dd/yyyy")) or rs.fields("timestamp").value = format(now(),"dd/mm/yyyy"))

The timestamp field is <timestamp> in SQL server 2000 and adVarBinary in VB.

Tried both and doesn't work. Throws the same error.

I noticed (watch) that the variant variable holding the updated timestamp is a byte array (0 to 7). Tried to copy the values individually but it doesn't work. This time it does not throw an error, just that the new values are not updated. Is there a specific function to copy byte values. Tried CByte(vTimeStamp(i)) unsuccessfully.

loop
rs.fields("timestamp").value (i) = vTimeStamp(i)
end loop

When reading the timestamp from a field in an ADO recordset, save it in a variant variable (no conversion):
  dim vntTimeStamp as Variant
  vntTimeStamp = rsData.Fields("TimeStamp").Value

If your data comes from an ADO command parameter instead in a recordset, then save the parameter value the same way in a variant variable:
  vntTimeStamp = parTimeStamp.Value

Saving the timestamp in a variant is the only way to preserve the format that SQL Server will recognize when you pass it back for updating or deleting. If you need to see the timestamp in some other form then copy it into the appropriate variable and then convert it with TimestampToHex or something like that but don't disturb the original value in the variant variable.

For updating, pass the timestamp back to a stored procedure in an ADO input/output parameter, formatted exactly as follows. The SP can check the current timestamp and can return the new timestamp after the update (note the ADO parameter type is BINARY and the size is 8):
  Dim adoUpdate As New Command
  Dim parID As Parameter
  Dim parValue As Parameter
  Dim parTimeStamp As Parameter
  With adoUpdate    
    .ActiveConnection = {your connection}
    .CommandText = "sp_my_update"
    .CommandType = adCmdStoredProc
    Set parID = .CreateParameter("ID", adInteger, adParamInput)
    Set parValue = .CreateParameter("ID", adDouble, adParamInput)
    Set parTimeStamp = .CreateParameter("TimeStamp", adBinary, adParamInputOutput, 8)
    .Parameters.Append parID
    .Parameters.Append parValue
    .Parameters.Append parTimeStamp
    parID.Value = {something}
    parValue.Value = {something}
    parTimeStamp.Value = vntTimeStamp
    .Execute
  End With
  vntTimeStamp = parTimeStamp.Value  'Returns the new timestamp.

This is a sample update stored procedure that uses the built-in SQL Server function "tsequal" to verify the timestamp before updating, and then passes back the new timestamp after the update is finished:
  CREATE PROCEDURE sp_my_update (@my_id smallint, @my_value int, @my_timestamp timestamp OUTPUT)
  AS
  UPDATE table
  SET table.value = @my_value WHERE table.id = @my_id and tsequal(table.timestamp, @my_timestamp)
  SELECT @my_timestamp = table.timestamp FROM table WHERE table.id = @my_id

For deleting, you only need to pass the timestamp to the stored procedure in an ADO input parameter so the SP can check the current timestamp:
  Dim adoDelete As New Command
  Dim parID As Parameter
  Dim parTimeStamp As Parameter
  With adoUpdate    
    .ActiveConnection = {your connection}
    .CommandText = "sp_my_delete"
    .CommandType = adCmdStoredProc
    Set parID = .CreateParameter("ID", adInteger, adParamInput)
    Set parTimeStamp = .CreateParameter("TimeStamp", adBinary, adParamInputOutput, 8)
    .Parameters.Append parID
    .Parameters.Append parTimeStamp
    parID.Value = {something}
    parTimeStamp.Value = vntTimeStamp
    .Execute
  End With

This is a sample delete stored procedure that uses the built-in SQL Server function "tsequal" to verify the timestamp before deleting:
  CREATE PROCEDURE sp_my_delete (@my_id smallint, @my_timestamp timestamp)
  AS
  DELETE FROM table WHERE table.id = @my_id and tsequal(table.timestamp, @my_timestamp)

How are your fields in the database declared ?
If timestamp is a date-time field, you must change
"2003-08-21T09:00:00.000" into "2003-08-21 09:00:00"
format(me.tTimestamp.Text,"yyyy-mm-dd hh-nn-ss")

The field type is timestamp, length 8, not a date-time field.

I have declared a variant to hold the timestamp value.

1. Current record in recordset is edited
2. The fields are copied and mapped to the corresponding parameter fields in the SP along with the vTimeStamp
3. vTimeStamp is updated as expected
4. Update the new timestamp in the recordset (this fails)

rs.Fields("timestamp").Value = vTimeStamp <---- Fails

I mentione earlier that the variant turns out to be a byte array. Is there any issues in copying byte arrays?

try this ...

rs.Fields("timestamp").Value = format(vTimeStamp ,"{ts 'YYYY-MM-DD HH:NN:SS'}")

what is the ADODB type of yourtimestamp field?

debug.print rs.Fields("timestamp").type

I tried this with 'adDBTimeStamp' and it works ....

    Dim d As Variant ' it works also with date
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Fields.Append "ts", adDBTimeStamp
    rs.Open
    d = #5/15/2004#
    rs.AddNew
    rs.Fields("ts").Value = d

The field is type 128.

The timestamp here is _not_ date time... but a unique binary number as defined by SQL Server 2000 listed below.

timestamp
timestamp is a  data type that exposes automatically generated binary numbers, which are guaranteed to be unique within a database. timestamp is used typically as a mechanism for version-stamping table rows. The storage size is 8 bytes.

Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

A future release of Microsoft® SQL Server&#8482; may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft® SQL Server&#8482; 2000 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. rowversion is subject to the behaviors of data type synonyms. For more information, see Data Type Synonyms.

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type:

CREATE TABLE ExampleTable (PriKey int PRIMARY KEY, timestamp)

If you do not supply a column name, SQL Server generates a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you specify rowversion.

A table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the position of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

Field type 128 is 'adBinary' and works in similar mode to a adVarchar ... this works ...

    Dim d As Variant ' it works also with date
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Fields.Append "ts", adBinary, 8
    rs.Open
    d = #5/15/2004#
    rs.AddNew
    rs.Fields("ts").Value = "20040428"

if your rs.Fields("ts").DefinedSize is 8 try this ...

rs.Fields("timestamp").Value = format(vTimeStamp ,"YYYYMMDD")

mmusante,

Thanks for your suggestion but if you read my previous post, this has nothing to-do with "datetime". It is just an internal SQL server datatype to maintain versioning of the row data.

Try this ...

strTs=""
for j=0 to ubound(vTimeStamp )
    strTs = strTs & vTimeStamp(j)
next
rs.Fields("timestamp").Value = strTs

Tried, same error "multi-step error". This is because rs.Fields("timestamp").Value  is an array, so you just can't simply assign a string to it.

I have also tried manually updating the array but the byte value doesn't change.

rs.Fields("timestamp").Value(0) = 0
rs.Fields("timestamp").Value(1) = 1

The code above does not modify the contents of the array and no error is returned. Really puzzles me. Other records can be modified which implies the recordset is updateable.

Can you post all properties of rs.Fields("timestamp")?

(I think that your problem is at ADO level bacause you gat a DB error usually at .Update time)

Which version of ADO are you using?

Try this ... (found on http://www.visualbasicforum.com/t94089.html)

rs.Fields("timestamp").AppendChunk  vTimeStamp

Tried.

Error: Operation is not allowed in this context.

Will read the article later. Out of office now..... thanks for your help.

I'm still unable to resolve this problem. any help is very much appreciated.

Please post your code with appendChunk ...

Below is the actual code used.

m_rsDocument.Fields("timestamp").AppendChunk m_vTimeStamp

If you want to re-create the situation, you could create a column in the DB with the type 'timestamp'. Query the table and try to modify the colum timestamp returned in the recordset.

I found a C++ example here (http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3BQ282932) but in the code they say ...

...
//Cannot set the value for a timestamp field, only setting the binary fields
...

are you sure that is it possible to write on a timestamp column?

I'm not sure whether it is possible to write to a timestamp column but I surely can read from it and assigned it to another byte array.

I'm starting to think that the timestamp column is read-only:

create table pippo(timestamp,pippo varchar(30))

insert into pippo(pippo) values('hello') /* this works*/

insert into pippo(timestamp,pippo) values (123456,'bye') /* I get an error 273 here */

update pippo set timestamp = 123456 /* I get an error 272 here */

I think so too. The problem is that this timestamp is used to control concurrency in a multi-user environment. If a users attempts to save data, the SP will check whether the timestamp is identical, otherwise it will warn the user that the data (row) has been updated by another user.

Here I have a large recordset where the user can scroll first, prev, next and last. When the user saves data, assuming that there is no concurrency issue, an updated timestamp is returned by the SP. This timestamp is stored in the variant vTimeStamp.

This is where the problem begins. If the user tries to save the same row again, a concurrency error will result (in actual fact, there should not be concurrency error) because the recordset holds the previous timestamp and not the one in vTimeStamp.

Re-querying the entire recordset is not efficient. Everything could be solved if I could get to update the damn timestamp......

 

I think you could try to work with a disconnected recordset and make a copy to it of your recordset ...

I heard about somebody that unconnect the recordset from the DB by setting ActiveConnection to nothing but I never tried it ...

I forgot to mention that it is a disconnected recordset that I'm working with. Each time a save is called, I just simply copy the column(rs) values to the corresponding parameters in the SP and update accordingly. This is where SQL Server will assign a new timestamp which will be returned via vTimeStamp.

I just cannot update the disconnected recordset timestamp value!
Only the timestamp now is the headache!!