An example of sorting in database using component TADOQuery

Often in applications that work with databases, you need to realize the sorting of data for some field, which is selected by mouse clicking on it’s title. This problem is solved in this task.

 

The task

Database “01_02_00_010_mydb.mdb”, which was formed by Microsoft Access, is given.

Database consists three tables.

Table Group.

Table Student.

Table Marks.

You need to develop the application, which shows table “Marks”. Into the table “Marks”, you need realize the sorting of data using all fields. You need realize the sorting in ascending or descending order. Start of sorting should be called when you click on the header of the corresponding field of table “Marks“.

The interconnection scheme between tables is shown in Figure 1.

Fig. 1. The interconnection scheme between tables

 

Progress

1. Run Embarcadero RAD Studio.

Save the project in some folder. For example

"C:\Programs\Delphi\Program_01_02_00_010u".

The name of module of main form is “Unit1.pas” by default.

The project name is “Project1.dproj” by default.

 

2. Building the form.

Place on the form components of following types:

• from tab dbGo the component of type TADOConnection for connecting to database (Fig. 2);
• from tab dbGo component of type TADOQuery to organize the SQL-query (Fig. 2);
• from tab “Data Access” component of type TDataSource to connect between the dataset and component of TDBGrid type (Fig. 3);
• from tab “Data Controls” component of type TDBGrid to showing the results of SQL-query, which is formed in component TADOQuery (Fig. 4).

 

Fig. 2. Components TADOConnection and TADOQuery

Fig. 3. Component of type TDataSource

Fig. 4. Component of type TDBGrid

After placing of components, the application form will be following (Fig. 5).

Fig. 5. Application form after placing the components

After building the form, the names of corresponding objects (variables) are following:

• ADOConnection1;
• ADOQuery1;
• DataSource1;
• DBGrid1.

 

3. Connecting database to application.

To connect the application to database, you need to set the property ConnectionString of component ADOConnection1.

Process of setting up “ConnectionString” is described here in details.

When you form the property ConnectionString in wizard windows, you need to set provider OLE DB – “Microsoft Jet 4.0 OLE DB Provider”.

In our case you need to set the path to database as:

C:\Programs\Delphi\Program_01_02_00_010u\01_02_00_010_mydb.mdb

 

4. Setting up the interconnections between components ADOConnection1, ADOQuery1, DataSource1, DBGrid1.

Now it is needed to connect the components together. For this you need to do the following actions:

• in the component ADOQuery1 property Connection = “ADOConnection1”;
• in the component DataSource property DataSet=”ADOQuery1″;
• in the component DBGRid1 property DataSource = “DataSource1“.

To avoid login and password checking, in component ADOConnection1 you need to set the property

LoginPrompt = false

 

5. Setting up of component of TADOQuery type.

Next step, you need to set up component TADOQuery.

To do it, in “Object Inspector” you need to select property “SQL” by clicking on “…” (Fig. 6). As a result, the window of editor “String List Editor” will be opened.

Fig. 6. Property “SQL” of component ADOQuery1

In the editor “String List Editor” you need to type the following text of SQL-query:

SELECT *
FROM  [Marks]

Fig. 7. Text of SQL-query in ADOQuery1

 

6. Property “Active” of component ADOQuery1.

Next step – displaying the table “Marks” in component DBGrid1.

To do it you need:

• select component ADOQuery1;
• set the property “Active” to value “true“.

As a result, table Marks will be displayed in component DBGrid1. Data of table, which are displayed, are based on the SQL-query, which is formed in property “SQL” of component “ADOQuery1” (Figure 8).

Fig. 8. Displaying data in DBGrid1

 

7. Creating a program code of sorting data in the fields of table “Marks“.

Now it is needed write the program code of sorting, when user does the click on the title of any field. If user clicks on the title first time, then sorting is realized as ascending. If user clicks second time on the same field, then sorting is realized as descending and so on.

When user clicks on the title of table in component DBGrid1, the event OnTitleClick is generated. If to intercept this event, it is possible to carry out the sorting on the desired field.

 

7.1. Inputting variables Order and Field.

Into section “private” of class TForm1, it is needed to enter variables named “Order” and “Field“. The corresponding code snippet is following:

...
type
  TForm1 = class(TForm)
    ADOConnection1: TADOConnection;
    ADOQuery1: TADOQuery;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    procedure DBGrid1TitleClick(Column: TColumn);
  private
    { Private declarations }
    Order:boolean; // Variable, which points the type of previous sorting.
    Field:integer; // Field's number, which were done the previous sorting
  public
    { Public declarations }
  end;
...

Variable “Order” points to the order of previous sorting:

• true – the sorting in ascending;
• false – the sorting in descending.

Variable “Field” points the number of field, for which were done previous sorting.

 

7.2. Programming an event of activation of form OnActivate.

Event “OnActivate” is generated, when form must be opened after running the application. It is needed to type the code, which initializes variables “Order” and “Field“.

When form is opened, the sorting of data is based on the field ID_Marks in ascending. Because, the variable “Order” must be equal “true” and variable “Field” must be equal 0.

To program the event OnActivate you need to do following:

• select the form “Form1“;
• in “Object Inspector” go to tab “Events“;
• do the double click by mouse in the field of event OnActivate.

The event handler is the following:

procedure TForm1.FormActivate(Sender: TObject);
begin
  order := true;
  field := 0;
end;

 

7.3. Programming the event of clicking on the title of table, which is displayed in DBGrid1.

As mentioned earlier, when you clicks on the title of table Marks, the event OnTitleClick is generated.

Code of event handler OnTitleClick is the following:

procedure TForm1.DBGrid1TitleClick(Column: TColumn);
var
  new_field:integer;
begin

  // forming of the initial values of internal variables
  new_field := Column.Index;
  // database connetion closing
  ADOQuery1.Close;
  // cleaning the text of previous SQL-query
  ADOQuery1.SQL.Clear;
  // adding a new SQL-query template strings
  ADOQuery1.SQL.Add('SELECT * FROM [Marks] ORDER BY ');
  if new_field = field then // the click on the same field
    order := not order   // change the sort order
  else
    begin
      // the click on another field
      field := new_field;
      order := true;
    end;
  case field of
    0: ADOQuery1.SQL.Add(' [ID_Marks] ');
    1: ADOQuery1.SQL.Add(' [Physics] ');
    2: ADOQuery1.SQL.Add(' [Mathematics] ');
    3: ADOQuery1.SQL.Add(' [Jurisprudence] ');
    4: ADOQuery1.SQL.Add(' [PhysicalTraining] ');
    5: ADOQuery1.SQL.Add(' [ID_Student] ');
  end;

  if not order then
    ADOQuery1.SQL.Add(' DESC');
  ADOQuery1.Open;
end;

Event handler gets as input parameter the column number “Column” of table, on which user did the click by mouse. On the basis of the value “Column” of the table, is defined the same or another field, on which user clicks. So, the variables “Field” and “Order” are formed.

When the text of SQL-query is formed in component ADOQuery1, it is needed close the connection with database. It is realized with the help of method Close():

ADOQuery1.Close

There are two methods in the property “SQL” of component ADOQuery1, which do the following:

• method Clear() cleans the text of SQL-query;
• method Add() adds a new text to the text of SQL-query.

⇑

---