Downloads
5910.zip

Here's a fast, easy way to create a data-driven Web application

The fastest and easiest way to create a Web application is to use Internet Information Server (IIS), Active Server Pages (ASP), ActiveX Data Objects (ADO), and the new Scripting Object Model (SOM). Then, add Visual InterDev (VID) as the development tool, and you have a powerful set of technologies you can use to build an application.

These tools provide many ways to build an application. To demonstrate one approach for building a data-driven application, I created a simple application that works with the Pubs sample database, which comes with SQL Server. The page I created extracts a list of titles from the database and places them in an HTML table. Then, a user can select a title, and review, edit, and update the data for the title.

You can use ADO and VID to build a powerful application that performs well. Before you start, you need to know about several aspects of creating a high-performance Web application. First, use all the tools at your disposal because tools such as VID make application-building easy and fast. And using ADO directly makes accessing the database easy and fast. Stored procedures also make the entire application run much faster and put less load on the database server.

Let's look at the sample application to see how to use these tools. The first step in building the sample application is to create two stored procedures to handle the database work for your application. I created the stored procedures with Visual Database Tools. The first stored procedure is RetrieveAllTitles, which retrieves all rows from the Titles table:

CREATE Procedure RetrieveAllTitles
AS SELECT * FROM Titles

The second stored procedure, Retrieve-Title, retrieves a single row from the Titles table. This stored procedure is

CREATE Procedure RetrieveTitle
        (
                @title_id char(6)
        )
AS SELECT * FROM titles WHERE title_id = @title_id

The next logical step in building the application is to create script functions to perform the ADO work. You can test these functions as logical blocks of code and then add them to the application. First create a new .asp file, as Listing 1 shows, and open it in the VID Design Editor.

The RetrieveAllTitles function, as Callout A in Listing 1 shows, executes the RetrieveAllTitles stored procedure, which retrieves all the titles in the table and returns the records in an array to create the recordset.

At this point, you can add some dummy code to your application to test the functions. For example, you can use the following code to test the RetrieveAllTitles function by placing it in the Body section of the page:

<%
dim aTitles
        aTitles = RetrieveAllTitles()
for i = 0 to ubound(aTitles)
response.write "TitleID = " &
<%=aTitles(i,0)%> & "<br>"
Next
%>

Now view the page in the browser, and you see a simple list of the title ID for each record.

If the database code is working, you can build the application. First, open the page in the VID Design Editor and add a new HTML table to the page by selecting Insert Table from the Tables menu. Create the new table with two rows, four columns, and a 0 border size. This table positions the data on the page.

The GetRows method of the Recordset object extracts the records from the recordset and places the records into an array. You need to be careful using GetRows because you can easily use too much memory on the server if the query returns a large number of rows. You can pass the array and use it elsewhere in the application after the Recordset object is destroyed in the last line of the function.

Next, just after the first row of the table, insert the code in Callout B in Listing 1. The first row of the table contains the headers for each column in the table. The ASP code in Callout B loops through the array and places each item from the array in the proper location in the table.

The first column in the table builds a hyperlink around the title_id with the anchor tag (<A>) to link the item to TitleDetail.asp. This link lets the user click on a title and drill down into the details of the table. Test the page by viewing it in the browser, and your output will look like the page in Screen 1.

Next, create TitleDetail.asp, and open it in the Source editor. This application uses the PageObject to manage some events. Drag the PageObject from the Toolbox and drop it onto the page after the end of the table. Do not change any of the PageObject's properties.

Now, you need to add the code for any events that drive the application. Open the Script Outline by clicking its tab under the Toolbox. If the Script Outline tab does not appear, press Ctrl+Alt+S. Expand Server Objects and Events, then expand thisPage and double-click the onenter event. This selection adds the onenter event block to your page, as Screen 2 shows.

The onenter event executes each time the page loads. This event lets the developer place page startup code and controls the code's execution when loading the page. Add the code shown in Callout A in Listing 2, page 42. Make sure you add the variable definitions before the start of the onenter event code. Doing this places the variables outside of that code block, which makes them public.

The onenter event code executes the RetrieveTitle function, then uses the returned array to populate the textboxes on the page. The IsArray VBScript function lets you test the array before trying to access the array in your code. This test prevents runtime errors when the array does not contain array data. If RetrieveTitle does not return a row, the return value is not an array, and the code can detect this fact.

Now, add the code to retrieve the detail information as Callout B in Listing 2 shows. The RetrieveTitle function takes a TitleID as a parameter and returns a single title in an array. The RetrieveTitle function executes the RetrieveTitle stored procedure to create the recordset. Next, add the code for the RetrieveTitle function as shown in Callout B in Listing 2.

The next step is to add controls to the page that holds the data, to provide proper user interaction. For the interface, this application uses several new design-time controls from VID 6. The design-time controls rely on SOM and provide a rich scripting model for building applications.

First, add the titles for each field (Title ID, Title, Type, and Price) to the first cell in each row of the table. Then, drag a Textbox design-time control from the Toolbox, and place it in the second cell of the first row. Answer Yes to enable the SOM prompt. Open the textbox properties, and set the Name property to txtTitleID. Set the Width and Maximum width properties to 14, then close the properties.

Next, drag a Textbox design-time control from the Toolbox and place it in the second cell of the second row. Open the textbox properties, and set the Name property to txtTitle. Set the Width and Maximum width properties to 61, then close the properties. Drag another Textbox design-time control from the Toolbox, and place it in the second cell of the third row. Open the textbox properties, and set the Name property to txtType and the Width and Maximum width properties to 14.

Now, drag another Textbox design-time control from the Toolbox, and place it in the second cell of the fourth row. Open the textbox properties, and set the Name property to txtPrice. Set the Width and Maximum width properties to 14, then close the properties.

To complete this page, drag Title.asp from Project Explorer and drop it on the page after the end of the table. This action creates a link from the detail page back to the master page. You can edit the link text by switching to the Source editor and making the change there. When you have completed the changes, view Title.asp and click any Title ID. This selection drills down into the details for that title, and your page will resemble the page in Screen 3.

You can see how easy it is to use IIS (for executing the ASP code), ASP, ADO, and the new SOM in VID 6 to create a simple Web application. You can see how easily the event code shown in Listing 2 isolates the code it contains and triggers its execution. Also, you can see that an SOM event model is designed to take Web development into a new era and makes building and managing applications easier.