Sceppa Core Reference errata on pp 469/473 and please help with concurrency error

[Timo]

I'm having trouble with concurrency errors (0 records affected)
but the Core Reference section relating to this subject seems to
have an error in the code sample. Maybe I'm wrong about this;
could someone please correct me if so!

On p. 473, the timestamp column is being assigned a value

e.Row("TSCol") = ....

and the penultimate paragraph on the page reads:

"After retrieving the new timestamp value and assigning it to the
appropriate column in the DataRow object..."

** But when I try to assign this column, I get an error that my
(SQLServer2000) timestamp column is READONLY. **

My update command uses the Primary Key and the timestamp columns
to check for concurrent changes to the row. My update command goes
like this:

Update T
Set ...
where PK = @PK and TS = @TS

And in the OnRowUpdated eventhandler, I assign the UpdateCommand
timestamp parameter as follows:

UpdateCommand.Parameters("@TS").Value = cmdGetTS.ExecuteScalar

However, when I make a second change to a record that I have just
updated (the scenario described at top of p.469 in Core
Reference), I get a concurrency error (0 records affected) even
though I am indeed using the most recent timestamp and have
correctly assigned the TS and PK parameters of my update query
prior to invoking the DA.Update() method.

Page 469 reads: "If you don't have the new value for the timestamp
column in your DataRow object, the subsequent update attempt will
fail."

I don't understand why the *row* must contain the new timestamp
value if one is using the primary key and the timestamp to test
for concurrency problems, and the update command takes the form
"Update Table ...where PK=@PK and TS=@TS". Wouldn't you want to
assign the UpdateCommand.Parameters("@TS") instead of the
e.row("TS")?

Clarification appreciated!
Timo

 

[David Sceppa]

Timo,

On p. 473, the timestamp column is being assigned a value
e.Row("TSCol") = ....
...

My update command uses the Primary Key and the timestamp
columns to check for concurrent changes to the row. My
update command goes like this:

Update T
Set ...
where PK = @PK and TS = @TS

If you're working with SQL Server 2000, you're best off
constructing your UpdateCommand to use a batch query to fetch the
new timestamp value automatically, as described in "Using Batch
Queries to Retrieve Data After You Submit an Update" earlier in
the chapter. Your UpdateCommand's CommandText would look like
this:

UPDATE MyTable
SET ...
WHERE PK = @PK AND TS = @TS;
SELECT TS FROM MyTable WHERE PK = @PK

This approach will provide better performance than relying on the
RowUpdated event as it involves a single round-trip to submit the
change and fetch the new timestamp.


The sample code you're describing was designed to show how
you can fetch server-generated data in the RowUpdated event when
working with a database such as Access that does not support
row-returning batch queries. To programmatically assign a value
to the timestamp's DataColumn, you would need to make sure the
DataColumn's ReadOnly property is False.

However, when I make a second change to a record that I
have just updated (the scenario described at top of p.469
in Core Reference), I get a concurrency error (0 records
affected) even though I am indeed using the most recent
timestamp and have correctly assigned the TS and PK
parameters of my update query prior to invoking the
DA.Update() method.

Page 469 reads: "If you don't have the new value for the
timestamp column in your DataRow object, the subsequent
update attempt will fail."

I don't understand why the *row* must contain the new
timestamp value if one is using the primary key and the
timestamp to test for concurrency problems, and the update
command takes the form "Update Table ...where PK=@PK and
TS=@TS". Wouldn't you want to assign the
UpdateCommand.Parameters("@TS") instead of the e.row("TS")?

Clarification appreciated!

When you submit pending changes using the DataAdapter, the
DataAdapter implicitly assigns values from columns to parameters
based on the settings in the Command's Parameters collection.
So, if you have code like...

With MyDataAdapter.UpdateCommand
.CommandText = "UPDATE MyTable SET ... " & _
"WHERE PK = @PK AND TS = @TS"
'Add parameters for SET clause
...
'Add parameters for WHERE clause
.Parameters.Add("@PK", SqlDbType.Int, 0, "PK")
.Parameters.Add("@TS", SqlDbType.Timestamp, 0, "TS")
End With

