Delphi TEdit to filter Tstringgrid with Access

Question

I'm using Delphi 10 with Firemonkey, i'm a bit new to it. I have a TStringGrid that i bound with LiveBindings to a access database. What I need is to filter this table or the TStringGrid with the text of a TEdit when I press a button or when any key is enter, and the show it in the results in the same TstringGrid. Just like a custom search/filter box.

I don't have any code of this yet. But i think it would be like a query.
procedure TForm3.Edit2Typing(Sender: TObject); begin adoquery1.Close; adoquery1.SQL.Text:='select * from instrutor where nome like " %' + edit2.text + '%"'; adoquery1.Open; end;
I've tried this but since I already have a livebinding with the adotable and the stringgrid, i don't the way i should link this Tedit

Please add the code you're tried to the q. Or are you saying you do not know where to begin? — MartynA
Like q query, sure. But the expression "filter", in a Delphi dataset context, usually means operating on (e.g. displaying) only a subset of records which have already been retrieved from the database into the Delphi app. So you need to decide whether you want a query or a filter and say. — MartynA
Ok, I should use the AdoTable.Filter propertie. Now I just have to bind it with the stringgrid? — Guilherme Raguzzoni

MartynA MartynA · Accepted Answer · 2015-11-14T14:35:55

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;

Delphi TEdit to filter Tstringgrid with Access

1 Answers