SQL Server Magazine

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
%>

   Prev. page   [1] 2     next page