the values in the PK and TS columns are assigned to the
corresponding parameters when you submit the pending change by
calling DataAdapter.Update.

If you execute the Command directly through your own code
(Command.ExecuteNonQuery, etc.) then you would directly assign
values to the necessary parameters.

Running a SQL Profiler trace can be helpful if you're unsure
of what values are actually passed to the database.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Greg]

+1 on SQL Profiler trace ...this really helps us see what getting passed in
and out

 

[Timo]

First, thank you for the reply. I'm still having troubles with
this.

To programmatically assign a value
to the timestamp's DataColumn, you would need to make sure the
DataColumn's ReadOnly property is False.
[..]
Running a SQL Profiler trace can be helpful if you're unsure
of what values are actually passed to the database.

I have run the SQL trace. Correct PK and TS value are being
passed. However, I am still unable to assign e.Row("TSCol") per
the example in the Core Reference; error message is that the
column is ReadOnly, though I've set the column ReadOnly property =
False. Can you suggest something else I might be doing wrong, or
not doing, that would make the TS column readonly? The TS column
is explicitly included in the column-list in my Select command.

I see from Google searching that you asked other Micrsofters back
in April 2003 (in one of the MSFT chats) about the apparent lack
of "native support" for the Timestamp datatype in ADO.NET. When I
examine the e.Row("TSCol") in the debugger in the OnRowUpdated
event following an insert, the TS column always contains DBNULL.
I assume that's what you had in mind when you asked about lack of
native support? The person who responded to you said he didn't
know what you meant, and then the chat ended.

The responder advised to requery after an insert to avoid
concurrency errors. But I'm using StoredProcedure rather than
batch queries to update; the SP returns the new TS value in an
OUTPUT parameter. Do I understand correctly from your advice, and
from that chat, that the SP output parameter approach simply isn't
a viable alternative at present? SP works okay when updating a
record twice in succession; but insert-then-update fails with a
"zero records affected" error.

I have retrieved the new timestamp via a command in the
onRowUpdate event after the insert, per your example, and have
assigned the TS value at that point to the
UpdateCommand.Parameters("@TS").Value so it will be available for
the next invocation of the update command. **But the value of the
timestamp column itself is DBNull at that point.**

We don't want to use batch queries for the very reasons you bring
up in your book, not to mention the rewriting involved, but will
have to do so if that's the only way to get this to work.
Timo

 

[Greg Robinson]

We use timestamps on updates like David describes and all works well.

We include the original pk and the original timestamp datacolumn values
in our where clause (using sprocs). We have an OutPut param in the
sproc on the timestamp column's new value (after the update). We create
an output parm for our UpdateCommand and set's its SourceColumn to the
timestamp DataColumn in our DataTable being updated, using the Original
version.

All works perfect. Update goes through, timestamp DataColumn's Value
gets updated.

 

[David Sceppa]

Timo,

We don't want to use batch queries for the very reasons
you bring up in your book, not to mention the rewriting
involved, but will have to do so if that's the only way
to get this to work.

I'm a little confused. Using batch queries would be simpler
and faster. I'm not sure what you mean about not using batch
queries "for the very reasons bring up in [the] book".

When performing updates via stored procedures, you can pass
back server-generated values through output (or input/output)
parameters. Set the Direction property on your ADO.NET
parameter(s) correctly and set Command.UpdatedRowSource to Both
or OutputParameters, and the new values should be applied to the
DataRow.

If you could isolate the behavior and post simple repro
code, that would be the easiest way to get to the root of the
problem.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.

 

[Timo]

Following up on my earlier posting...

I had been using CommandType.Text for the Insert and
CommandType.StoredProcedure for the Update. ADO.NET doesn't appear
to like that mix. After replacing the sql text command with a
stored procedure for the insert, things are working as expected. I
can update the row immediately after inserting it without getting
a concurrency error.
Timo