Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 832
  • Last Modified:

Save record after EDatabaseError (already changed by another user)

Hi,

Is there a way to post a record even when it was just updated by another user. Normally one would get an EDatabaseError (Can't find row for update).

I found some code to handle the synchronisation with the dataset, but that doesn't save the new updated values.

// Added to OnPostError-event
with ADODataset1 do 
begin
  UpDateCursorPos; {MUST ALWAYS DO!}
  RecordSet.Resync(adAffectCurrent, adResyncAllValues);
  ReSync([]); {May not be needed}
end;

Open in new window


Is there a way to save the last update from this point on anyway?
I've tried Action := daRetry; but then the error keeps popping up.
0
Stef Merlijn
Asked:
Stef Merlijn
3 Solutions
 
soscpdCommented:
Hi

Use Try / Except. If Try fails, Save the record on Except, like:

Try
  SaveRecord(record);
Except
  DoSaveRecordAnyWay(record);
End;

On debugger, you should see the error message. Alone, the application should not complain about anything.

If you like to try while error, you can even loop like

Var
 PostEd : Boolean;
Begin

PostEd := False;

While Not PostEd do
Try
  if SaveRecord(record) = 1 then
    PostEd := True
  else
    PostEd := False;
Except
  DoSaveRecordAnyWay(record);
  PostEd := False;// For sure
End;

Of course, that's insane. A single broken database connection can gift you a endless loop.

I suspect you have a design issue here. What database are you using? Why both users can't work at the same time? Do they need to update the record? Can't they insert and you pick the last (updated) from a select max on a timestamp field?

If you like to elaborate a little bit, maybe we can find another way to handle that then shut the EDatabaseError up and post anyway.

Regards
Rafael
0
 
markusventerCommented:
set Cursorlocation to clUseServer on your dataset
0
 
Stef MerlijnDeveloperAuthor Commented:
soscpd:
I discovered that trying to save the record at all costs might not be a good idea. The record might be deleted by the other user, and therefore never accessible for any update. Probably a better way would be to abort the post and refresh the dataset. Any changes will be lost then. Maybe CursorLocation = clUseServer might solve the inaccessable row.
markusventer:
I'll do some testing with CursorLocation = clUseServer
Do you also have any experience with CursorType? I'm thinking of using ctDynamic instead of ctKeySet. According to the help, this would make added and deleted records know to other users. Any suggestions for usage of one or the other?
 
0
Independent Software Vendors: 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!

 
Geert GruwezOracle dbaCommented:
there will allways be a problem with duplicate updates >> who wins ?

if you ever solve this problem, let me know, i'm very interested
only if it's a works in all cases solution :)

which i'm about 100000000000000000% sure doesn't exist.

a software lock (notifying the user someone else is going to or will change the record) is the only solution i know of
but it doesn't allways work for 100%
people allways ask for a overrule, or i'm the boss, so i should have priority ...
0
 
Stef MerlijnDeveloperAuthor Commented:
One solution I'm  now building is to check the actual database (by additional query) to see if the selected record has changed in comparison to the details on screen.
This check can be done just before an update is posted. As I have only one table that is troubling me with regard to this issue, I can be pretty sure I got the 99,9%. Basically it would be very uncommon that two users would change the same record (in this case events in a scheduler). But even than I can fallback on this check. Also I build a mechanism with timer to regulary update the scheduler every xx seconds.
Deleted records that were still on screen by an other user, can be handled the same way. Just check if it is actually still in the database, if not, abort transaction and refresh the table/scheduler. There are a lot of checks to perform to cover all situations, but I believe I got it covered now.
0
 
Stef MerlijnDeveloperAuthor Commented:
Setting cursorLocation on the ADOQuery to clUseServer didn't work for me. I got all kinds of errors (like: Database doesn't support bookmarks, which is strange as I'm using SQL Server 2008, I would imaging that it will have support for that??). Anyway being as far as I am with programing it would mess up the due-date of my project to figure that part out (and everything would need re-testing also).
0
 
Stef MerlijnDeveloperAuthor Commented:
All your suggestions put me in the right direction, thank you all.
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now