Creating a DataWindow Object
Now you are ready to create a DataWindow object that will communicate with the database. Select File-->New from the main menu. Click the DataWindow tab and you will see a list of DataWindow presentation styles (see Figure 9
Figure 9. DataWindow Presentation Styles: Click the DataWindow tab when creating a new DataWindow displays a list of available presentation styles.
Figure 10. Selecting a Data Source: The dialog lets you choose from among several data source options.
You'll use the tabular DataWindow presentation style first, so click it now. The tabular DataWindow displays data in rows and columns on the page. Note that the DataWindow supports many other presentation styles, including graphs, labels, crosstabs, and grids. Now select the data source for the DataWindow.
Click the icon "SQL SELECT" as the data source (see Figure 10) and then click "Next". The DataWindow designer presents a list of all the tables in the pubs database because you already connected to the database earlier. Remember, the designer displays the tables of the current database connection. For this application, you're using only one database, but for multiple-database projects, you'll need to select the correct database.
Select the authors table and click Open (see Figure 11).
Figure 11. Selecting Tables: After connecting to a database, you can choose the tables to access for your SELECT queries.
Figure 12. Selecting Columns: After selecting a table, the designer displays a list of columns.
You now see the list of columns along with several tabs at the bottom (see Figure 12
). This is the window from which you select columns and generate the SQL. You can select the Syntax tab at any time to view the generated SQL. Alternatively, if you prefer to type the SQL yourself, you can select Design-->Convert To Syntax from the menu to get an editor that lets you enter the SQL manually. Select the columns in the order you want them to appear on the layout. You could also right-click on the table name and select choose "Select All" from the popup menu.
|Figure 13. Sorting Columns: To sort query data, select the columns you want to sort by, and drag them to the right-hand side of the pane.|
In this case, you want to retrieve the data sorted by last name and first name, so select the columns and drag them to the right side of the lower pane (see Figure 13
Click the syntax tab to view the modified SQL syntax. For this application, you're going to retrieve all the rows in this table, so you don't need a WHERE clause. When the SQL syntax looks OK you've finished the SQL for this section. I'll discuss retrieving one row or a set of rows based on a WHERE clause at the end of this discussion.
Using the DataWindow's Visual Features
|Figure 14. Selecting Colors and Borders: From the DataWindow Painter, you can select colors and borders to display.|
From the File menu, select "Return to DataWindow Painter." Another dialog appears that lets you select colors and borders (see Figure 14
Click Next to use the default values supplied. A final dialog gives you one last chance to change the settings. Click Finish for now. If you want to modify any portion of the SQL statement at a later time or later date, you can easily do so by returning to the painter.
Modifying the DataWindow Object's Visual Properties
At this point, you should be looking at the interface designer, which has three main panels (see Figure 15). You can use the visual layout and properties panel to:
- Resize columns, change fonts and rename the header.
- Create controls on the screen.
- Disable the primary key column except for new rows.
- Show a conditional bitmap.
- Change the color of a column.
- Add a footer line to calculate the total number of rows in the DataWindow.
- Set the update properties that will generate a SQL UPDATE statement to the database.
I'll briefly describe each of these operations in the following sections.
Resizing Columns, Changing Fonts and Renaming Headers
|Figure 15. Interface Designer: The designer has three main panels, from which you control the DataWindow layout, change headers, resize columns, and change fonts.|
Click on the header band and the column called au_lname
. The column headers take their default names from the column names, but you can change the displayed column names. Using the properties panel, change the text to "Last Name" (see Figure 15
Click on the Alignment control and set it so the header is left aligned. Alternatively, you can change the text using the toolbar. Go to the header band and shrink the width to one-half the original size. Next, resize and align the column containing the data. Click on the header band and the Last Name. Hold down the CTRL key, and click the object showing au_lname
in the detail band. Use the toolbar (see Figure 16
) to size both objects to the same width, and then align both to the same left position. Using the right arrow key, move both objects slightly to the right. Use the arrow key on the properties tab and scroll to the right. Change the font to Courier New.
Create a Control on the Screen
|Figure 16. Changing Alignment: Use the Alignment toolbar controls and the arrow keys to align and size objects.
You are going to change the contract column to display a checkbox. This column has two possible values, so a CheckBox is an appropriate control. Scroll to the right of the top panel (in visual layout) and click the object named "Contract" in the detailed band. Use the Properties tab to find the Edit tab. Select CheckBox as the type and fill in the data value on as 1, data value off as 0. You may need to scroll down to see all properties for the checkbox. Scroll through the data view and verify this column is now a CheckBox. Resize the CheckBox appropriately and center it below the header column.