Delphi - Multiple ADOQuery

Question

I would like to ask you for help with my Delphi Project (RAD Studio), Im new in Delphi.

Im using: TADOConnection (with connection string) 2x ADOQUERY (set "Connection = TADOConnection)

I need 3 Separate DBGrids with DataSource from first ADOQuery and I cannot find a way to filter entry data to grids (I dont want to use 3 "WHERE" different queries) is there any way to do it ? (I was trying to find out something in DataSource property but it does not have "Filter" property, also its not possible to filter "LiveBinding" in RAD Studio).

Thanks for any help, im looking forward how to do it

Have a perfect day JP

Answer 1

What Ken White says is quite correct. However, the code needed with ADO components to feed several grids with differently-filtered versions of the AdoQuery's data is actually very simple: you just add as many TAdoDataSets as there are filtered grids and this code

procedure TForm2.Button1Click(Sender: TObject);
begin
  AdoDataSet1.Clone(AdoQuery1);
  AdoDataSet1.Filter := 'Name like ''B%''';
  AdoDataSet1.Filtered := True;

  AdoDataSet2.Clone(AdoQuery1);
  AdoDataSet2.Filter := 'Name like ''C%''';
  AdoDataSet2.Filtered := True;

  //    Etc
end;

With the default settings of TAdoConnection and TAdoQuery, changes made to records in the filtered grids are automatically propagated back to the AdoQuery and then back to the server table.

In Delphi Seattle or later, you could use FireDAC instead of ADO and use code like this, which is a bit more complicated but not by much:

procedure TForm2.FormCreate(Sender: TObject);
begin
  FDConnection1.UpdateOptions.AutoCommitUpdates := True;
  FDQuery1.CachedUpdates := True;
  FDQuery1.Open;
end;

procedure TForm2.FormClose(Sender: TObject; var Action: TCloseAction);
begin
  FDQuery1.ApplyUpdates(-1);
end;

procedure TForm2.Button1Click(Sender: TObject);
begin
  FDMemTable1.CloneCursor(FDQuery1);
  FDMemTable1.Filter := 'Name like ''B%''';
  FDMemTable1.Filtered := True;

  FDMemTable2.CloneCursor(FDQuery1);
  FDMemTable2.Filter := 'Name like ''C%''';
  FDMemTable2.Filtered := True;
  //    Etc
end;

Note that this should work fine provided the FDQuery's CachedUpdates is set to True, as well as the FDConnection's UpdateOptions.AutoCommitUpdates, so these settings are done in the FormCreate above. The ApplyUpdates is necessary to avoid any chamges being discarded when the program ends.

Btw, you said

but it does not have "Filter" property, also its not possible to filter "LiveBinding" in RAD Studio).

I'm not sure why you say that. Because the Filter is a property of the dataset ,whether it's an FDQuery or an FDMemTable, you should be able to use filtering in principle with no problem. However, given that Live Bindings is a bit temperamental, I would close and re-open the datasets after changing any filtering.