2010. december 29., szerda

Incremental search in a DBGrid


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


Here is a sample project:

// Makes incremental search in a DBGrid with a TEdit

unit U_Main;


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

  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);

    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;
    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);

  Fm_Main: TFm_Main;


{$R *.DFM}

procedure TFm_Main.dbg_DataTitleClick(Column: TColumn);
  vi_Counter: Integer;
  vs_Field: string;
  with dbg_Data do
    //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
      SQL.Text := QueryStatement + 'ORDER BY ' + vs_Field;

    //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;
      Ed_Search.OnKeyPress := FALphaNumericKeyPress;
end; //End of TFm_Main.dbg_DataTitleClick

procedure TFm_Main.FloatOnKeyPress(Sender: TObject; var Key: Char);
  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);
  //Keep a pointer for the default event Handler
  FALphaNumericKeyPress := Ed_Search.OnKeyPress;

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

  //Select the first Grid�s Column
end; //End of TFm_Main.FormCreate

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

    //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]);
        Locate(vs_Field, Ed_Search.Text, [loCaseInsensitive,
end; //End of TFm_Main.Ed_SearchChange


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

Nincsenek megjegyzések:

Megjegyzés küldése