1. Computing
Sorting records in Delphi DBGrid by Clicking on Column Title
How to sort records in Delphi DbGrid by clicking on the column title. Plus: how to change the appearance of the selected column title to reflect the sort order. Even more: how to change the cursor when moving over the DBGrid column titles.
 Join the Discussion
"Post your views, comments, questions and doubts to this article."
Discuss!
 Related Resources
• free DB Course.TOC
• Coloring DBGrid
• Multiple row selection in DBGrid
• Using Delphi DB components
• more Database articles

Delphi DBGrid ... what a powerful component! If you are developing data aware applications you are probably using the DBGrid component every day.
In this article you'll see how to add some more nitty gritty features to your database applications - your users will love it!

Following the concepts set in the Beginners Guide to Delphi Database Programming, examples below use ADO components (AdoQuery/AdoTable connected to ADOConnection, DBGrid connected to AdoQuery over DataSource) to display the records from a database table in a DBGrid component. All the component names were left as Delphi named them when dropped on the form (DBGrid1, ADOQuery1, AdoTable1, ...)
If you do not know how to display records from a database table (or query) in a DBGrid component, please explore the "Connecting to a database" chapter.

Mouse moves over DBGrid title area?
First, let's see how to change the mouse pointer while it moves over the DBGrid title area. All you have to do is to add the code to the OnMouseMove event for the DBGrid component. The code below simply uses the MouseCoord property of the DBGrid component to "calculate" where the mouse pointer is - if over DGBrid title area, the pt.y equals 0, which is the first row in the DBGrid - the title area (displaying column/field titles).

procedure TForm1.DBGrid1MouseMove
  (Sender: TObject; Shift: TShiftState; X, Y: Integer);
var
  pt: TGridcoord;
begin
  pt:= DBGrid1.MouseCoord(x, y);

  if pt.y=0 then
    DBGrid1.Cursor:=crHandPoint
  else
    DBGrid1.Cursor:=crDefault;
end;

Sort on column click? Change font of the sorted column title?
The next piece of code is my favorite. If you are using ADO approach to Delphi database development, and want to sort the records in the dataset, you need to set the Sort property of your AdoDataset (ADOQuery, AdoTable). The Sort property is the widestring value indicating the "ORDER BY" part of the standard SQL query - of course you do not need to write the SQL query to be able to use the Sort property. You simply set the Sort property to the name of a single field or to a comma-separated list of fields, each following the sort order. Something like:

ADOTable1.Sort := 'Year DESC, ArticleDate ASC'

The OnTitleClick event of the DBGrid component has a Column parameter indicating the Column the user has clicked on. Now, each Column (object of type TColumn) has a Field property indicating the Field (TField) represented by the Column - and the Field in its FieldName property holds the name of the field in the underlying dataset. Therefore, to sort an ADO dataset by field/column a simple line can be used:

with TCustomADODataSet(DBGrid1.DataSource.DataSet) do
  Sort := Column.Field.FieldName; // + ' ASC' or ' DESC'

Below you can find the code for the OnTitleClick even handler that sorts the records by column click. The code, as always, extends the idea.
First we want to, in some way, mark the column that is currently used for sort order. Next, if we click on a column title and the dataset is already sorted by that column we want to change the sort order from ASC (ascending) to DESC (descending) and vice versa. And finally, when we sort the dataset by another column we want to remove the mark from the previously selected column.

For the sake of simplicity, to mark the column that "sorts" the records we'll simply change the font style of the column title to Bold, and remove it when dataset is sorted using another column.

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
{$J+}
 const PreviousColumnIndex : integer = -1;
{$J-}
begin
  if DBGrid1.DataSource.DataSet is TCustomADODataSet then
  with TCustomADODataSet(DBGrid1.DataSource.DataSet) do
  begin
    try
      DBGrid1.Columns[PreviousColumnIndex].title.Font.Style :=
      DBGrid1.Columns[PreviousColumnIndex].title.Font.Style - [fsBold];
    except
    end;

    Column.title.Font.Style := 
    Column.title.Font.Style + [fsBold];
    PreviousColumnIndex := Column.Index;

    if (Pos(Column.Field.FieldName, Sort) = 1)
    and (Pos(' DESC', Sort)= 0) then
      Sort := Column.Field.FieldName + ' DESC'
    else
      Sort := Column.Field.FieldName + ' ASC';
  end;
end;

Note: the code above uses typed constants to preserve the value of the previously "selected" column for sort order.

Delphi DBGrid with Column title click sort

That's it. Simple and powerful - this is why you have picked Delphi. If you want more articles describing how to use various Delphi data aware component, go for Using Delphi DB components.

A Beginner's Guide to Delphi Database Programming >>
>> the TOP

©2014 About.com. All rights reserved.