Re: Help on resolving record conflicts with ADO

Assuming you are using SQL Server...

When ADO Posts, it constructs an SQL update statement. It uses the primary and
the fields that have changed in the Where clause. Something like:

Update MyTable
Set SomeField = NewValue
Where
PrimaryKey = SomeValue
And SomeField = OldValue

If Another user has changed the value of SomeField, then the Update statement
fails because it can no longer find that value.

I stopped using the Post method in ADO and construct my own update statements
that only use the PrimaryKey as in "Where" clause. The trick is knowing what
fields the user changed so you can dynamically create an Update statement
without using every field in the table.

Take a look at this, it might give you some ideas.
http://www.skzimmelman.com/Articles/TFields_WhatChanged.htm


-Steve-

"Bill Hicks" <waylien_no_spam@xxxxxxxxx> wrote in message
news:43fd6f59$1@xxxxxxxxxxxxxxxxxxxxxxxxx
I am using Delphi 7, SQL2000, ADO.

I am trying to find a clean way to resolve update conflicts between users and
am stuck. I have a SQL table that has a primary key. If a user attempts to
update a record that's already been updated by another user, the OnPostError
event fires and allows me to handle the conflict. So far so good. Now, if I
try to run the following code because the user has choosen to overwrite the
other users changes, I run into the same OnPostError as soon as the retry
occurs. It seems as though there is not enough information to complete the
update.

TAdoDataset(DataSet).UpdateCursorPos;
TAdoDataset(DataSet).Properties['Update Criteria'].Value :=
adCriteriaKey;
TAdoDataset(DataSet).RecordSet.Resync(adAffectCurrent,
adResyncUnderlyingValues);
Action := daRetry;

I thought that setting ['Update Criteria'].Value := adCriteriaKey was going to
allow me to use just the key field to make the update, but it seems like ADO
still cannot locate the record in question.

Thanks in advance.

Bill Hicks



.

Relevant Pages

  • Re: Help on resolving record conflicts with ADO
    ... When ADO Posts, it constructs an SQL update statement. ... statements that only use the PrimaryKey as in "Where" clause. ... Update statement without using every field in the table. ... choosen to overwrite the other users changes, ...
    (borland.public.delphi.database.ado)
  • Re: Text replacing from another table
    ... your new solution doesn't seem to work when I run it in SQL 2005 SP3 ... I think it's because no update statement can update the ... From ReplacementCriteria ... want to consider just doing this with a cursor. ...
    (microsoft.public.sqlserver.programming)
  • Re: New concurrency error
    ... exception in the condition you specify. ... Person A reads record 1 with timestamp 333 ... Person B issues the update statement. ... when I used to use timestamp for concurrency (SQL 6.5), ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: SqlCeCommand.ExecuteNonquery() for update hangs application
    ... If appointmentReason is a bit field, ... SQL CE/SQL Mobile is definitely related to the ... > connection and the update statement and just call ...
    (microsoft.public.sqlserver.ce)
  • Re: Weird problem: Update statement updating records it should not
    ... from table2, table3 ... Field3 won't accept nulls and from the above SQL should never be set ... But the error is that field3 can't be set to null. ... Post the EXACT error the ACTUAL update statement is producing, ...
    (comp.databases.oracle.misc)