Refreshing a ClientDataSet nested in a DataSetField

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;

Onetoone

Good point. I've been so wrapped up in this for over a day now that I managed to leave the question out! I will edit it in a mo. – Onetoone

Well, I believe that in a CDS you can have more than a single field for keys, your solution for my question would be incomplete though. But, hey, +1 that's an tricky idea to solve the problem... – Odette

Thanks. Incomplete because of multi-field keys you mean or something else? My code has moved on a bit from what I posted because of a similar problem I've run into with the server detail table. – Onetoone

Because of multi-field, but nevermind because this can be easily changed. Anyway, why have you choose OnAfterScroll? – Odette

Basically because IME it's the most reliable event to pick up on the fact that the current master row has changed and so the cdsDetail needs to fetch a different set of rows. I've used it in a number of situations where relying on the VCL to get the details for the right master record is impractical for a variety of reasons. – Onetoone

Ok. Can the above code reproduce the problem you described? I'll try it here. Have you tried debugging with DCU's? – Odette

Yes, it can, with my data anyway (150k master rows, 0.5m details, fwiw). Btw, I think I know a fix for the problem even though I still haven't figured out what exactly causes the difference my q is asking about. If it works, I'll post it after a bit of testing ... – Onetoone

What method are you calling when you click the button? RefreshcdsMasterAndDetails? – Odette

RefreshcdsMasterAndDetails, i.e. same as within the cdsMaster's AfterScroll – Onetoone

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.

Onetoone