Archive for the ‘dbgrid’ category

“Click-on-column-name-sorting” with dbgrid

June 6, 2006

This post describes a simple solution how to sort data by one column in TDBGrid by clicking on the column titles. Two simultaneous clicks on the same column will switch between ascending and descending orders. There are several other (and better :)) solutions out there, but this one I like for being small and simple. Downside of the solution is it won’t sort the rows by several columns.

OK. Here we go. TDataModule1 has a DataSource1 linked to TIBDataSet1 and TForm1 has a DBGrid1 linked to DataModule1.DataSource1.

In TIBDataSet1.SelectSQL we reserve one line for the sorting like this:

select colum1, column2, column3, column4
  from table1
/* this row can be replaced later by SQL[2] := 'order by ...' */

Delphi provides us with a handy class called TBits, where we can store the sorting order of the columns:

Use TBits to store and access an indefinite number of boolean values.

Declare a field of type TBits:

private
  SortingDirections: TBits;

Create and initialize it:

procedure TForm1.FormCreate(Sender: TObject);
begin
  DataModule1 := TDataModule1.Create();
  SortingDirections := TBits.Create();
  SortingDirections.Size := 
    DataModule1.IBDataSet1.FieldCount;
end;

Always write cleanup-code right after writing creation code 😉 :

procedure TForm1.FormDestroy(Sender: TObject);
begin
  ..
  SortingDirections.Free();
end;

Final step is to create a TDBGrid.OnTitleClick event handler:

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
var
  sSortMode: string;
begin

  // flip the sorting for this column
  SortingDirections[Column.Index] :=
    not SortingDirections[Column.Index];

  if SortingDirections[Column.Index] then
    sSortMode := 'asc'
  else
    sSortMode := 'desc';

  with DataModule1.IBDataSet1 do begin
    Close();
    SelectSQL.Strings[2] :=
      Format('order by %s %s', 
        [Column.FieldName, sSortMode]);
    Open();
  end;

end;

There You have it.

Advertisements