An example of displaying the table of database in component TDBGrid
The task
Is given the database “05_01_00_005_db.mdb“, which where created in Microsoft Access.
Database includes three tables Student, Subject and Session.
Structure of tables is the following.
Table “Student“.
Table “Subject“.
Table “Session“.
The tables are interconnected by the following scheme (Fig. 1):
Fig. 1. The interconnection scheme between tables
We need create an application that displays the contents of the tables in such form.
To output the table you need to use component of type TDBGrid.
Data of fields we need to take from tables as shown below.
Progress
Create the project of type “VCL Form Application“.
- Placing components on the form.
From tool palette “Tool Palette” we place on the form such components:
– component of type TLabel for displaying the message (Fig. 2);
– from tab “Data Access” component of type TDataSource (Fig. 3);
– from tab “DBGo” component of type TADOConnection (Fig. 4);
– from tab “DBGo” component of type TADOQuery (Fig. 5);
– from tab “Data Controls” component of type TDBGrid for displaying the table (Fig. 6).
Also, for output data from database is possible to use the component TADOTable from tab DBGo.
In our case component of type TADOQuery is used instead TADOTable, since data in DBGrid are formed from three tables.
Using component TADOTable there is appropriate in the event when you want to display only one table from the database.
In component TADOQuery will be created a SQL-query, which will form one table from three according to problem condition.
Fig. 2. Placing the component of type TLabel at the tool palette
Fig. 3. Placement of component of type TDataSource on the form
Fig. 4. Component TADOConnection
Fig. 5. Component TADOQuery from tab dbGo
Fig. 6. Component TDBGrid from tab dbGo
As a result of placement of above mentioned components on the form, in C++ Builder will be formed objects with the names: Label1, DataSource1, ADOQuery1, ADOConnection1, DBGrid1.
- The interconnection scheme between components.
In our case, the interconnection scheme between components has the view, as shown at figure 7.
Fig. 7. The interconnection scheme between components and database
First step is setting connection between application and database. This process is described here. Process connecting the application and database is look like the wizard.
As a result, the property ConnectionString of component ADOConnection1 will be formed. This property points on the location of database file “05_01_00_005_db.mdb“.
After forming the property ConnectionString, we need to set the property LoginPrompt of component ADOConnection1 to “false” value. It is necessary for access to records of database without password check.
To link other components between themselves you need to do:
– in component ADOQuery1 property Connection=ADOConnection1;
– in component DataSource1 property DataSet=ADOQuery1;
– in component DBGrid1 property DataSource=DataSource1.
- Property SQL of component ADOQuery1.
The next step is the setting of property “SQL” of component ADOQuery1. In this property we need to set the string of SQL-query, which will be formed for displaying of data from database.
In according to problem condition, you can form the table, which includes fields from three tables of database.
We will form SQL-query so as to select all data from all tables. And then we leave only necessary fields in component DBGrid1.
Call the property “SQL” of component ADOQuery1. To do it, click on the button “…” (Fig. 8).
Fig. 8. Property SQL of component ADOQuery1
As a result the window of editor will be opened. In this window you need to type the text of SQL-query. Input the following text:
SELECT * FROM [Session], [Student], [Subject]
Thus, we display all fields from three tables.
Fig. 9. Editor to create SQL-query
Press on the button “OK“.
- Forming the fields in fields editor of component ADOQuery1.
After pressing at “OK” you need to call the window of fields editor of component ADOQuery1. Do the click of mouse right button on the ADOQuery1 and in context menu call the command “Fields Editor…“.
Fig. 10. Calling the editor of fields of component ADOQuery1
As a result the window of editor of fields Form1->ADOQuery1 will be opened (Fig. 11).
Fig. 11. Fields editor of component ADOQuery1
Do the click of mouse right button and in the context menu select command “Add All Fields” (Fig. 12). It means, that we add all fields for processing, which were received from SQL-query.
Fig. 12. Command “Add All Fields“
After selecting of command the window of editor of fields will have view as shown in figure 13.
Fig. 13. The editor of fields with all selected fields of three tables
We form the window of fields editor such, that it will look like as shown in Figure 14. To delete field from list, you need select the command “Delete” from context menu (right mouse button).
Fig. 14. The fields, which are used in according to problem condition
System creates the object for every field in fields editor, which you can edit in Object Inspector.
After that you can close the editor of fields.
- Activation of ADOQuery1.
Set the property Active to “true” in component ADOQuery1. After that, all six fields from ADOQuery1 will be showed in component DBGrid1 (Fig. 15).
Fig. 15. Property “Active” of component ADOQuery1
- Forming DBGrid1.
In component DBGrid1 all fields from ADOQuery1 are displayed.
Now, you can edit the size of the form and component DBGrid1. To do this you need click the right mouse button on DBGrid1 and from context menu (Fig. 16) select “Columns Editor…“.
Fig. 16. Calling the editor “Columns Editor…” of component DBGrid1
As a result, the window “Editing DBGrid1->Columns” will be opened. The work with editor “Columns Editor” is look like the work with editor of fields of component ADOQuery1.
Add all fields by command “Add All Fields” (Fig. 17).
Fig. 17. Command “Add All Fields“
The window of editor will have the view as showed on figure 18.
Fig. 18. The fields, which can be processed in DBGrid1
Now, there is a possibility select the according field and process it appearance (for example, Session, Year).
Also, using the mouse you can change the order of fields. By simply dragging we order the fields thus as shown at the Figure 19. This order of the fields corresponds to the order in the table from the problem condition.
Fig. 19. Order of fields according to problem condition
If select the field Session.Year, then you can see in Object Inspector, that object with name DBGrid1.Columns[0] corresponds to it. Similarly, the field Semester corresponds to the object with name DBGrid1.Columns [1]. Also, with the offset of column’s numbers the objects for other fields are created.
By using this objects, you can edit the appearance of columns (fields), which are displayed in DBGrid1.
For example, you can: set the centered the text of titles, set centered the text of fields, set the alignment data in field and so on.
At the figure 20 you can see how changes the centered of title of component DBGrid1.
Fig. 20. Centering of title
After editing the form and editing fields of DBGrid1, the application form will look like as shown in Figure 21.
Fig. 21. Application form after editing DBGrid1 and form
Now you can run the application.