SQL Server Database Access with IIS - 19 Mar 2001

Use ASP and ADO to access your database

Most Web applications built for the Internet or corporate intranets and extranets require a database for data storage and retrieval. Microsoft SQL Server is a fantastic database server for Web-based applications. However, IIS administrators might need an introduction to data access as it relates to pages that IIS serves.

Let's look at how to configure a Data Source Name (DSN) and use it with ActiveX Data Objects (ADO) to access a database from an Active Server Pages (ASP) file. To execute the tools, tips, code, and tricks that we show you, you need access to SQL Server 2000 or SQL Server 7.0 and to an IIS 5.0 or IIS 4.0 Web server. (We used SQL Server 2000 and IIS 5.0.)

Using ADO to Access SQL Server Data Dynamically

ADO is a powerful and easy-to-use object model that software developers can use to access data. ADO is the preferred data access interface for using IIS and ASP to develop Web-based applications.

You can use ADO to access many sources of data, not just SQL Server. Microsoft provides ADO as an interface layer to OLE DB and designed ADO to be sufficiently generic to accommodate any OLE DB provider—SQL Server, Microsoft Exchange Server, Active Directory (AD), or another platform entirely. Data providers are components that represent these diverse data sources (e.g., SQL Server databases, indexed sequential files, spreadsheets, document stores, mail files). Providers use a common abstraction called a rowset to expose data uniformly. ADO is powerful and flexible because it can connect to any of these data providers and still expose the same programming model, regardless of the specific features of any given provider.

Creating a DSN

Developers most commonly use ADO with a DSN in ASP files. A DSN is the logical name that ODBC uses to refer to the required database information. IIS uses a DSN to connect to an ODBC data source, such as the PUBS SQL Server database. You must create a DSN on your IIS Web server machine that the ASP file we provide (GetTitles.asp) can use to access data from the PUBS database on your SQL Server machine (which will most likely be on a different machine).

To create a DSN that connects to your SQL Server PUBS database, in Windows NT, access the Control Panel ODBC Data Sources applet; in Windows 2000, access the Control Panel Administrative Tools applet, then double-click Data Sources (odbc). You're going to create a system DSN, including the services (e.g., IIS) running as a part of the OS, that will be available to all users on the machine.

On the ODBC Data Source Administrator dialog box, click the System DSN tab, which Figure 1 shows, then click Add. To create a new data source, you must first identify the type of data to which you want to connect. In this case, it's SQL Server, so scroll to the bottom of the driver list and select that option, as Figure 2 shows. Click Finish to proceed with the New Data Source to SQL Server Wizard.

Now, name your DSN pubs. (Naming your DSN after the SQL Server database it's attaching to alleviates confusion on IIS machines that have numerous DSNs with connectivity to numerous SQL Server machines.) Next, add a description of the data source. We chose the name System DSN for the PUBS database, as Figure 3 shows. Finally, chose the name of the SQL Server that you're attaching to. (You might have to type its name.) Click Next.

Now, you choose the authentication credentials for the system DSN that you're creating. The default choice, With Windows NT authentication using the network login ID, uses the credentials of the currently authenticated user. For this example, let's select the With SQL Server authentication using a login ID and password entered by the user option, then type a valid SQL Server login ID and password that have sufficient privileges to read from the PUBS database, as Figure 4, page 11, shows. For this example, we used the all-powerful sa account for the system DSN to authenticate with.

Click Next. Select pubs from the Change the default database to drop-down list, as Figure 5 shows. (Notice that you already have connectivity to the SQL Server database because the wizard has listed all the databases on the SQL Server you've attached to.) Leave the rest of the default choices, then click Next twice. A summary page like the one Figure 6 shows will appear. Click Test Data Source to test connectivity to the SQL Server database.

Using Your System DSN with ASP

You've successfully created a system DSN that you can use in ASP files. Now, download GetTitles.asp and its .inc file, adovbs.inc, from the Code Library on the IIS Administrator Web site. Create a folder called IISAdministrator under inetpubwwwroot, and place GetTitles.asp and adovbs.inc in the folder. Because ASP dynamically generates GetTitles.asp, IIS needs to process it and render it to the connecting browser.

You can use ADO with the VBScript language inside HTML with ASP scripting. For VBScript code to work in an HTML page or in ASP scripting, you must insert the code within a pair of HTML tags or under a scripting section. A scripting section begins with the <% notation and ends with the %> notation. Place the VBScript code between these tags.

VBScript can't load ADO constants from the ADO type library; therefore, unless you include the ADO constants declaration file in VBScript code, you must specify the literal values of ADO constants. Adovbs.inc, which Listing 1 shows, contains all the ADO constant definitions. To use ADO objects in VBScript, you must first include adovbs.inc in your script, then use the CreateObject method to create ADO objects. (Adovbs.inc resides in the program filescommon filessystemado folder on your server, but you can also download it from the Code Library on the IIS Administrator Web site.)

After you've included adovbs.vbs, you can use ADO to retrieve records from the SQL Server database and persist them into a recordset. In GetTitles.asp in Listing 2, you first instantiate an ADO connection object, which callout A in Listing 2 shows, then open the connection with the DSN that you created earlier. By default, the IUSR account doesn't have permission to access the SQL Server database. Therefore, you have to pass in the User ID (UID) and password in the open string. If you want to use only the DSN with no UID and password in the open string, you need to grant each account access in SQL Server to the PUBS database.

Now, look closely at the SQLQuery string, which callout B in Listing 2 shows, that we built in five statements. When you've defined the SELECT statement, retrieving a record set from SQL Server to use in GetTitles.asp is as simple as using the Execute method on the ADO Connection object.

Next, you must build an HTML table and display the field headers, then begin the iteration of the record set. The do while not loop at callout C in Listing 2 loops through the record set until no more records exist, then places the Title, Author's First Name, Author's Last Name, and Price records into the HTML table. Also, notice at callout D in Listing 2 the syntax and method by which you display the data in the HTML table. RSTitleList("title") is the Title field in the record set. To place this value in the table, you use the <%= variable %> notation to place the value of the variable into the HTML table. Therefore, placing <%= RSTitleList("title") %> between the tags places the title in the cell of the HTML table. Figure 7 shows the output from GetTitles.asp.

Just the Tip of the Iceberg

This article has demonstrated how to use ADO with a DSN to use SQL Server data in your ASP files. Keep in mind that we discussed and used only a small part of the feature-rich functionality of ADO. In future articles, we'll show you how IIS can leverage ADO to access other data sources, such as Exchange Server and AD. In addition, we'll show you how to use ADO without a DSN and explain why sometimes that option is better.

Tip: Click Client Configuration, and make sure that your DSN is using TCP/IP as the network library. TCP/IP provides a reliable and fast access to SQL Server.

Note: Using an administrator account in the open string is bad practice because someone could read the source code and gain access to the SQL Server machine. Instead, use an account that can only access the necessary tables and stored procedures in the specific database (in this case, PUBS).