Now, use Windows Explorer to navigate to the location in which the wizard placed the file. Two files (authors1.htm and authors2.htm) appear in that location because each page has a maximum of 15 rows from the SQL Server database and the Authors table had more than 15 rows. Run a browser, and navigate to the HTML file authors1.htm. Because I placed my file on my IIS Web server, I navigate to http://localhost/IISAdministrator/Authors1.htm, as Figure 6 shows.
Tip: A great reference for ADO in its most granular detail is David Sussman, et al., Professional ADO 2.5 Programming (Wrox Press, 2000).
Notice that there is a Next link on the bottom of each page. Note, however, that these links are static HTML files—snapshots of the database at the time you ran the wizard. Now, let's look at how to create dynamic SQL Server-driven Web pages with ActiveX Data Objects (ADO) and ASP.
Accessing SQL Server Data Dynamically
ADO is a powerful and easy-to-use object model for accessing data. ADO is the preferred data-access interface for developing Web-based applications when you're using IIS and ASP. ADO is the application programming interface to many sources of data, not just SQL Server.
Microsoft provides ADO as an interface layer to OLE DB. ADO is generic enough to accommodate any OLE DB provider (e.g., SQL Server, Microsoft Exchange Server, Active Directory—AD). ADO is most commonly used in ASP files in conjunction with a Data Source Name (DSN). A DSN is the logical name that ODBC uses to refer to the information required to access data. IIS uses a DSN for a connection to an ODBC data source, such as the SQL Server Pubs database.
You need to create a DSN on your IIS Web server machine. An ASP file leverages the DSN to access data from the pubs database on your SQL Server machine. To create a DSN in Windows NT 4.0, double-click the Control Panel Data Sources (odbc) applet. In Windows 2000, double-click the Control Panel Administrative Tools applet, then double-click Data Sources (odbc) to bring up the ODBC Data Source Administrator dialog box, which Figure 7 shows.
You want to create a System DSN that will be available to all users on the machine that includes the services running as a part of the OS (i.e., IIS). User and File DSNs are available to the currently logged-on user only. Go to the System DSN tab, then click Add and follow these steps:
Tip: Click Client Configuration to make sure your DSN is using TCP/IP as the network library. TCP/IP provides the fastest and most reliable access to SQL Server and is certainly the only way to make SQL Server connections in firewall and demilitarized zone (DMZ) scenarios.
To create a new data source, first identify the type of data you want to connect to—SQL Server, in this case. On the Create a New Data Source dialog box, scroll to the bottom of the list, and choose SQL Server. Click Finish to proceed with the New Data Source to SQL Server Wizard.
On the next wizard page, name your DSN pubs. Software developers usually name the DSN after the SQL Server database it's attaching to. This convention helps alleviate confusion on IIS machines that have many DSNs connecting to many SQL Server machines.
Add a description of the data source. Software developers usually include the name of the SQL Server machine and the database that the DSN is attaching to. In this case, however, the description is System DSN for the PUBS database.
Choose the name of the SQL Server machine that you're attaching to. You might have to enter its name. Click Next.
Choose the authentication credentials for the System DSN. The default choice, With Windows NT authentication using the network login ID, uses the credentials of the currently authenticated user. IIS certainly can't use this authentication because IIS administrators usually log off the system when they walk away from the machine. In that case, IIS would fail its authentication when it needed to use the System DSN to attach to the SQL Server. Therefore, choose With SQL Server authentication using a login ID and password entered by the user, then type a valid SQL server username and password that has sufficient privileges to read from the pubs database. Because I haven't written this code to run in a production environment, I've used the all-powerful sa account for my System DSN to authenticate with. Click Next.
For the Change the default database to option, choose the pubs database from the drop-down list box. You know that you already have connectivity to the SQL Server machine because the wizard lists all the databases on the SQL Server machine you've attached to. Keep the defaults for the rest of the choices, and click Next to continue.
The next page in the wizard presents options with regard to language, encryption, and logging. Retain the defaults, and click Next to continue to the final page of the wizard, which summarizes all your choices, as Figure 8, page 15, shows. Click Test Data Source to test connectivity to SQL Server.
Great! You've successfully created a System DSN that your developers can use in ASP. Now you can begin to leverage the power of SQL Server in ASP files on your IIS servers. This step is the first and most important one in database communications between IIS and SQL Server. Future articles will dive deeper into the topic and provide alternatives for SQL Server communications with IIS and practical examples of application code.
Prev. page
