Sortable DBGrid

Question

I want to implement a sortable DBgrid (that sorts its rows when clicked on column title). I managed to make it sortable in an ascending order but I can't do it in a descending order. Here are my design settings:

Query1.DatabaseName:='Test';
DataSetProvider1.DataSet:=Query1;
ClientDataSet1.ProviderName:=DataSetProvider1;
DataSource1.DataSet:=ClientDataSet1;
DBGrid1.DatSource:=DataSource1;

And here are fragments of my code:

procedure TForm2.FormShow(Sender: TObject);
begin
  Query1.Open;
  ClientDataSet1.Data:=DataSetProvider1.Data;
  ClientDataSet1.AddIndex('objnameDESC','objname',[ixDescending]);
  ClientDataSet1.AddIndex('SUM(cd.worktime)DESC','SUM(cd.worktime)',[ixDescending]);
end;

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
  case Column.Index of
  0: if ClientDataSet1.IndexFieldNames='objname' then
       ClientDataSet1.IndexFieldNames:='objnameDESC'
     else
       ClientDataSet1.IndexFieldNames:='objname';
  1: if ClientDataSet1.IndexFieldNames='SUM(cd.worktime)' then
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime)DESC'
     else
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime)';
  end;
end;

When I click on a column title for the first time, sorting is done in an ascending order - so up to here everything is OK. When I click for the second time I expect sorting in a descending order to be done but instead I get the message:

Project ... raised Exception class EDatabaseError with message
'ClientDataSet1: Field 'objnameDESC' not found'.

Any ideas about what I am doing wrong?

Answer 1

Many of my programs need this, so I wrote a general procedure which builds two indices for each field in the dataset

Procedure BuildIndices (cds: TClientDataSet);
var
 i, j: integer;
 alist: tstrings;

begin
 with cds do
  begin
   open;
   for i:= 0 to FieldCount - 1 do
   if fields[i].fieldkind <> fkCalculated then
    begin
     j:= i * 2;
     addindex ('idx' + inttostr (j), fieldlist.strings[i], [], '', '', 0);
     addindex ('idx' + inttostr (j+1), fieldlist.strings[i], [ixDescending], '', '',0);
    end;
   alist:= tstringlist.create;
   getindexnames (alist);
   alist.free;
   close;
  end;
end;

At this stage, I have indices idx0 and idx1 for the first field, idx2 and idx3 for the second field, etc.

Then, in the form which displays the dbgrid (here the active query is called qShowFees)

procedure TShowFees.DBGrid1TitleClick(Column: TColumn);
var
 n, ex: word;

begin
 n:= column.Index;
 try
  dbGrid1.columns[prevcol].title.font.color:= clNavy
 except
 end;

 dbGrid1.columns[n].title.font.color:= clRed;
 prevcol:= n;
 directions[n]:= not directions[n];

 ex:= n * 2;
 if directions[n] then inc (ex);
 with qShowFees do
  try
   disablecontrols;
   close;
   indexname:= 'idx' + inttostr (ex);
   open
  finally
   enablecontrols
  end;
end;

'Directions' is a form array of booleans which 'remembers' which way each column is currently sorted (ascending or descending) so clicking the dbgrid's title bar a second time will cause the grid to be sorted in the opposing manner to which it was sorted before. 'Prevcol' is a form variable which stores the currently selected column; this is saved between invocations, so the next time the user opens the form, it is sorted in the same way as she left it previously.

Answer 2

Wrong assignment

Apart from the fact that an incorrect assignment is made, a switch back to "ascending" is not possible.

For 2 Colums you need 4 Indexes.

Assuming 'objname' and 'SUM(cd.worktime)' are Fields.

procedure TForm2.FormShow(Sender: TObject);
....
ClientDataSet1.AddIndex('col0_asc','objname',[]);
ClientDataSet1.AddIndex('col0_desc','objname',[ixDescending]);
ClientDataSet1.AddIndex('col1_asc','SUM(cd.worktime)',[]);
ClientDataSet1.AddIndex('col1_desc','SUM(cd.worktime)',[ixDescending]);
....

Use ClientDataSet1.IndexName

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
  case Column.Index of
  0: if ClientDataSet1.IndexName='col0_asc' then
       ClientDataSet1.IndexName:='col0_desc'
     else
       ClientDataSet1.IndexName:='col0_asc';
  1: if ClientDataSet1.IndexName='col1_asc' then
       ClientDataSet1.IndexName:='col1_desc'
     else
       ClientDataSet1.IndexName:='col1_asc';
  end;
....

Or shorter

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
     if ClientDataSet1.IndexName='col'+IntToStr(Column.Index)+'_asc' then
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_desc'
     else
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_asc';
....

But it is better to test the number of columns that are active (AddIndex = done).

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
   if Column.Index < 2 then begin 
     if ClientDataSet1.IndexName='col'+IntToStr(Column.Index)+'_asc' then
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_desc'
     else
       ClientDataSet1.IndexName:='col'+IntToStr(Column.Index)+'_asc';
   end;
