Re: Help on resolving record conflicts with ADO

Thanks for the input, Steve.

I'm no expert, but it seems like having to write my own update statements
(and also needing to know what fields the user has updated) seems a bit
extreme. Is there any way to be certain the upate statement ADO is
constructing is being put together with the proper key information, so you
don't have to go through all that extra work? I've been told BetterADO is
one way to go, but I'm running into similar problems.

"Steve Zimmelman" <skz@xxxxxxxxxxxxxxxxxx> wrote in message
news:43fde88a$1@xxxxxxxxxxxxxxxxxxxxxxxxx
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. ... that only use the PrimaryKey as in "Where" clause. ...
    (borland.public.delphi.database.ado)
  • Re: How can a recordset returned by a sproc be updated?
    ... > While I run the test on my box I haven't seen update statement as OP said. ... >> SQL Server MVP ... >>> ADO. ... >>> internal text of the stored procedure? ...
    (microsoft.public.sqlserver.programming)
  • Re: RollbackTrans methos still set Underlying Values
    ... I also double-checked my references and I am using ADO 2.8. ... >> recordset has the current values in the Underlying Value property, ... >> the recordset after the user fixes teh offending rows. ... >> that ADO is attempting to issue an Update statement, ...
    (microsoft.public.data.ado)
  • Re: replace Rs.AddNew with INSERT
    ... ADO doesn't require the Update statement. ... it will update for you if you call AddNew again or call Edit. ... If the Update statement is required, then I would expect an error along the ... If the second recordset is DAO (and by the syntax of the OpenRecordset ...
    (comp.databases.ms-access)