Hi All,

I am using the default Database instance, with AutoTransactions set to false.

Each client can belong to multiple documents, and each document can have multiple clients assigned to it. In the example below, an Add button has been clicked on the new document form, and the code below is on the client search form. Once the client has been found, the Ok button is clicked.

I have a couple of questions.

1 - The Database.Rollback in the OnCommitError event doesn't seem to be needed. It causes an error when it's there, and doesn't seem to make any difference when it's not. Should this be there or not. If not, should it be somewhere else? (The error is : "A transaction is not active").

2 - The OnCommitError is run correctly when a client that is already on the document is added (again). A key violation is raised. This is fine, but from now on, any database attempt (read, write, anything) causes an error, being the same system generated error message originally received for the key violation.
How do I clear the transaction and/or database stuff, so I can continue on fresh?

Thanks,
Paul

======================================================================

procedure TfSearchClient.bOKClick(Sender: TObject);
begin
 //save the chosen client with the current document id, into the "DocClients" dataset on the fDocNew form
                              
 if not Database.InTransaction then begin
   Database.AfterCommit := AddClientToDoc_AfterCommit;
   Database.OnCommitError := AddClientToDoc_OnCommitError;
   Database.StartTransaction;

   fDocNew.dsDClients.Open;
   fDocNew.dsDClients.Insert;
   fDocNew.dsDClients.Columns['DocId'].AsInteger := fDocNew.dsDocs.Columns['DocId'].AsInteger;
   fDocNew.dsDClients.Columns['ClientId'].AsInteger := qSearchCli.Columns['ClientId'].AsInteger;
   fDocNew.dsDClients.Save;
   Database.Commit;
 end else begin
   MessageDlg('A transaction is currently active. Cannot save at this time.', 'Information', mtInformation, [mbOk], mbOk);
 end;
end;

procedure TfSearchClient.AddClientToDoc_AfterCommit(Sender: TObject);
begin
 //open up the doc clients dataset
 fDocNew.dsDClients.Params.Clear;
 fDocNew.dsDClients.Params.Add('DocId=' + (IntToStr(fDocNew.dsDocs.Columns['DocId'].AsInteger)));
 Database.LoadRows(fDocNew.dsDClients);
 Database.AfterCommit := nil;
 Database.OnCommitError := nil;
 Close;
end;

procedure TfSearchClient.AddClientToDoc_OnCommitError(Sender: TObject; const ErrorMsg: String);
begin
 MessageDlg('There was an error adding the new client to the document.' + #13 + #10 + #13 + #10 + ErrorMsg, 'Document Error', mtError, [mbOk], mbOk);
 Database.Rollback;
 Database.AfterCommit := nil;
 Database.OnCommitError := nil;
 Close;
end;

Paul,

<< 1 - The Database.Rollback in the OnCommitError event doesn't seem to be needed. It causes an error when it's there, and doesn't seem to make any difference when it's not. Should this be there or not. If not, should it be somewhere else? (The error is : "A transaction is not active"). >>

No, you can't perform a rollback after the final commit has been executed because, as the error message indicates, there actually isn't any transaction active anymore.  So, remove that.

<< 2 - The OnCommitError is run correctly when a client that is already on the document is added (again). A key violation is raised. This is fine, but from now on, any database attempt (read, write, anything) causes an error, being the same system generated error message originally received for the key violation.
How do I clear the transaction and/or database stuff, so I can continue on fresh? >>

The method you want is this:

https://www.elevatesoft.com/manual?action=viewmethod&id=ewb2&comp=TDatabase&method=CancelPendingRequests

That will simply cancel the pending Commit.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Perfect. That did it. Thanks!

Cheers,
Paul

<<The method you want is this:

https://www.elevatesoft.com/manual?action=viewmethod&id=ewb2&comp=TDatabase&method=CancelPendingRequests

That will simply cancel the pending Commit.>>