Reputation: 241
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?
Upvotes: 3
Views: 8895
Reputation: 6477
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.
Upvotes: 0
Reputation: 11968
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;
....
Upvotes: 2
Reputation: 4786
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;
Upvotes: 1
Reputation: 47889
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;
Upvotes: 6