The example below is a minimal application using live binding between a TAdoQuery and a TStringGrid. I've done it for convenience as a VCL application rather than an FMX one, but that doesn't make any difference to how to do filtering on a
live-bound AdoQuery.
It uses 2 TEdits to specify the filter value to match and the name of the field
to filter on (in practice it would be better to do something like populate a listbox with the available field names).
The main "work" is done in the UpdateFilter procedure.
The fact that it uses live binding doesn't make any difference to how to apply the filter to the dataset. However, live binding to a StringGrid is significantly slower than a traditional (VCL) TDBGrid. An important thing to avoid is the situation where the dataset has a large number of fields and there is one stringgrid column for each field because it can make the app very slow to respond to changes in the filter criteria. A way to mitigate the effect of this is simply to restrict the number of stringgrid columns to a much lower number, by setting the ColCount of the stringgrid to a suitably low value. An alternative is to define persistent fields for the dataset, but only create a few of them.
In the code below, I've used the OnChange events of the TEdits to update the FilterFieldName and FilterValue fields, but obviously you could have a separate button to click to call the UpdateFilter procedure.
Code:
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
StringGrid1: TStringGrid;
BindingsList1: TBindingsList;
DataSource1: TDataSource;
LinkGridToDataSource1: TLinkGridToDataSource;
BindSourceDB1: TBindSourceDB;
edFilterFieldName: TEdit;
edFilterValue: TEdit;
procedure FormCreate(Sender: TObject);
procedure edFilterFieldNameChange(Sender: TObject);
procedure edFilterValueChange(Sender: TObject);
private
FFilterFieldName : String;
FFilterValue : String;
procedure SetFilterFieldName(const Value: String);
procedure SetFilterValue(const Value: String);
procedure UpdateFilter;
public
property FilterFieldName : String read FFilterFieldName write SetFilterFieldName;
property FilterValue : String read FFilterValue write SetFilterValue;
end;
[...]
procedure TForm1.FormCreate(Sender: TObject);
begin
FilterFieldName := edFilterFieldName.Text;
FilterValue := edFilterValue.Text;
end;
procedure TForm1.edFilterFieldNameChange(Sender: TObject);
begin
FilterFieldName := edFilterFieldName.Text;
end;
procedure TForm1.edFilterValueChange(Sender: TObject);
begin
FilterValue := edFilterValue.Text;
end;
procedure TForm1.SetFilterFieldName(const Value: String);
begin
if FilterFieldName <> Value then begin
FFilterFieldName := Value;
UpdateFilter;
end;
end;
procedure TForm1.SetFilterValue(const Value: String);
begin
if FilterValue <> Value then begin
FFilterValue := Value;
UpdateFilter;
end;
end;
procedure TForm1.UpdateFilter;
var
Expr : String;
begin
AdoQuery1.Filtered := False;
// The next statement checks whether the FilterFieldName
// matches a field in the dataset and exits if not. Since the
// FilterFieldName value comes from an edit box, it will be incomplete while the user is typing it in
if AdoQuery1.FieldByName(FilterFieldName) = Nil then
exit;
if FilterValue <> '' then begin
Expr := FilterFieldName + ' like ' + QuotedStr('%' + FilterValue + '%');
AdoQuery1.Filter := Expr;
AdoQuery1.Filtered := True;
end;
end;
