Solved

Sorting in TCustomDBGrid

Posted on 1998-07-23
8
847 Views
Last Modified: 2011-09-20
I have to sort rows in TCustomDBGrid. By clicking a column it have to sort by this column (like in Win Explorer). Have You got any useful tips?

Greetz
Bartek
0
Comment
Question by:bartekz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +2
8 Comments
 
LVL 3

Expert Comment

by:vladika
ID: 1358478
If you use TTable ...
In the OnTitleClick handler try to set Table.IndexFieldName property equal Column.FieldName

If you use TQuery ...
In the OnTitleClick handler try to recreate TQuery.SQL property
by adding proper 'ORDER BY' statement in it


0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1358479
Vladika has a good point there. Zif.
0
 

Author Comment

by:bartekz
ID: 1358480
Well, I didn't say I'm writing a component derived from TDBGrid.
In OnTitleClick I can read field values, then I need to sort it and I have to redraw grid (probably remapping fields in DefineFieldMap method). It has to work like this:
- after first clicking on title -> sorting ascending,
- after second clicking on title -> sorting descending,
It has to sort by cliked field/column (like in Win Explorer)

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:Matvey
ID: 1358481
You should use SQL:
SELECT * FROM <table_name> ORDER BY <field_name> ASCENDING/DESCENDING.

If you click first time you sort ascending, seccond change it to descending. You could also use two indexes here, bt SQL is easier and doesn't require indexes.

