sorting dbgrid
lazarus / 16-Databases

----------------------------------------------------------------------
Hansvb · 2014-05-07 19:16

I have a dbgrid connected to a datasource which is connected to a zeos
TQuery component. I want to sort de dbgrid by clicking on the title in
the dbgrid. How does it work?

----------------------------------------------------------------------
taazz · 2014-05-07 19:41

dbgrid is the view zeos Tquery is the model. When clicking the title
you re execute an sql that returns the data sorted as you need them ee
select * from MyTable order by MyClickedColumn thats it.

----------------------------------------------------------------------
Mike.Cornflake · 2014-05-07 22:41

This code is not intended to work out of the box for you.  I've swiped
it directly from a project of mine, and it has library calls to
functions you won't have.  Nevertheless, this shows the general steps
to go through...  Basically you don't sort the grid - you sort the
Dataset...

FDataset is just the Dataset I'm displaying in the grid.   My code
allows for either a TSQLQuery or TZQuery being held in FDataset - as
you can see I've only implemented sort for TZQuery.  You can tidy all
that FDataset code...

Code: [Select]Procedure TFrameGrid.grdSQLTitleClick(Column: TColumn); 
Var
  sTemp: String;
  oZQuery: TZQuery;
Begin
  If (FDataset = nil) Then
    Exit;

  // This code is sorting for FDataset=TZQuery  (ZeosLib)
  If (FDataset Is TZQuery) Then
  Begin
    oZQuery := TZQuery(FDataset);

    If oZQuery.Active And (oZQuery.RecordCount > 0) Then
    Begin
      sTemp := '"' + Column.Field.FieldName + '"';

      If (oZQuery.SortedFields = sTemp) Then
      Begin
        If oZQuery.SortType = stAscending Then
          oZQuery.SortType := stDescending
        Else
          oZQuery.SortType := stAscending;
      End
      Else
      Begin
        ClearSortImage(grdSQL);  // This calls a For-Loop, which sets
alls Column[n].Title.ImageIndex := -1;

        oZQuery.SortedFields := sTemp;
        oZQuery.SortType := stAscending;
      End;

      Column.Title.ImageIndex := Ord(oZQuery.SortType);

      //RefreshParent;  //  Just a call that refreshes the owner of
this grid.  (My Grid is actually sitting in a Frame)
    End;
  End;

  // TODO Add sorting code if we're using TSQLQuery
End;                                 
There's some good posts elsewhere on sorting as well...

I've been meaning to swipe the code from posts such as http://forum.la
zarus.freepascal.org/index.php/topic,20142.msg116100.html#msg116100 to
get the TSQLQuery sorting in...

See also: http://wiki.lazarus.freepascal.org/Grids_Reference_Page#Sort
ing_columns_or_rows_in_DBGrid_with_sort_arrows_in_column_header

----------------------------------------------------------------------
robert83a1 · 2014-05-21 08:03

Hi,

 I use this to sort my grids everywhere , this one will work out of
the box with dbgrid :

 Code: [Select]procedure GridSort(GridName : TDBGrid; Column: TColumn;
QueryName : TZReadOnlyQuery);
{$J+}
 const PreviousColumnIndex : integer = -1;
 const SortOrder : TSortType = stAscending;
{$J-}
begin
  try
//    GridName.Columns[PreviousColumnIndex].title.Font.Style:=GridName
.Columns[PreviousColumnIndex].title.Font.Style-[fsBold];
    GridName.Columns[PreviousColumnIndex].Title.Caption:=StringReplace
(GridName.Columns[PreviousColumnIndex].Title.Caption, '▼',
'',[rfReplaceAll, rfIgnoreCase]);
    GridName.Columns[PreviousColumnIndex].Title.Caption:=StringReplace
(GridName.Columns[PreviousColumnIndex].Title.Caption, '▲',
'',[rfReplaceAll, rfIgnoreCase]);
  except
  end;

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

  PreviousColumnIndex := Column.Index;

  QueryName.SortedFields:=Column.FieldName;

  GridName.Columns[Column.Index].Title.Caption:=StringReplace(GridName
.Columns[Column.Index].Title.Caption, '▼', '',[rfReplaceAll,
rfIgnoreCase]);
  GridName.Columns[Column.Index].Title.Caption:=StringReplace(GridName
.Columns[Column.Index].Title.Caption, '▲', '',[rfReplaceAll,
rfIgnoreCase]);

  if SortOrder = stAscending then
    begin
        SortOrder:=stDescending;
        QueryName.SortType:=stDescending;
        GridName.Columns[Column.Index].Title.Caption:=GridName.Columns
[Column.Index].Title.Caption+'▼';
    end
  else
    begin
        SortOrder:=stAscending;
        QueryName.SortType:=stAscending;
        GridName.Columns[Column.Index].Title.Caption:=GridName.Columns
[Column.Index].Title.Caption+'▲';
    end;
end;

procedure GridClearSort(GridName : TDBGrid; QueryName :
TZReadOnlyQuery);
var i : integer;
begin
  QueryName.SortedFields:='';
  QueryName.SortType:=stIgnored;

  for i:=0 to GridName.Columns.Count-1 do
    begin
//      GridName.Columns[i].title.Font.Style:=GridName.Columns[i].titl
e.Font.Style-[fsBold];
      GridName.Columns[i].Title.Caption:=StringReplace(GridName.Column
s[i].Title.Caption, '▼', '',[rfReplaceAll, rfIgnoreCase]);
      GridName.Columns[i].Title.Caption:=StringReplace(GridName.Column
s[i].Title.Caption, '▲', '',[rfReplaceAll, rfIgnoreCase]);
    end;
end;


and I use it like this on any grid :

Code: [Select]procedure TCityMain.DBGrid2TitleClick(Column: TColumn);
begin
  GridSort(DBGrid2,Column,CityQuery);
end;

procedure TCityMain.Image7Click(Sender: TObject);
begin
  GridClearSort(DBGrid2,CityQuery);
end;

I think once I'm done with the program I'm doing, I'm gona make a
component out of this ... will need to alter a few things... but I
also did ExportGridToExcel procedure
all work...only thing not builtin to dbgrid...

Greetings
Rob