ADOQuery AfterScroll not Triggered with One Record / No Record

Question

I have a MasterQry and a SlaveQry on the form . The MasterQry is something like this :

select * from Header where Active = 1 .

On it's AfterScroll event I have following :

select * from Slave where HeadID=:Header.ID

Now if I do :

if MasterQry.Active then MasterQry.Close;
MasterQry.Open;

this works flawlessly if I have more then one record , but it does not work if I have only one .

Even if I do MasterQry.First; nothing happens.

If I try MasterQry.AfterScroll(MasterQry) I get an access violation .

I was refactoring my code and was trying to make it more compact , because I did a lot of Open Close Locate ID ( needed to refresh data to get actual status, whether it is locked etc. ) and I did this :

function RefreshQuery(AQuery : TADOQuery; ID : integer) : boolean ; overload;
var AfterOpen,AfterScroll : TDataSetNotifyEvent;
begin
  result:=false;

  AfterOpen := AQuery.AfterOpen;
  AfterScroll := AQuery.AfterScroll;

  AQuery.AfterOpen:=nil;
  AQuery.AfterScroll:=nil;

  if AQuery.Active then AQuery.Close;
  AQuery.Open;

  if not AQuery.Locate('id', ID, []) then
    result:=false
  else
    result:=true;

  AQuery.AfterOpen:=AfterOpen;
  AQuery.AfterScroll:=AfterScroll;
  if Assigned(AQuery.AfterScroll) then
    AQuery.AfterScroll(AQuery);
end;

Please NOTE this code is not universal but suits my needs perfectly . What I noticed is that the AfterScroll event here is being triggered even if I have only One Record in the MasterQry , or even if I have non at all . I was really happy , I tested it multiple times and it delivered correct results.

I checked the TADOQuery.First and TADOQuery.Locate procedures and both had DoAfterScroll but it was not triggered with One Record or with No Record . ( the SlaveQry was left open in a undesired state )

I've googled a lot for this but was unable to find the reason .

My Question is : why does this work ? why does AfterScroll fires with One or No Record .

Thank you.

UPDATE

I can only reproduce this with Microsoft SQL . So in order to test this you need . Two Tables .

In MasterTable add two records ( ID , Text, Active )

1 First 1

2 Second 1

in the SlaveTable add two or more records ( ID,HeadID,Text )

1,1,First-1

2,1,First-2

3,2,Second-1

4,2,Second-2

Now drop on the Form one ADOConnection two ADOQueries .

in the MainQuery you have following text

Select * from MasterTable where Active=1

in the SlaveQuery you have following text

select * from SlaveTable where HeadID=:HeadID

on the MainQuery.BeforeOpen you have this :

MainQuery.AfterScroll:=nil;

on the MainQuery.AfterScroll you have this :

if SlaveQuery.Active then SlaveQuery.Close;
SlaveQuery.Parameters.ParamByName('HeadID').Value:=MainQueryID.Value;
SlaveQuery.Open;

on the MainQuery.AfterOpen you have this :

MainQuery.AfterScroll:=MainQueryAfterScroll;

Add a Button to this form :

Button1Click Event Contains following :

if MasterQuery.Active then MasterQuery.Close;
MasterQuery.Open;

So if you now attach a Grid to both Queries , you can see it is following perfectly .

Without closing the Program , go into the SQL Server Manager and run the following update statement :

update MasterTable set Active=0

Press the Button1 on the Form again :

The MasterQuery is Emtpy , the SlaveQuery was left in the Last Open State .

In order to fix this you need to alter the Button1Click as follows :

var AfterOpen,AfterScroll : TDataSetNotifyEvent;
begin
  AfterOpen := AQuery.AfterOpen;
  AfterScroll := AQuery.AfterScroll;

  AQuery.AfterOpen:=nil;
  AQuery.AfterScroll:=nil;

  if AQuery.Active then AQuery.Close;
  AQuery.Open;

  AQuery.AfterOpen:=AfterOpen;
  AQuery.AfterScroll:=AfterScroll;
  if Assigned(AQuery.AfterScroll) then
    AQuery.AfterScroll(AQuery);
end;

And now it works . I don't know why because MasterQuery.First should trigger DoAfterScroll but nothing Happens . It seems like Setting AfterScroll to nil and then back again somehow triggers AfterScroll even when it has 1 Record or is empty .

Answer 1

As I said in a comment, most of the code in your RefreshQuery shouldn't be necessary, as linking Master->Detail datasets should "just work". In fact, your RefreshQueryshouldn't be necessary at all.

I created a minimal project based on your master and slave tables just by dropping components from the pallete, wiring them up and adding ONLY the code in Form1.FormCreate below. The contents of the Slave grid correctly track the Master grid, including the case where there are no matching Slave records, i.e. the Slave grid displays empty. Notice there are no data events whatever needed, i.e no AfterScroll and no Locate, etc, calls.

  type
    TForm1 = class(TForm)
      dsMaster: TDataSource;
      DBGrid1: TDBGrid;
      DBNavigator1: TDBNavigator;
      DBGrid2: TDBGrid;
      DataSource2: TDataSource;
      DBNavigator2: TDBNavigator;
      ADOConnection1: TADOConnection;
      qMaster: TADOQuery;
      qSlave: TADOQuery;
      qSlaveID: TIntegerField;
      qSlaveHeaderID: TIntegerField;
      qSlaveAText: TWideStringField;
      procedure FormCreate(Sender: TObject);
    public
    end;

  [...]

  procedure TForm1.FormCreate(Sender: TObject);
  begin
    qMaster.SQL.Text := 'select * from mastertable';

    qSlave.DataSource := dsMaster;
    qSlave.SQL.Text := 'select * from slavetable where headerid = :id';
    //  NOTE: because the DataSource property of qSlave is set to dsMaster,
    //  the ` = :id` tells the Ado run-time code to get the value of the
    //  ID field in the qMaster table.

    qMaster.Open;
    qSlave.Open;
  end;

If you want to refresh the Master or Slave table in case another user has changed records in it, you can do that like this:

procedure TForm1.Button1Click(Sender: TObject);
begin
  qMaster.Refresh;
end;

but be aware that the table needs to be correctly set up on the Sql Server. As long as the ID field is set up as a primary key, and/or there is a unique index set up on it on the server, the call to Refresh should work fine, but if not you will get an error with a message to the effect of "Insufficient key information for updating or refreshing." You could of course do the refresh on a timer (but don't call it too frequently, i.e. more than once every few seconds).