If you want the titles of the DB grid to look like buttons so you can actually get the feeling of pressing them,  then use something like RXDBGrid (freeware at http://rx.demo.ru/).
0
 
LVL 1

Accepted Solution

by:
venks earned 100 total points
ID: 1358482
Dear bartekz
Following is an aricle that i dug out from my archive which may be of help to you.
Some recoding is required(I could not find the code that i had written long back)
It is an unusual approach but it works.
The atricle had appeared   in # 18 Nov 96 in the unofficial news letter of delphi users.
Venks



Sorting Columns in a DBGrid
by Robert Vivrette - 76416.1373@compuserve.com
Many professional applications will display data in grid fields and allow you to sort on any one of the columns simply by clicking on the column header. Although what is proposed here is not the best way to accomplish this, it is a fairly simple way to mimic the same behavior.
The key hurdle in this problem is the DBGrid itself. It has no OnClick or OnMouseDown events, so it really was not designed to capture this kind of input. It does provide an OnDoubleClick, but this really doesn't work too well. What we need is a way to make the column headers clickable. Enter the THeaderControl component.

THeaderControl is a component that comes in Delphi 2.0 and provides the basic functions that we want. It can detect clicks on its individual panels, and the panels even go up and down when pressed (like a button). The key is to connect the THeaderControl to the DBGrid. Here is how it is done:
First, start a new application. Drop a THeaderControl on the form. It will automatically align to the top edge of the form. Now drop a DBGrid on the form and set its Align property to alClient. Next, add a TTable, and TDataSource component. Set the Tables DatabaseName property to DBDEMOS and its TableName to EVENTS.DB. Set the DataSource's DataSet property to point at Table1 and the DBGrid's DataSource property to point to DataSource1. Set Table's Active property to False in case it has been turned on. Now the fun begins!

Now we need to setup the THeaderControl component to look like the DBGrid's column headers. This will be done in code in the Form's FormCreate method. DoubleClick on Form1's OnCreate event and enter the following code:

procedure TForm1.FormCreate(Sender: TObject);
var
  TheCap : String;
  TheWidth,a : Integer;
begin
  DBGrid1.Options := DBGrid1.Options - [dgTitles];
  HeaderControl1.Sections.Add;
  HeaderControl1.Sections.Items[0].Width := 12;
  Table1.Exclusive := True;
  Table1.Active := True;
  For a := 1 to DBGrid1.Columns.Count do
    begin
      with DBGrid1.Columns.Items[a-1] do
        begin

          TheCap := Title.Caption;
          TheWidth := Width;
        end;
      with HeaderControl1.Sections do
        begin
          Add;
          Items[a].Text := TheCap;
          Items[a].Width := TheWidth+1;
          Items[a].MinWidth := TheWidth+1;
          Items[a].MaxWidth := TheWidth+1;
        end;
      try
        Table1.AddIndex(TheCap,TheCap,[]);
      except
        HeaderControl1.Sections.Items[a].AllowClick := False;

      end;
    end;
  Table1.Active := False;
  Table1.Exclusive := False;
  Table1.Active := True;
end;


Since the THeaderControl will be taking the place of the Grid's column headers, we first remove (set to False) the dgTitles option in the DBGrid's Options property. Then, we add a column to the HeaderControl and set its width to 12. This will be a blank column that is the same width as the Grid's status area on the left.
Next we need to make sure the Table is opened for Exclusive use (no other users can be using it). I will explain why in just a bit.
Now we add the HeaderControl sections. For each one we add, we will be giving it the same text as the caption of that column in the DBGrid. We loop through the DBGrid columns, and for each one we copy over the column's caption and width. We also set the HeaderControl's MinWidth and MaxWidth properties to the same as the column width. This will prevent the column from being resized. If you need resizeable columns, you will need a bit more code, and I wanted to keep this short and sweet.

Now comes the interesting part. We are going to create an index for each column in the DBGrid. The name of the index will be the same as the columns title. This step is in a try..finally structure because there are some fields that cannot be indexed (Blobs & Memos for example). When it tries to index on these fields, it will generate an exception. We catch this exception and turn off the ability to click that column. This means that non-indexed columns will not respond to mouse clicks. The creation of these indexes is why we had to open the table in Exclusive mode. After we are all done, we close the table, set Exclusive off and reopen then table.

One last step. When the HeaderControl is clicked, we need to turn on the correct index for the Table. The HeaderControl's OnSectionClick method should be as follows:

procedure TForm1.HeaderControl1SectionClick(
                HeaderControl: THeaderControl;
                Section: THeaderSection);
begin
  Table1.IndexName := Section.Text;
end;


That's it! When the column is clicked, the Table's IndexName property is set to the same as the HeaderControl's caption.
Pretty simple, huh? There is a lot of room for improvement however. It would be nice if clicking on a column a second time would reverse the sort order. Also, column resizing would be a nice added touch. I am going to leave these to you folks!



(* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

0
 
LVL 3

Expert Comment

by:vladika
ID: 1358483
I think this equivalent to my short comment
"In the OnTitleClick handler try to set Table.IndexFieldName property equal Column.FieldName"

And this will not work, for example, for client-server databases
(Table1.Exclusive := True - It's strong demand for such task
and user may havn't rights for such operation as creating index)

And how about Query?

0
 

Author Comment

by:bartekz
ID: 1358484
Thanx for all answers. But in my component I don't have access to Table or Query (so I can't use Table.IndexFieldName), I only have Datase property. I generated TQuery object and executed query which should sort it, but data shown in Grid is still not sorted! I tried to refresh it, but... doesn't work. I really think I have to remap field in DefineFieldMap method and redraw Grid after it.
0
 
LVL 3

Expert Comment

by:Matvey
ID: 1358485
The query doesn't sort it phisically!!!
To sort the table phisically use DbiSortTable.



BDE API Examples (DbiSortTable)

Sorts an opened or closed table, either into itself or into a destination table. There are options to remove
duplicates, to enable case-insensitive sorts and special sort functions, and to control the number of
records sorted.


Example 1: Sort a table on a single field.

Options are: Case Insensitive, Case Sensitive, Descending, and Ascending.
This example uses the following input:
SortTable(Table1, Table1.FieldByName('SortField'), False, sortDESCEND, L);
NOTE: L is defined as a longint and specifies the amount of records to sort. Use TTable.RecordCount
to sort the entire table.
The function is defined as follows:

procedure SortTable(Table: TTable; Field: TField; CaseInsensitive: boolean;
                Order: SORTOrder; var SortNumber: longint);
var
  Props: CURProps;
  FieldNumber: word;
  hDb: hDBIDb;

begin
  if Table.Active = False then
    raise EDatabaseError.Create('Table must be opened');
  Check(DbiGetCursorProps(Table.Handle, Props));
  if Props.bIndexed = True then
    raise EDatabaseError.Create('No index can be active when sorting the table');
  FieldNumber := Field.Index + 1;
  Check(DbiGetObjFromObj(hDBIObj(Table.Handle), objDATABASE, hDBIObj(hDb)));
  Table.Close;
  try
    Check(DbiSortTable(hDb, PChar(Table.TableName), Props.szTableType,
             nil, nil, nil, nil, 1, @FieldNumber, @CaseInsensitive, @Order, nil,
             False, nil, SortNumber));
  finally
    Table.Open;
  end;
end;


(http://www.inprise.com/devsupport/bde/bdeapiex/)
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Dev Express grid collapse 2 55
Wincontrol not (correctly) drawn 15 59
How to Get Images From Server to Client using App Tethering 1 57
Get weeknumber and year from date 4 56
The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question