CODEWITHSUNDEEP
delphidelphi-xe2
Sharpie
Sharpie

Reputation: 383

Filter Query/DataSource records onto cxgrid

Using Delphi XE2.

Building a software package which connects to a database via query/datasource.

I would like to implement a filter option for records in a table, so at a click of a button a cxgrid will then display the records which match the filter selections.

I cant quite work out how to do this. Any help will be appreciated.

Have this so far but I honestly don't if this is close to what im trying to achieve.

procedure TFilter.btnClick(Sender: TObject);
begin
with aQry do
begin
  SQL.Clear;
  Close;
  SQL.Text := 'select * from TABLE where record_name like'+QuotedStr(name.Text+'%');
  SQL.Text := 'and record_type like '+QuotedStr(type.Text+'%');
  SQL.Text := 'and record_type2 like '+QuotedStr(type2.Text+'%');
  SQL.Text := 'and record_type3 like '+QuotedStr(type3.Text+'%');
  SQL.Text := 'and record_type4 like '+QuotedStr(type4.Text+'%');
  Open;
end;
end;

Upvotes: 0

Views: 1564

Answers (2)

crefird
crefird

Reputation: 1610

You can do what you want using the Query's Filter property. First, execute the query to get all records that could be displayed.

aQry.SQL.Text := 'select * from TABLE'; 
aQry.Open;

Then when the values to filter on are defined, define and activate the Filter

aQry.Filtered := false;
aQry.Filter := 'record_name like'+QuotedStr(name.Text+'%') '+
  'and record_type like '+QuotedStr(type.Text+'%') '+
  'and record_type2 like '+QuotedStr(type2.Text+'%') '+
  'and record_type3 like '+QuotedStr(type3.Text+'%') '+
  'and record_type4 like '+QuotedStr(type4.Text+'%');
aQry.Filtered := true;

If any of the Text values are changed, you will have to execute the above again.

This approach does the filtering in your program's memory, and does not refresh the data from the database. If you expect the data in the database to change between filter changes, RRUZ's approach may be better for you.

An alternative to Query's Filter, is the cxGrid.TableView.DataSource.Filter. Defining it in code is a bit more complicated than the Query's Filter. But it can be easily be defined by the user without any code if you make TableView's Navigator and Navigator.Filter visible and enabled.

Upvotes: 0

RRUZ
RRUZ

Reputation: 136391

In your code you are building a invalid SQL sentence, because you are overwritten the content of the SQL each time which the Text property is set, So you must use the Add method to build the SQL sentence. Also you must consider use parameters.

Try the next sample which build and run a parameterized SQL sentence depending of the values entered on the filters (maybe you will need modify the source in order to run).

  AQry.Close;
  AQry.SQL.Clear;
  AQry.SQL.Add('select * from TABLE where 1=1');
  if name.Text<>'' then
   AQry.SQL.Add('and record_name like :record_name');
  if Edittype.Text<>'' then
   AQry.SQL.Add('and record_type like :record_type');
  if type2.Text<>'' then
   AQry.SQL.Add('and record_type2 like :record_type2');
  if type3.Text<>'' then
   AQry.SQL.Add('and record_type3 like :record_type3');
  if type4.Text<>'' then
   AQry.SQL.Add('and record_type4 like :record_type4');

  if name.Text<>'' then
    Aqry.Parameters.ParamByName('record_name').Value :=  name.Text+ '%';
  if Edittype.Text<>'' then
    Aqry.Parameters.ParamByName('record_type').Value :=  Edittype.Text+ '%';
  if type2.Text<>'' then
    Aqry.Parameters.ParamByName('record_type2').Value :=  type2.Text+ '%';
  if type3.Text<>'' then
    Aqry.Parameters.ParamByName('record_type3').Value :=  type3.Text+ '%';
  if type4.Text<>'' then
    Aqry.Parameters.ParamByName('record_type4').Value :=  type4.Text+ '%';

  AQry.Open;

Upvotes: 3

Related Questions