009 – An example of creating of calculating fields in component of type TDBGrid in Delphi

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“.

01_02_00_009_table01e

Table “Groups“.

01_02_00_009_table02e

Table “Marks“.

01_02_00_009_table03e

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

01_02_00_009_01_

Fig. 1. The scheme of interconnection between tables

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

01_02_00_009_table04e

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

 

Progress

  1. Run Delphi.

Save the project.

  1. 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).

01_02_00_009_02_

 Fig. 2. Components TADOConnection and TADOQuery

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

01_02_00_009_03_

Fig. 3. Component TDataSource

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

01_02_00_009_04_

Fig. 4. Component TDBGrid from tab “Data Controls

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

01_02_00_009_05_

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.

 

  1. 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.

 

  1. 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).

01_02_00_009_06_

Fig. 6. Property SQL of component ADOQuery1

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

01_02_00_009_07_

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).

01_02_00_009_08_

Fig. 8. Editor “String List Editor

 

  1. 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).

01_02_00_009_09_

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.

 

  1. 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).

01_02_00_009_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).

01_02_00_009_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.

01_02_00_009_12_

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).

01_02_00_009_13_

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

 Close the editor of fields of component ADOQuery1.

 

  1. 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.

01_02_00_009_14_

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”.

01_02_00_009_15_

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).

01_02_00_009_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.