Sort DBGrid by clicking column's title

Question

Well, this seems a little tricky (if not imposible). I'm trying to make my DBGrid sort its data by clicking on column's title.

The thing is that I'm (sadly) working with Delphi 3, I'm not using ADO DataSets and the query gets a lot of rows, thus I can't reopen my TQuery changing the order by clause on clicks.

Someone has implemented something like this?

Answer 1

This is actually done by sorting the dataset, and then the grid reflects the change. It can be done easily enough by creating an index on the dataset field for that column. Of course, this can only be done on a dataset that supports index sorting, such as TClientDataset.

Answer 2

On the TDBGrid's OnTitleClick method you can do something like...

procedure TfmForm1.DBGrid1TitleClick(Column: TColumn);
var
   i: Integer;
begin
   // apply grid formatting changes here e.g. title styling
   with DBGrid1 do
      for i := 0 to Columns.Count - 1 do
         Columns[i].Title.Font.Style := Columns[i].Title.Font.Style - [fsBold];
   Column.Title.Font.Style := Column.Title.Font.Style + [fsBold];

   with nxQuery1 do // the DBGrid's query component (a [NexusDB] TnxQuery)
   begin
      DisableControls;
      if Active then Close;
      for i := 0 to SQL.Count - 1 do
         if (Pos('order by', LowerCase(SQL[i])) > 0) then
            //NOTE: ' desc' The [space] is important
            if (Pos(' desc',LowerCase(SQL[i])) > 0) then 
               SQL[i] := newOrderBySQL
            else
               SQL[i] := newOrderBySQL +' desc';
      // re-add params here if necessary
      if not Active then Open;
      EnableControls;
   end;
end;

There are plenty of ways in which you could optimise this I'm sure however it depends on the capabilities of the components you use. The example above uses a query component though if you used a table component you'd change the index used instead of the 'order by' clause.

The handling of the SQL here is a very basic version. It does not handle things like SQL batch statements, resulting in possible multiple 'order by..' clauses or commented SQL statements i.e. ignoring bracketed comments "{..}" or single line comments "//"

Regards

Answer 3

Delphi 3 have TClientDataset. And TQuery can use explicitly created indexes on the database to order data on the IndexName property.

Answer 4

Here are some examples of how to do this: Sorting records in Delphi DBGrid by Clicking on Column Title .

As already mentioned, sorting is quite easy if you are using a TClientDataSet (cds.IndexFieldNames := Column.FieldName in the OnTitleClick of the TDBGrid). However if you are not able to do this you can either regenerate your query (which you have stated you don't want to do) or obtain a more advanced data grid such as Express Quantum Grid (which I think allows you to sort).

Answer 5

On the TDBGrid's OnTitleClick method you can write this simple code:

procedure TForm1.DBGrid3TitleClick(Column: TColumn);
var
  cFieldName:string;
begin
  cFieldName:= DBGrid3.SelectedField.FieldName;
  AdoDataset1.Sort:=cFieldName;
end;

Answer 6

example: (https://www.thoughtco.com/sort-records-in-delphi-dbgrid-4077301)

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;

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
var
  cFieldName:string;
begin
  Adotable1.Sort := Column.Field.FieldName;
end;

Answer 7

If you are using a combination of TFDQuery, TDataSource and TDBGrid you can order with this easy way!

procedure TFrmGer.DBGridTitleClick(Column: TColumn);
begin
  OrderByTitle(MyFDQuery, Column);
end;

Put it in a helper file so you can use it latter again.

procedure OrderByTitle(AQuery: TFDQuery; Column: TColumn);
begin
  AQuery.IndexFieldNames := Column.DisplayName;
end;