Filter database table/query whilst typing in TEdit box

I have a form which has a cxGrid on it and connects to a database table via TADQuery (FireDAC).

I've also added a TEdit box to the same form. When a user starts typing into the TEdit box, can I make the grid start to filter the results based on what the user is typing (preferably for more than one field in the table)?

If so, your thoughts/examples would be appreciated. Thanks.

Solution

In your FDQuery, you might have a sort of query like this :

SELECT * FROM MyTable
WHERE ColumnA LIKE :paramA

This would apply to a string column, but any other logical comparison would work. I presume you know how to write your query.

You would of course need to add the parameter, either programmatically or via the Parameters property in the design-time Object Inspector, being sure to match the parameter's name with that used in the query. I presume also that you know how to do this.

In the OnChange handler of your TEdit, then, you could do something like:

procedure TForm1.Edit1Change(Sender: TObject);
begin
  FDQuery1.Params.ParamByName('paramA').Value := Format('%%%s%%',[Edit1.Text]);
  FDQuery1.Refresh;
end;

This, naturally, braces the search term with % SQL wildcards, narrowing the returned records to those which contain the substring typed by the user into the edit box.

If you are querying a large dataset where the .Refresh operation is long-running you can instead use a timer to delay refreshing the dataset until the user has stopped typing for a given interval of time.