An example of creating of calculating fields in component of type TDBGrid

The process of creating of fields of database table, in which is data calculating, is described.

 

The task

The database named “01_02_00_009_mydb.mdb” is given. Database contains three tables: Students, Marks, Groups.

The structure of tables is the following.

Table “Students“.

Table “Groups“.

Table “Marks“.

The tables are connected between them by such scheme (Fig. 1):

Fig. 1. The scheme of interconnection between tables

In this task you need to create a result table, which contains such fields.

The field “Average” must be created as a calculating field.

 

Progress

1. Run Delphi.

Save the project.

2. Developing the form.

To solve given problem we will use following components:

– component of type TADOConnection to connecting with database;

– component of type TADOQuery for organizing queries at SQL-language;

– component of type TDataSource for interconnection between data and component TDBGrid of visualization data;

– component of type TDBGrid for displaying of results of SQL-query, which is formed in component TADOQuery.

Components TADOConnection and TADOQuery are placed on the tab dbGo of components palette “Tool Palette” (Fig. 2).

Fig. 2. Components TADOConnection and TADOQuery

Component TDataSource is placed on the tab “Data Access” of component’s palette “Tool Palette” (Fig. 3).

Fig. 3. Component TDataSource

Component TDBGrid is placed on the tab “Data Controls” of component’s palette (Fig. 4).

Fig. 4. Component TDBGrid from tab “Data Controls“

After placing components, the main form of application has the view as shown at figure 5.

Fig. 5. Main form of application after placing components of type TADOConnection, TADOQuery, TDataSource, TDBGrid

As a result, Delphi automatically creates four objects with names ADOConnection1, ADOQuery1, DataSource1, DBGrid1. With the help of these names you can have access to properties and methods of components. Optionally, in the field Name in Object Inspector of corresponding component, you can change the name of object (variable). Let’s leave it as it is.

 

3. Connecting to database.

To connect to database you need fill the property “ConnectionString” of component TADOConnection. The process of connecting to database is described here. Connecting to database is created as step by step process with the help of connection wizard and in this article will not be described.

As a result, we have filled property “ConnectionString” of component ADOConnection1.

In this property the location of database file and other parameters are specified.

 

4. Property SQL of component ADOQuery1.

The next step is forming of SQL-query, in which will be choosed fields from tables according to problem condition, and also will be formed new calculating field. In the calculating field, we will calculate mathematical average.

Select component ADOQuery1. In the Object Inspector select “…” in front of name “SQL” (Fig. 6).

Fig. 6. Property SQL of component ADOQuery1

As a result, the window of editor “String List Editor” will be opened (Fig. 7).

Fig. 7. Editor to input the query at SQL-language

In the editor type the following text of SQL-query:

SELECT [Students.Name],
       [Marks.Mathematics],
       [Marks.Physics],
       [Marks.Jurisprudence],
       [Marks].[PhysicalTraining],
       (Mathematics+Physics+Jurisprudence+PhysicalTraining)/4 AS   [Average]
FROM 
       [Students], [Marks]
WHERE
       ([Students.ID_Student]=[Marks.ID_Student])

Let’s explain some moments in text of SQL-query.

Command

SELECT ... FROM ... WHERE

means, that you need to select from database some information using a condition (word WHERE).

After word “SELECT” are given fields, which must be displayed in component DBGrid1 (according to problem condition):

– [Students.Name] – name of student;

– [Marks.Mathematics] – evaluating by mathematics;

– [Marks.Physics] – evaluating by physics;

– [Marks.Jurisprudence] – evaluating by jurisprudence;

– [Marks].[PhysicalTraining] – evaluating by physical training;

– [Average] – field, in which mathematical average is calculated.

After word “FROM” tables of database are given. Based on these tables is created the result table. In our case these are tables Students and Marks.

After word “WHERE” the condition of data selection is given.

In our case, the unique identifier of student in the table Students can match to the unique identifier of student from table “Marks“. It is necessary for avoiding of repeating of the students evaluations when information is output.

The window of SQL-editor has the following view (Fig. 8).

Fig. 8. Editor “String List Editor”

 

5. Property Active of component ADOQuery1.

In order to displaying the data in table DBGrid1, you need to set the property Active of component ADOQuery1 to “true” (Fig. 9).

Fig. 9. Property Active of component ADOQuery1

As a result, the all fields, which are described in the text of SQL-query, will be displayed. The calculating field of mathematical average will be shown also.

 

6. Field’s editor of component ADOQuery1.

As you can see at Figure 9, the view of fields and records, which are displayed in component DBGrid1, is not correct. Therefore, we will try to configure these fields for better viewing.

First of all, we need to call the fields editor of component ADOQuery1. This is done by simply selection of command “Fields Editor…” from context menu, that will be called after pressing the right mouse at the component ADOQuery1 (Fig. 10).

Fig. 10. Calling the fields editor of component ADOQuery1

In the editor Form1.ADOQuery1 you need to do the click of right mouse button and select command “Add All Fields…” (Fig. 11).

Fig. 11. Command “Add All Fields” of adding of all fields from SQL-query

As a result, the window of fields editor will have view as shown at figure 12. For each field the corresponding object will be created. You can control by every object in Object Inspector. For example, for field “Students.Name” is created the object with name ADOQuery1StudentsName, for field Marks.Mathematics is created the object with name ADOQuery1MarksMathematics and so on.

Fig. 12. Fields, that may be processed according to SQL-query

Let’s set the output of mathematical average with an accuracy of 2 decimal places. To do this, select the field “Average” and in the Object Inspector set the property “Displat Format” to value “.00” (Fig. 13).

Fig. 13. Field “Display Format” of object ADOQuery1Average

Close the editor of fields of component ADOQuery1.

 

7. Editor of columns (fields) of component DBGrid1.

For better data visualization from database table you need to use the editor of columns (fields) “Columns Editor…” of component DBGrid1.

Fig. 14. Calling the editor of columns “Columns Editor…” of component DBGrid1

As a result, the window, shown at Figure 15, will be opened. In the window, using mouse you need to call the context menu, in which you need to select “Add All Fields”.

Fig. 15. Adding all fields for processing in DBGrid1

As a result, for visual processing, in DBGrid1 will be added all fields, which corresponds the all fields of component ADOQuery1 (Fig. 16).

Fig. 16. Fields, which you can process to DBGrid1

The object-variable corresponds to every field. For example, for field “0-Students.Name” is corresponding object “DBGrid1.Columnsj[0]”, for field “1 – Marks.Mathematics” is corresponding object with name “DBGrid1.Columns[1]” and so on.

In the fields editor of component DBGrid1 you can do the set of operations for better displaying of data:

– set the order of fields (columns) with the help of mouse;

– set the alignment of text and titles of fields of table, which is displayed in DBGrid1;

– set the different fonts to displaying data and set the color of fonts;

– set the width of each field and so on.