Scripting Using DTCs and Data-Bound Controls

Now that we've seen how to script the Data Environment using 100 percent manual coding, let's see how to incorporate the Recordset DTC and data-bound controls to achieve the same results. We'll look at several examples, including simple SQL queries using the Recordset and Grid DTCs. We'll also spend time on more advanced examples that use the Recordset, FormManager, and several other DTCs together.

Writing Database Queries

Besides scripting the DE object, another way to execute a query and display the resulting output on screen is to use a Recordset DTC to perform the query and to use a Grid DTC to display the output. Figure 13-4 shows the output of the AccountCodeGrid.asp file (part of the VI-Bank Web project on the CD-ROM). This file performs the same operation as our earlier example—it queries a database table named Account_Code and presents the resulting records in an HTML table on screen.

Figure 13-4. Output from the AccountCodeGrid.asp page, showing the records in the Account_Code table.

What is the advantage of using the Recordset DTC plus the Grid DTC rather than the pure scripting approach? The Grid DTC can give you some additional functionality—it provides navigation buttons for moving through the recordset. These buttons can be useful, especially for large recordsets that cannot be displayed on a single Web page. You can specify your exact navigation requirements for page or row navigation via the Navigation tab on the Grid Properties dialog box, as shown in Figure 13-5 below. Here you can specify the button captions and the number of records displayed per page. You can also specify the color used for the current row.

Figure 13-5. The Grid Properties dialog box for the Grid DTC allows you to specify various navigation options for your recordset.

Figure 13-6 shows the AccountCodeGrid.asp page. The Recordset DTC uses a data command object named AccountCode as the source for its data. This is the same data command object that we used for our DE script object example earlier in this chapter. This is an example of how data command objects can be reused across many pages within your Web applications.

Figure 13-6. AccountCodeGrid.asp Web page as it appears within the Visual InterDev IDE.

The Data tab of the Grid Properties dialog box is where you specify the fields to be output on your Web page. Figure 13-7 shows the Data tab for the Grid DTC on AccountCodeGrid.asp. The two output fields are the account_id and account_description columns. The headers for these columns have been named Account Code and Account Description. You can see that the Grid DTC is bound to the AccountCode data command object.

Figure 13-7. The Data tab of the Grid Properties dialog box.

As another example of using the Recordset DTC along with a Grid DTC, we'll take a look at the balance.asp page in the VI-Bank Web project. This page displays a user's bank balance for both checking and saving accounts. What is interesting is that the results of the query are displayed via the Grid DTC in an HTML table and also via manual scripting for display on the Web page outside the Grid DTC's HTML table. The Recordset DTC executes a parameterized query as follows:

SELECT Customer.first_name, Customer.last_name,      Account_Code.Account_Description, Account_Balance.balance,     Account_Balance.balance - 100 AS Available  FROM Account_Code  INNER JOIN Account_Balance ON Account_Code.Account_Id =      Account_Balance.account_id  INNER JOIN Customer ON Account_Balance.ssn = Customer.ssn  WHERE (Customer.ssn = ?) 

Since the Recordset DTC is expecting a parameter in order to execute the query, it is marked to not open the recordset at run time. The following code is placed just below the Recordset DTC to set the input parameter and to execute the query:

<% rsAccountBalance.setParameter 0, "123456789" rsAccountBalance.Open() %> 

NOTE

---

You can learn more about the setParameter and Open methods for Recordset objects in Chapter 14.

The customer's first and last names are displayed via the following script code within the balance.asp page:

<% Response.Write "Account Balance for " + _     rsAccountBalance.fields.getValue("First_Name") Response.Write " " + rsAccountBalance.fields.getValue("Last_Name") + _     "<P>" %> 

The remaining columns from the query are displayed in the Grid DTC. These are the Account_Description and Balance columns from the Account_Code and Account_Balance tables. Another column—Available Balance—is a calculated field from the Balance column. Figure 13-8 shows the output from the Balance.asp page. In this example, the page-navigation and row-navigation features available within the Grid DTC were turned off since they are not required.

Figure 13-8. Output from the Balance.asp Web page created using a Recordset DTC and a Grid DTC.

Using the FormManager DTC

In the previous section, we saw how to use the Recordset and Grid DTCs to display the results of SQL queries to the end user. By using the FormManager DTC along with the Recordset and other DTCs, you can provide your users with complete control over their data. This DTC provides the ability to insert, update, and delete records from the database.

As an example, we'll again look at some of the code in the VI-Bank Web project on the companion CD-ROM. The code we'll look at is the Profile.asp page. This page allows a bank customer to update his or her profile information, including address, home and work phone numbers, and e-mail address. The page uses a Recordset DTC to provide the SQL query, a number of Textbox and Button DTCs to display and manipulate the data, and a FormManager DTC to coordinate the various modes and actions available on the page.

This example requires that, given the customer's social security number, the Recordset DTC will query the Customer table for a specific customer and retrieve all the necessary fields. These include fields for the first name, last name, address, city, state, ZIP code, home phone, work phone, and e-mail address of the customer. The following code shows the query:

SELECT first_name, last_name, address_1, address_2,     city, state, zip, home_phone, work_phone, e-mail FROM Customer WHERE ssn = ? 

The customer should be able to click an Edit button and edit all fields except the first name and last name fields. After clicking the Edit button, the customer should have access to the Save and Cancel buttons. A Display button should also be available to enable viewing of the data in display-only mode—this should be the starting mode for the Web page. Figure 13-9 below shows the profile.asp page within the browser.

