002 – How to sort data in the database table

How to sort data in the database table 

The task

  Is given a “Product” table of database “db1.mdb“, that has the following structure

ID_Product

Name Code Count Price

  The field ID_Product is a primary key.

  Perform data sorting in table for a given field.

 

Progress

  1. Run Borland C++ Builder 2007.

2. Create a database in Microsoft Access, that contains one table named as “Product”.

  The file name of database is any (eg “db1.mdb“). Add in table “Product” at least three records to be capable of verification of sorting.

  1. Create Windows application.

 4. Forming of form components (see fig. 1).

Place on the form such components placed on “Tool palette”:

– TADOConnection (“DbGo” tab);

– TADOTable (“DbGo” tab);

– TDataSource (“DataAccess” tab);

– TDBGrid (“Data Controls” tab).

 As a result, objects-variables are formed with names: ADOConnection1, ADOTable1, DataSource1, DBGrid1.

05_01_00_002_01_

Fig. 1. Main form of application

  1. Setting connections between components.

To set connections between components and connect it with our database we need to do:

– connect database file to our program with the help of ConnectionString property of ADOConnection1 component;

– the property “Connection” of ADOTable1 component set to “ADOConnection1” value;

– the property “DataSet” of DataSource1 component set to “ADOTable1” value;

– the property “DataSource” of DBGrid1 component set to “DataSource1” value.

  1. Setting access to database without password check.

  Select the ADOConnection1 component on the form. Set the “LoginPrompt” property to “false”.

  1. Activation of “Product” table.

 The property “TableName” of ADOTable1 component set to “Product” value (select from the drop down list).

  “Active” property of ADOTable1 component need to set to “true”. As a result “Product” table will be shown on screen.

  1. DBGrid1 setting.

  Adjust width of fields of table “Product” so that all fields will be appear in the window of DBGrid1 component.

  1. Call buttons of sorting.

  For example, add three buttons on the form with names Button1, Button2 and Button3.

  Form the “Caption” property as follows:

– Button1.Caption = “Name”;

– Button2.Caption = “Code”;

– Button3.Caption = “Count”.

In general the form would look like as shown on figure 2.

05_01_00_002_02_

Fig. 2. Main form

10. Programming of event handlers.

  The code of event handlers of mouse clicking to buttons Button1, Button2, Button3 is shown in the following listing.

// Button "Button1" (sorting by "Name")
void __fastcall TForm1::Button1Click(TObject *Sender)
{
     ADOTable1->IndexFieldNames = "Name";  
}

// Button "Button2" (sorting by "Code")
void __fastcall TForm1::Button2Click(TObject *Sender)
{
     ADOTable1->IndexFieldNames = "Code";  
}

// Button "Button3" (sorting by "Count")
void __fastcall TForm1::Button3Click(TObject *Sender)
{
     ADOTable1->IndexFieldNames = "Count"; 
}

  As we see from code, all three handlers set the IndexFieldsNames property of ADOTable1 component into table field value. Automatically, the sorting takes place on a given field.

  If the field IndexFieldNames is left blank, data will sort by primary key. If the primary key is not in the table, the data will be displayed as they are typed to the table.

  A wider sorting allows TADOQuery component.

  After running the project we see that the data in the table are sorting by pressing the appropriate button.