Refreshing a ClientDataSet nested in a DataSetFiel

Question

I've nearly finished some code to solve the problem in this unanswered q from last year:

Refresh Nested DataSet with poFetchDetailsOnDemand

Received wisdom is that it's not possible to refresh the nested detail CDS from the server without closing and re-opening the master CDS, but obviously that can generate a disproportionate amount of network traffic, just to refresh a single master row and its detail rows.

I thought of a straightforward way to refresh the detail CDS from the server and it nearly works. My code below basically does a detail refresh by temporarily applying a filter to the master ADO query to filter it down to the current master row and, while that filter is in effect, refreshing the master CDS by applying a similar filter to it and then calling its Refresh method. This is triggered by the master CDS AfterScroll event.

There is just one teeny problem: On my form,as well as the 4 datasets and accompanying grids, I have a Refresh button on the form that calls my RefreshcdsMasterAndDetails that's also called in cdsMasterAfterScroll. If I move around the master CDS using its grid, my code all works fine, and the detail CDS rows correctly update immediately, along with those in the AdoQuery detail <>but if I trigger it by clicking the Refresh button, the CDS detail rows only update every second time I click the Refresh button.

My question is: Why should there be any difference in the effect of my code when it's triggered from a button click rather than from the AfterScroll event, in as much as it reliably does what it's supposed to do called from the AfterScroll event but only every other time when triggered by a button click?

//Obviously MasterPKName below is a const and DoingRefresh is a boolean
// flag on the form

procedure TForm1.cdsMasterRowRefresh(MasterPK : Integer);
begin
  if DoingRefresh then Exit;

  DoingRefresh := True;

  try
    cdsMaster.Prior;
    cdsMaster.Next;
    cdsMaster.Filter := MasterPKName + ' = ' + IntToStr(MasterPK);
    cdsMaster.Filtered := True;
    cdsMaster.Refresh;
    cdsMaster.Filtered := False;

    cdsMaster.Locate(MasterPKName, MasterPK, []);

  finally
    DoingRefresh := False;
  end;
end;

procedure TForm1.qMasterRowRefresh(MasterPK : Integer);
begin
  qMaster.Filter := MasterPKName + ' = ' + IntToStr(MasterPK);
  qMaster.Filtered := True;
  qMaster.Refresh;

  cdsMasterRowRefresh(MasterPK);

  qMaster.Filtered := False;
  qMaster.Locate(MasterPKName, MasterPK, []);
end;

procedure TForm1.RefreshcdsMasterAndDetails;
var
  MasterPK : Integer;
begin
  MasterPK := cdsMaster.FieldByName(MasterPKName).AsInteger;

  cdsDetail.DisableControls;
  cdsMaster.DisableControls;
  qDetail.DisableControls;
  qMaster.DisableControls;

  try
    qMasterRowRefresh(MasterPK);
  finally
    qMaster.EnableControls;
    qDetail.EnableControls;
    cdsMaster.EnableControls;
    cdsDetail.EnableControls;
  end;
end;

procedure TForm1.cdsMasterAfterScroll(DataSet: TDataSet);
begin
  RefreshcdsMasterAndDetails;
end;

Answer 1

Despite a lot of careful observation and debugging, I still don't have a satisfying explanation of exactly why my CDS refreshing code behaves differently if called in the master CDS's AfterScroll event, where the detail CDS always gets updated correctly, and in a ButtonClick handler where the detail CDS only gets updated every second click. I imagine that it's something to do with the fact that the master CDS's cursor has already moved by the time the AfterScroll handler is called, unlike the situation where I click the button.

However, I have found a simple work-around and a fix.

The work-around is simply not to call DisableControls on the 4 datasets before doing the refresh. Then the detail CDS always gets refreshed correctly. Any other permutation of disabling some or all the datasets results in the difference my q is about. I don't like this work-around though, because the cdsMaster DBGrid has to scroll all the way through the data, just ti refresh one master row + its details.

The fix is to do something that on reflection I should have done in the first place, namely to force a refresh of the detail ADO query (with my data, simply calling its Refresh, which was my first attempt at a fix, provokes the familiar Ado error "Insuffient key column information for updating ..." despite the detail table having a PK on the server).

So, here's the fix:

procedure TForm1.qMasterRowRefresh(MasterPK : Integer);
begin
  try
    qMaster.Filter := MasterPKName + ' = ' + IntToStr(MasterPK);
    qMaster.Filtered := True;

    qMaster.Refresh;

    //  Do NOT omit the next 3 lines, needed to ensure that the detail query
    //  and hence the detail CDS, is refreshed

    qDetail.Parameters.ParamByName(MasterPKName).Value := MasterPK;
    qDetail.Close;
    qDetail.Open;

    cdsMasterRowRefresh(MasterPK);

  finally
    qMaster.Filtered := False;
    qMaster.Locate(MasterPKName, MasterPK, []);
  end;
end;

As I got into this by looking into an earlier unanswered SO question, I'll be transplanting an updated version of the code to an answer to that one.