Load DB Data Based on User Selection
The DB records of car models will be displayed in a table, with only those models being loaded and displayed that belong to the manufacturer chosen by the end user (in the combo box). See the previous section for details of how this is set up. In this section, first, a table is created to display the records, then the selection of records to load into the $DB1 tree and display is defined.
Table for displaying records
In the DB, each record (or row) corresponds to a different car model. So the best approach for displaying these records would be to add a table with repeating rows to the design, where each table row corresponds to a DB row. The table should have three columns, one each for the car manufacturer, the model, and the horsepower of the car, and also a header row (see screenshot below). When the user changes the selection in the combo box, then car models of the new manufacturer should be loaded and displayed.
To insert a table that has the properties described above, add a table control with the same specifications as those shown in the screenshot below.
Inside the table, do the following:
•Key the repeating row of the table to the DB row. Do this by dragging the element $DB1/DB/RowSet/Row onto the repeating row icon of the table. This specifies that for each (record) row in the $DB1 tree, there will be a corresponding row in the table.
•Drag-and-drop label controls into each of the three header cells and give them suitable text, corresponding to the three column headers (see screenshot below).
•For the contents of the three columns, drag and drop, respectively, the following attribute nodes of the Row element from the Page Sources pane, and create them as label controls: Manufacturer, Model, Horsepower (see screenshot below).
When you have finished, the table in the design should look something like this:
Select the DB records to load and display
The table that you have just created will display all the records that are loaded from the DB. And the way the $DB1 tree is currently defined, all the records in the DB will be loaded—that is, all the car models of all the manufacturers—and all will be displayed. However, the goal is to load and display only the car models of the manufacturer that the user selects in the combo box.
To load only the car models of the selected manufacturer, create a SELECT statement on the $DB1 tree. Do this as follows:
1.Click the DB icon to the right of the $DB1 tree legend (see screenshot below).
2.In the dialog that appears, click Change to any SELECT.
3.In the Modify SQL SELECT Statement dialog that appears, enter the following SELECT statement: SELECT [Manufacturer], [Model], [Horsepower] FROM Cars WHERE Manufacturer = :Manufacturer. This statement selects—and therefore loads—only the Manufacturer, Model, and Horsepower fields of those records where the Manufacturer field matches the value supplied by the :Manufacturer parameter. Since the SELECT statement contains a parameter (:Manufacturer), a line for the parameter definition will automatically be added to the lower pane of the dialog (see screenshot).
4.Enter the following XPath expression as the definition of the :Manufacturer parameter value: $PERSISTENT/Root/@Manufacturer. This sets the SQL SELECT statement to select those DB records where the Manufacturer field matches the value currently in the $PERSISTENT/Root/@Manufacturer node—which is the user selection.
It is important to note that the SELECT statement that is defined on the $DB1 page source selects what data from the DB to load into the $DB1 tree. This is how the mechanism works:
•As soon as the user changes the value in the combo box, the Reload action of the control's OnFinishEditing event reloads the $DB1 tree (see the definition of the combo box).
•The $DB1 tree is loaded on the basis of a SELECT statement.
•This SELECT statement uses a parameter that has as its value the value of the $PERSISTENT/Root/@Manufacturer attribute, which holds the new user selection. The parameter causes only those DB rows to be selected that have a Manufacturer field value that is the same as the manufacturer which the user selected.
•All the DB rows that have been loaded into the $DB1 tree will be displayed in the table. But since only the rows corresponding to the user selection have been loaded into the $DB1 tree in the first place (see screenshot below), the table will display only the car models corresponding to the user selection.
