Solved

How to assign timestamp value to a record

Posted on 2004-04-26
30
569 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 12
  • 3
  • +1
30 Comments
 

Author Comment

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

Expert Comment

by:Dhaest
ID: 10925588
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
ID: 10925739
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 53

Expert Comment

by:Dhaest
ID: 10925798
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
ID: 10925800
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
ID: 10925932
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
ID: 10928279
try this ...

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

Expert Comment

by:mmusante
ID: 10928311
what is the ADODB type of yourtimestamp field?

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

Expert Comment

by:mmusante
ID: 10928390
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
ID: 10934408
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
ID: 10936194
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
ID: 10936466
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
ID: 10936498
Try this ...

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

Author Comment

by:craman
ID: 10936702
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
 
LVL 6

Expert Comment

by:mmusante
ID: 10936838
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
ID: 10936849
Which version of ADO are you using?
0
 
LVL 6

Expert Comment

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

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

Author Comment

by:craman
ID: 10936938
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
ID: 11012794
I'm still unable to resolve this problem. any help is very much appreciated.
0
 
LVL 6

Expert Comment

by:mmusante
ID: 11012901
Please post your code with appendChunk ...
0
 

Author Comment

by:craman
ID: 11012974
Below is the actual code used.

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

Author Comment

by:craman
ID: 11013064
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
ID: 11013132
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
ID: 11013179
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
ID: 11013228
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
ID: 11013271
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
ID: 11013415
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
ID: 11028005
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
ID: 11350471
PAQed, with points refunded (350)

CetusMOD
Community Support Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

729 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