The starting point for this Web page is a data command object named Customer. This command object contains the SQL code listed above. The next step is to create the profile.asp page. First a Recordset DTC is dragged onto a new ASP Web page and associated with the Customer data command object. Next the Textbox and Button DTCs are dragged onto the page. Each Textbox DTC is associated with one of the fields in the recordset by accessing the Textbox Properties page and selecting the appropriate field from the drop-down selection list. When you lay out the Textbox fields, use the Design tab within Visual InterDev—it's the most efficient way to control the positioning of the elements on the page. An HTML table can be used to align the Textbox fields horizontally.

Figure 13-9. The profile.asp page showing the Account Profile page for the VI-Bank sample Internet Banking application.

The buttons required for this page are Display, Edit, Save, and Cancel. Finally, a FormManager DTC is used to specify the modes and actions for the page. Figure 13-10 shows the profile.asp page in Design view within Visual InterDev.

Figure 13-10. The profile.asp page in Design view within Visual InterDev. An HTML table is used to align the text and Textbox DTCs on the page.

The FormManager DTC is given two modes: Edit and Display. In Display mode, only the Display and Edit buttons are visible on the page and all the fields are grayed out. In Edit mode, the Display, Edit, Save, and Cancel buttons are all visible on the page and the fields, with the exception of the first and last name fields, are enabled and visible.

Table 13-1 shows the actions performed for Display mode. Table 13-2 below shows the actions performed for Edit mode. These actions are specified in the Form Mode tab of the FormManager Properties dialog box.

Table 13-1. The actions performed for the Display mode of the profile.asp page.

Object	Member	Value
Displaybtn	Disabled	True
Displaybtn	Show	()
Editbtn	Disabled	False
Editbtn	Show	()
Savebtn	Disabled	True
Savebtn	Hide	()
Cancelbtn	Disabled	True
Cancelbtn	Hide	()
FirstNametxt,
LastNametxt	Disabled	True
FirstNametxt,
LastNametxt	Show	()
Address1txt,
Address2txt,
Citytxt, Statetxt,
Ziptxt,
HomePhonetxt,
WorkPhonetxt,
Emailtxt	Disabled	True
Address1txt,
Address2txt,
Citytxt, Statetxt,
Ziptxt,
HomePhonetxt,
WorkPhonetxt,
Emailtxt	Show	()

Table 13-2. The actions performed for the Edit mode of the Profile.asp page.

Object	Member	Value
Displaybtn	Disabled	False
Displaybtn	Show	()
Editbtn	Disabled	True
Editbtn	Show	()
Savebtn	Disabled	False
Savebtn	Show	()
Cancelbtn	Disabled	False
Cancelbtn	Show	()
FirstNametxt,
LastNametxt	Disabled	True
FirstNametxt,
LastNametxt	Show	()
Address1txt,
Address2txt,
Citytxt, Statetxt,
Ziptxt,
HomePhonetxt,
WorkPhonetxt,
Emailtxt	Disabled	False
Address1txt,
Address2txt,
Citytxt,
Statetxt,
Ziptxt,
HomePhonetxt,
WorkPhonetxt,
Emailtxt	Show	()

Now that the status of the buttons and Textbox DTCs on the page have been defined for both the Display and Edit modes, the final step is to specify the actions to take when each of the buttons are clicked. To do this, enter data into the Action tab of the FormManager Properties dialog box. Table 13-3 summarizes the actions to take for each button's onclick event.

Table 13-3. The actions performed for the onclick events for the four buttons on the profile.asp page.

Current Mode	Object	Event	Next Mode	Action
Display	Editbtn	Onclick	Edit	N/A
Edit	Displaybtn	Onclick	Display	N/A
Edit	Savebtn	Onclick	Edit	rsProfile.updateRecord()
Edit	Cancelbtn	Onclick	Edit	rsProfile.cancelUpdate()

Figure 13-11 shows the profile.asp page in Edit mode. Compare this figure with Figure 13-9, which shows the same page in Display mode. One way to improve the experience of this Web page for the end user is to change the scripting platform from the server to the client. This way, the buttons react immediately to being clicked, as opposed to having to perform a round-trip (to the server and back) to perform the required action. Changing the scripting platform can be accomplished easily: simply access the Properties dialog box for the ASP page and change the DTC Scripting Platform radio button from Server (ASP) to Client (IE 4.0 DHTML). One drawback to this is that the recordset can be moved to the client side for processing only if your end users are running Microsoft Internet Explorer 4.0 or later. If you do not know or cannot enforce the browser types of your end users, you are probably better off keeping the scripting platform on the server side for breadth of deployment.

Figure 13-11. The profile.asp page showing the Account Profile page for the VI-Bank sample Internet Banking application. The page is shown in Edit mode.

What if you do decide to make the change to the client-side scripting platform? After you have applied the change to your Web page, all the DTCs will inherit the change and will run under the client-scripting platform. Be sure to move any manual code that you have added for the Recordset DTC—or for any of the Textbox DTCs—from the server side to the client side. These objects no longer exist on the server, so you can script them using only client-side Microsoft JScript or Microsoft VBScript (Visual Basic, Scripting Edition).

Another advantage of moving the scripting platform from the server side to the client side is that you can perform validation of user-entered data more easily on the client. In the next chapter, we'll see how this can be accomplished via Recordset events.