Solved

How to assign timestamp value to a record

Posted on 2004-04-26
30
535 Views
Last Modified: 2013-12-25
Hi,

I get an error (multi-step error) when trying to assign a timestamp value to a record.

rs.Fields("timestamp").Value = vTimeStamp

The variable vTimeStamp is declared as a variant. This variable is updated through a store procedure and contains valid timestamp data. The problem is when I assign the vTimeStamp to the corresponding field in the record, it throws a multi-step error.

Thanks for any help

0
Comment
Question by:craman
  • 13
  • 12
  • 3
  • +1
30 Comments
 

Author Comment

by:craman
Comment Utility
One more thing, I'm able to assign values to other fields, string, integer, etc.
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
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"))
0
 

Author Comment

by:craman
Comment Utility
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
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
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)
0
 
LVL 53

Expert Comment

by:Dhaest
Comment Utility
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")
0
 

Author Comment

by:craman
Comment Utility
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?
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
try this ...

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

Expert Comment

by:mmusante
Comment Utility
what is the ADODB type of yourtimestamp field?

debug.print rs.Fields("timestamp").type
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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
0
 

Author Comment

by:craman
Comment Utility
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.

0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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")



0
 

Author Comment

by:craman
Comment Utility
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.

0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
Try this ...

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

Author Comment

by:craman
Comment Utility
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.


0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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)
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
Which version of ADO are you using?
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
Try this ... (found on http://www.visualbasicforum.com/t94089.html)

rs.Fields("timestamp").AppendChunk  vTimeStamp
0
 

Author Comment

by:craman
Comment Utility
Tried.

Error: Operation is not allowed in this context.

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

0
 

Author Comment

by:craman
Comment Utility
I'm still unable to resolve this problem. any help is very much appreciated.
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
Please post your code with appendChunk ...
0
 

Author Comment

by:craman
Comment Utility
Below is the actual code used.

m_rsDocument.Fields("timestamp").AppendChunk m_vTimeStamp
0
 

Author Comment

by:craman
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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?
0
 

Author Comment

by:craman
Comment Utility
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.
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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 */
0
 

Author Comment

by:craman
Comment Utility
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......

 
0
 
LVL 6

Expert Comment

by:mmusante
Comment Utility
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 ...
0
 

Author Comment

by:craman
Comment Utility
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!!
0
 

Accepted Solution

by:
CetusMOD earned 0 total points
Comment Utility
PAQed, with points refunded (350)

CetusMOD
Community Support Moderator
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now