Sybase's DataWindow .NET: Way Beyond the .NET DataGrid : Page 4
Sybase's new DataWindow .NET lets you create powerful data-driven applications that provide advanced display and printing capabilities with less programming than ever. If you ever worked with PowerBuilder, you'll know why a .NET version of the DataWindow is such an important addition to your toolset. If not, you'll be surprised at the power of the DataWindow.
by Frank Giannino
Dec 8, 2004
Page 4 of 6
Disable the Primary Key Column Except for New Rows
Figure 17. Disabling Columns: Click on a detail object and the Protect icon to get a Protection dialog where you can enter protection conditions. The code in the figure disables the primary column except for new rows.
Typically, you want to prevent users from changing the primary key column unless they're adding a new row. To do so, click on the detail object named au_id. Go back to the General tab (the first tab) on the property pane. Click the red image next to the label "Protect." Enter the text below (see Figure 17).
Click OK to save your changes. Now, in the displayed form, the column is disabled except in new rows.
Show a Conditional Bitmap
Suppose you wanted add a bitmap to appear if a user selects a state that begins with the letter "C." To do this, you add a calculated column instead of adding a bitmap directly. Both methods work, but computed columns are more powerful and can contain many thingsincluding several columns from the database. For example, you can set the visible property based on a condition if you added a bitmap directly to the form. Use the menu Insert-->Control-->Computed Column to insert a computed column. You could also use the toolbar as shown in figure 18.
Figure 18. Inserting Computed Columns: The "+/=" icon on the toolbar lets you insert a computed column.
Click just to the right of au_lname in the detail band to place the computed column on the form. Enter the following in the expression dialog. The downloadable code contains the redball.jpg image.
bitmap(if( state like 'C%',"redball.jpg",""))
Unfortunately, the image is extremely pixelated. That's because the image is really 14 x 9 pixels, so you need to change the size of the computed column. Click the object in the detail band and navigate to the Position tab. Set the width to 14 and the height to 9. The image should appear as expected.
Change the Color of a Column
You can change display colors based on conditions. Here, you'll set the phone number column value to display in blue if the author has a contract and lives in California. Click on the object phone located in the detail band. Navigate to the Font folder in the Properties window. Click the small red image next to Text Color and enter the following line in the expression dialog.
if(state='CA' and contract =1,
Note: You can change the text color without setting a condition by simply selecting the color from the dropdown list.
Add a Header and Footer
Figure 19. Toolbar Convenience: Available toolbar icons let you add a title, page numbers, a date, and underline column headings.
In the designer, click the header band and slide it down to create another half inch of space. Do the same on the footer band. Now click the first column in the header band (au id) and then choose Edit-->Select-->Select Right to select all the items in the header band. Use the arrow keys or mouse to slide all the labels down. Use the toolbar (or menu) to add a title, page numbers, date and column heading underline (see Figure 19).
The underline tool looks like a straight line. Click on the header line and stretch it to the last column. On the General tab of the Properties window, set the pen width to 3 for the underline. Now add a computed column to the footer. Use the toolbar or the menu Insert-->Control-->Computer Field. Enter the following in the expression box:
"Total Rows: " + String(count( au_id for all ))
Make the column bold using the Properties tab (Font) or the toolbar and resize it until it's large enough to display the whole message. The footer will appear on each page. As you can see, the DataWindow provides powerful display options.
Figure 20. Specifying Update Properties: The figure shows the Specify Update Properties dialog, where you specify key columns, how the DataWindow should update changes to key columns, and which columns to update.
Obviously, one of the most important features of database applications is letting users edit data and save their changes. To do that, you need to tell the DataWindow which columns to update and how to generate the SQL UPDATE statements to save changes.
First, select Rows-->Update Properties from the menu. You'll see three possible options on the dialog in the "Where Clause for Update/Delete" section: Key Columns, Key and Updateable Columns, and Key and Modified Columns (see Figure 20). Table 1 describes how you can determine which option you should select.
Table 1. Three options for determining which type of WHERE clause a DataWindow should generate.
The WHERE clause will contain only the primary key, for example:
Update authors set fieldlist = value where au_id = '123'
All changes made by the current user will overwrite any prior changes made by other users.
Least used of the three options
Key and Updateable Columns
The where clause will contain the primary key and all the columns that are updateable on the screen. This option compares all columns to make sure nothing else changed since the retrieve was done, for example:
UPDATE authors set
fieldlist = value
WHERE au_id = '123'
AND au_lname =
AND au_fname =
The update will return an error message if any other user has updated any data since the DataWindow retrieved the primary key
Most commonly used option
Key and Modified Columns
The where clause will contain the primary key and only those columns that changed via the data entry screen. For example this option generates the statement:
UPDATE authors SET
fieldlist = value WHERE au_id = '123'
AND contractor = 1
(if the only column that changed was contractor and the original value was 1)
Useful if your screen displays non-editable columns and one department owns update responsibilities for specific columns within the same table.
Save the DataWindow object and name it de_authors. To save the DataWindow, use the menu options File-->Save. Name the object de_authors in the Save TemplateXHTM dialog (see Figure 21), as well as in the Save DataWindow (see Figure 22) dialog.
Figure 21. The Save TemplateXHTML Dialog: Be sure to name your template in this dialog, which you reach from the File-->Save menu.
Figure 22. The Save DataWindow Dialog: Name your DataWindow in this dialog, which you reach from the File-->Save menu.