Incremental search in a DBGrid

Mirror

Incremental search in a DBGrid (Views: 712)

Problem/Question/Abstract:

When you fill a DBGrid with Data from a Query you can search for each column of the Grid, with a TEdit.

Answer:

Here is a sample project:

// Makes incremental search in a DBGrid with a TEdit

unit U_Main;

interface

uses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  Db, DBTables, StdCtrls, Grids, DBGrids, ExtCtrls, DBCtrls;

type
  TFm_Main = class(TForm)
    Panel1: TPanel;
    Panel2: TPanel;
    qry_Data: TQuery;
    Ds_Data: TDataSource;
    dbg_Data: TDBGrid;
    Label1: TLabel;
    Ed_Search: TEdit;
    Database1: TDatabase;
    qry_DataNUM_FACTURA: TStringField;
    qry_DataF_FACTURA: TDateTimeField;
    qry_DataM_DEVENGADO: TFloatField;
    DBNavigator1: TDBNavigator;
    procedure dbg_DataTitleClick(Column: TColumn);
    procedure FormCreate(Sender: TObject);
    procedure Ed_SearchChange(Sender: TObject);

  private
    FQueryStatement: string;

    //Since for Alphanumeric Field you don´t need to validate nothing
    //just keep a method pointer to the default Event Handler
    FALphaNumericKeyPress: TKeyPressEvent;
  public
    property QueryStatement: string read FQueryStatement;

    //Since we are going to search in various Fields wich DataType
    //can be of diferent types, we must validate the user input on
    //the OnkeyPress of the TEdit, but instead of building a super
    //generic routine, lets make things simple. Build a separate
    //method for each DataType you are interested in validate.

    //I will only validate for Fields of type ftFloat, but you easily
    //customize the code for your own needs..

    //Method Pointer for Fields of DataType ftFloat
    procedure FloatOnKeyPress(Sender: TObject; var Key: Char);
  end;

var
  Fm_Main: TFm_Main;

implementation

{$R *.DFM}

procedure TFm_Main.dbg_DataTitleClick(Column: TColumn);
var
  vi_Counter: Integer;
  vs_Field: string;
begin
  with dbg_Data do
  begin
    //First, deselect all the Grid´s Columns
    for vi_Counter := 0 to Columns.Count - 1 do
      Columns[vi_Counter].Color := clWindow;

    //Next "Select" the column the user has Clicked on
    Column.Color := clTeal;

    //Get the FieldName of the Selected Column
    vs_Field := Column.FieldName;

    //Order the Grid´s Data by the Selected column
    with qry_Data do
    begin
      DisableControls;
      Close;
      SQL.Clear;
      SQL.Text := QueryStatement + 'ORDER BY ' + vs_Field;
      Open;
      EnableControls;
    end;

    //Get the DataType of the selected Field and change the Edit´s event
    //OnKeyPress to the proper method Pointer
    case Column.Field.DataType of
      ftFloat: Ed_Search.OnKeyPress := FloatOnKeyPress;
    else
      Ed_Search.OnKeyPress := FALphaNumericKeyPress;
    end;
  end;
end; //End of TFm_Main.dbg_DataTitleClick

procedure TFm_Main.FloatOnKeyPress(Sender: TObject; var Key: Char);
begin
  if not (Key in ['0'..'9', #13, #8, #10, #46]) then
    Key := #0;
end; //End of TFm_Main.FloatOnKeyPress

procedure TFm_Main.FormCreate(Sender: TObject);
begin
  //Keep a pointer for the default event Handler
  FALphaNumericKeyPress := Ed_Search.OnKeyPress;

  //Set the original Query SQL Statement
  FQueryStatement := 'SELECT FIELD1, FIELD2, FIELD3 '
    'FROM ANYTABLE ';

  //Select the first Grid´s Column
  dbg_DataTitleClick(dbg_Data.Columns[0]);
end; //End of TFm_Main.FormCreate

procedure TFm_Main.Ed_SearchChange(Sender: TObject);
var
  vi_counter: Integer;
  vs_Field: string;
begin
  with dbg_Data do
  begin
    //First determine wich is the Selected Column
    for vi_Counter := 0 to Columns.Count - 1 do
      if Columns[vi_Counter].Color = clTeal then
      begin
        vs_Field := Columns[vi_Counter].FieldName;
        Break;
      end;

    //Locate the Value in the Query
    with qry_Data do
      case Columns[vi_Counter].Field.DataType of
        ftFloat: Locate(vs_Field, StrToFloat(Ed_Search.Text),
            [loCaseInsensitive, loPartialKey]);
      else
        Locate(vs_Field, Ed_Search.Text, [loCaseInsensitive,
          loPartialKey]);
      end;
  end;
end; //End of TFm_Main.Ed_SearchChange

end.

So, you can customize the code to manage another DataTypes of TFields.

<< Back to main page