....

Answer 3

You should be setting the IndexName and not IndexFieldNames. IndexFieldNames accepts field names and creates an index on the fly.

procedure TForm2.DBGrid1TitleClick(Column: TColumn);
begin
  case Column.Index of
  0: if ClientDataSet1.IndexFieldNames='objname' then
       ClientDataSet1.IndexName:='objnameDESC'
     else
       ClientDataSet1.IndexFieldNames:='objname';
  1: if ClientDataSet1.IndexFieldNames='SUM(cd.worktime)' then
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime) DESC'
     else
       ClientDataSet1.IndexFieldNames:='SUM(cd.worktime)';
  end;
end;

Answer 4

As you are already using TClientDataSet you might make use of a component I made for exactly that purpose. Create an instance, set its Grid property and it will automatically connect to the OnTitleClick event.

type
  TDBGridSorter = class(TComponent)
  strict private
    FSortColumn: TColumn;
    FGrid: TDBGrid;
    procedure CreateIndex(const FieldName: string; Descending: Boolean);
    function GetDataSet: TClientDataSet;
      function MakeIndexName(const FieldName: string; Descending: Boolean): string;
    procedure SetSortColumn(const Value: TColumn);
    procedure SortByField(const FieldName: string; out Descending: Boolean);
  private
    procedure SetGrid(const Value: TDBGrid);
  strict protected
    procedure GridTitleClick(Column: TColumn);
    property DataSet: TClientDataSet read GetDataSet;
  public
    property Grid: TDBGrid read FGrid write SetGrid;
    property SortColumn: TColumn read FSortColumn write SetSortColumn;
  end;

procedure TDBGridSorter.CreateIndex(const FieldName: string; Descending: Boolean);
var
  cds: TClientDataSet;
  indexDef: TIndexDef;
  indexName: string;
begin
  cds := DataSet;
  if cds <> nil then begin
    indexName := MakeIndexName(FieldName, Descending);
    if cds.IndexDefs.IndexOf(indexName) < 0 then begin
      indexDef := cds.IndexDefs.AddIndexDef;
      indexDef.Name := indexName;
      indexDef.Fields := FieldName;
      indexDef.CaseInsFields := FieldName;
      if Descending then
        indexDef.DescFields := FieldName;
    end;
  end;
end;

function TDBGridSorter.GetDataSet: TClientDataSet;
begin
  if (Grid <> nil) and (Grid.DataSource <> nil) and (Grid.DataSource.DataSet is TClientDataSet) then
    Result := TClientDataSet(Grid.DataSource.DataSet)
  else
    Result := nil;
end;

procedure TDBGridSorter.GridTitleClick(Column: TColumn);
begin
  SortColumn := Column;
end;

function TDBGridSorter.MakeIndexName(const FieldName: string; Descending: Boolean): string;
const
  cAscDesc: array[Boolean] of string = ('_ASC', '_DESC');
begin
  Result := FieldName +  cAscDesc[Descending];
end;

procedure TDBGridSorter.SetGrid(const Value: TDBGrid);
begin
  if FGrid <> Value then begin
    if FGrid <> nil then begin
      FGrid.OnTitleClick := nil;
      FGrid.RemoveFreeNotification(Self);
    end;
    FGrid := Value;
    if FGrid <> nil then begin
      FGrid.FreeNotification(Self);
      FGrid.OnTitleClick := GridTitleClick;
    end;
  end;
end;

procedure TDBGridSorter.SetSortColumn(const Value: TColumn);
const
  cOrder: array[Boolean] of string = ('˄', '˅');
var
  descending: Boolean;
  S: string;
begin
  if FSortColumn <> nil then begin
    S := FSortColumn.Title.Caption;
    if StartsStr(cOrder[false], S) or StartsStr(cOrder[true], S) then begin
      Delete(S, 1, 2);
      FSortColumn.Title.Caption := S;
    end;
  end;
  FSortColumn := Value;
  if FSortColumn <> nil then begin
    SortByField(FSortColumn.FieldName, descending);
    FSortColumn.Title.Caption := Format('%s %s', [cOrder[descending], FSortColumn.Title.Caption]);
  end;
end;

procedure TDBGridSorter.SortByField(const FieldName: string; out Descending:
    Boolean);
var
  cds: TClientDataSet;
  curIndex: TIndexDef;
  N: Integer;
begin
  cds := DataSet;
  if cds <> nil then begin
    descending := false;
    N := cds.IndexDefs.IndexOf(cds.IndexName);
    if N >= 0 then begin
      curIndex := cds.IndexDefs[N];
      if SameText(FieldName, curIndex.Fields) then
        descending := not (ixDescending in curIndex.Options)
    end;
    { make sure the index exists }
    CreateIndex(FieldName, descending);
    cds.IndexName := MakeIndexName(FieldName, descending);
  end;
end;