Get down to the basics of interactive Web design
Active Server Pages (ASP) provides server-side scripting for Microsoft Internet Information Server (IIS) Web servers. Microsoft introduced ASP in IIS 3.0 and carried it forward into IIS 4.0. ASP lets you use any scripting language that follows the ActiveX scripting standard, and it enables flexible and dynamic Web page creation. Typically, ASP combines HTML and embedded VBScript. IIS includes an OLE automation server that executes the VBScript and sends the script's output in HTML form to a browser-based client. ASP scripts execute on the server, so they work with any Web browser because the browser receives only a stream of HTML. Understanding how ADO objects work with ASP, and specifically, how to use ADO objects to retrieve and modify data, can make creating dynamic Web pages a breeze.
How ASP Works
Figure 1 illustrates how ASP combines HTML and ActiveX script to produce dynamic HTML. As you can see, ASP scripting is different from browser-based scripting. With traditional browser-based scripting, the Web server sends an HTML page containing the ActiveX script to the client's browser, which is responsible for executing the script. Client-based scripting places an increased burden on the client and can cause problems if a browser client can't execute a script. An ASP page, conversely, executes on the IIS Web server. While executing the page, the server directly passes the client any HTML or client scripts the ASP page contains. When the server encounters an ASP server script, it executes the script and sends to the client any output the script generates, in HTML form. The browser-based client sees no difference between the HTML stream that an ASP script creates and the HTML stream that a static Web page sends. Thus, ASP's server-side scripting essentially produces Web pages as the scripts execute. (See "ASP Scripting Basics" in the September article index at http://www .sqlmag.com.) Because ASP generates a pure HTML stream, it provides browser independence. And because IIS interprets ASP on the fly, ASP is an ideal mechanism for incorporating the results of interactive database queries in Web pages. Accessing SQL Server via ADO from ASP provides this functionality.
Web clients use HTTP and TCP/IP to communicate with the IIS Web server, either across the Internet or on a local intranet. When the Web client requests an ASP page, the IIS Web server executes the page's ASP scripts. To access a SQL Server database, the ASP script opens a connection to SQL Server with a Connection, Command, or Recordset object, then uses that object to send a data-access request to the server. SQL Server can be running on the same system as the IIS Web server. However, because usually several applications use SQL Server at once, it's sometimes more convenient to install SQL Server on a separate system and make the connection across the local network. After the SQL Server system finishes processing the requests, it sends the results back to the ADO object in the ASP script. IIS then processes the ASP script and sends an HTML stream back to the client. Therefore, a network connection must exist between the IIS Web server and the SQL Server database system. In addition, the Web server must have an OLE DB provider and the ADO runtime DLLs installed.
Using ADO Objects with ASP
Using ADO, your application's first action is to use the Connection, Command, or Recordset object to open a connection to SQL Server. You can use the Connection object to explicitly establish ADO connections, or you can use the Command or Recordset object to make ADO connections dynamically. After establishing the connection, your ASP application can issue the same sort of ADO commands that a standard Visual Basic (VB) application can perform. These commands include executing stored procedures, opening and traversing a recordset, and inserting, updating, and deleting data.
To connect to SQL Server from an ASP page, ADO can use the OLE DB provider for either ODBC or SQL Server. The OLE DB provider for ODBC lets you use the ADO object framework with most existing ODBC drivers. You can use the OLE DB provider for SQL Server only to connect to a SQL Server database. But you can use either of these OLE DB providers with the ADO Connection, Command, and Recordset objects. Listing 1 (written in VBScript) shows how to use the OLE DB provider for ODBC to establish a connection with SQL Server.
The first action in Listing 1 is a declaration of three variables that will contain the SQL Server authentication information and the name of the SQL Server system. Next, the script declares the cn variable, which it will use for the ADO Connection object. After the script declares the working variables, the ASP Request object's Form method assigns values to those variables.
Next, the ASP Server object's CreateObject method creates a new ADO Connection object and assigns that object to the cn variable that was created earlier. The CreateObject method can instantiate COM objects. This example illustrates how to create an instance of an ADODB.Connection object, but you can also use it with other COM object frameworks such as SQL-DMO or Active Directory. (For more information about ASP objects, see the sidebar "The ASP Object Model" on page 36.)
The script then assigns the cn Connection object's ConnectionString property an OLE DB connection string, which lets you establish a connection without a Data Source Name (DSN). Because you didn't specify an OLE DB provider with the PROVIDER keyword, the provider defaults to the OLE DB provider for ODBC. The DRIVER keyword identifies the driver to be used. The SERVER keyword specifies the name of the SQL Server system to connect to. The UID and PWD keywords provide the login information, and the DATABASE keyword sets the default database as Pubs. After you've assigned a connection string to the ConnectionString property, the Connection object's Open method opens a connection to the SQL Server system that the script identifies.
Retrieving Data with the ADO Recordset
You can use ADO to retrieve data with the Recordset object or the Command object. Both these objects can work with an active Connection object, or they can open separate connections. Each time an ADO Recordset or Command object establishes a connection, a new communication session with SQL Server starts. If your application needs to perform multiple operations, it's usually more efficient to use a Connection object to open the connection and then use that Connection object with your other Recordset and Command objects.
Listing 2 illustrates using the ADO Recordset object in an ASP page. The first part of this script begins much like the simple connection example in Listing 1. The script declares, then assigns, the working variables. Then the script creates an ADO Connection object, followed by an ADO Recordset object. Next, the script assigns the connection string to the ADO Connection object and calls the Open method to start the connection to SQL Server. The script sets the ActiveConnection property of the rs Recordset object to the active connection object, cn, and the Recordset object's Open method executes. The first parameter of the Open method contains a simple SQL statement that selects all the columns and rows from the stores table in the Pubs database.
After the script returns the query results, the ASP page creates an HTML table and sizes it to contain six columns. Then the script assigns the column headings. The script uses standard HTML to build all the column headings. Inside the HTML table body, at Callout A in Listing 2, a section of VBScript sets up a Do Until loop. This loop processes the contents of the rs Recordset object. When the script reaches the end of the Recordset object, the rs.EOF property becomes true, and the loop ends.
An embedded ASP script assigns the value of each column based on the column's name in the ADO Recordset object. In this example, the column name that originates from the Stores table of the Pubs database identifies each item in the Fields collection. The next portion of VBScript code performs a MoveNext method to move to the next row in the Recordset object. Then the Loop statement transfers control back to the top of the Do Until loop. When the loop has read the last row in the Recordset, the loop ends, and the rs Recordset and the cn Connection object close. You can see the results of this ASP page in Screen 1.
Prev. page
[1]
2
next page 
