ExecuteCompleted of AdoConnection is not fired if user changes data via dbgrid

if i execute a query like

Update Table Set aField = 1 Where tablePk = 1

i can get the affected record count (RecordsAffected parameter) from ExecuteComplete event of AdoConneciton. But if i change data via dbgrid, the ExecuteComplete event is not fired.

How can i get affected record count after insert/update/delete command that was executed by dbgrid?

Solution

I don't think there's a way to get the count of Rows Affected when you do updates, inserts and deletes via a TDBgrid or other DB-aware components like TDBNavigator. The reason is that DB-aware controls call the Post and Delete methods of TDataSet, and those calls overrides of InternalPost and InternalDelete in TAdoCustomDataSet. They work in a fundamentally different way than executing a SQL statement via the ExecSql method of, say, TAdoQuery.

By design, TDataSet.Post and TDataSet.Delete should only affect a single row, so if the operation succeeds, you know that exactly one row was affected.

It's worth noting, though not much help for what you want to do, that there is a way to attach the same event handler to a number of TAdoCustomDataSet descendants that share a TAdoConnection, as shown in this code:

procedure TForm1.FormCreate(Sender: TObject);
var
  i : Integer;
begin
  for i := 0 to AdoConnection1.DataSetCount - 1 do
    AdoConnection1.DataSets[i].AfterPost := AfterPost;
  AdoQuery1.Open;
  AdoQuery2.Open;
end;

procedure TForm1.AfterPost(DataSet: TDataSet);
var
  Q : TAdoQuery;
begin
  if DataSet is TAdoQuery then begin
    Q := TAdoQuery(DataSet);
    Caption := IntToStr(Q.RowsAffected);
  end
  else
    Caption := 'Post';
end;

Of course, if the datasets involved already have their own event handlers, you would need some structure to store the existing handlers and chain to the right one of them in the shared handler (f.i. TForm1.AfterPost above).

If you try the above code and observe what happens when you post an edit from the DBGrid which is getting its data from a TAdoQuery, you'll see that unfortunately, the RowsAffected is zero. That's because the TAdoQuery's FRowsAffected is only updated when its ExecSql method is called, and it doesn't get called for dataset operations invoked via the DBGrid. The difference is because the AdoConnection's OnExecuteComplete is called from the Command object used to carry out TAdoQuery's ExecSql. Operations initiated from the DBGrid, otoh, call methods of the RecordSet object associated with the TAdoCustomDataSet in its InternalPost and InternalDelete, and that does not invoke the AdoConnection's OnExecuteComplete.

The RecordSet objects have their own sets of events, see f.i. RecordSetEvents in ADOInt.Pas, and conceivably you could set up shared event handlers for those in a similar way to the shared AfterPost event example above. However, I don't think that that would do anything useful for you if you want to get the RowsAffected value for a TDataset Insert/Update/Delete invoked from the DBGrid (or, say a TDBNavigator connected to its TDataSource).

The reason I say that is that if you take a look at the source code for the InternalPost method of the TAdoCustomDataSet you'll see that it includes

  if State = dsEdit then
    UpdateData
  else
  begin
    Recordset.AddNew(EmptyParam, EmptyParam);
    try
      UpdateData;
    except

and the nested UpdateData does its stuff by calling

    Recordset.Update(EmptyParam, EmptyParam);

Now, if you look at the MS Documentation for RecordSet.Update, you'll see e.g.

https://msdn.microsoft.com/en-us/library/ecc2bf09.aspx?f=255&MSPPError=-2147217396

which clearly states that if Update does not affect exactly one record, an exception is raised. I imagine that's what @KenWhite had in mind when he said "There will only be a single record updated". So if RecordSet.Update succeeds, you know that only one row was affected.

I haven't checked, but since TAdoCustomDataSet.InternalDelete uses its Recordset object to do the deletion, similar would likely be true of that.