Article ID: 313062 - Last Review: November 1, 2006 - Revision: 7.3

HOW TO: Connect to a Database by Using Active Server Pages in Windows 2000

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.
This article was previously published under Q313062

On This Page

Expand all | Collapse all

SUMMARY

This step-by-step article describes how to connect to a database by using an Active Server Pages (ASP) Web page.

Back to the top

Create a Data Source Name

To connect to a database by using ASP pages, you must first create a Data Source Name (DSN) on the Web server for the type of database to which you want to connect. To do so, use one of the following methods.

For a Database Program

To create a DSN for a database program (such as Microsoft Access):
  1. Log on to the Web server computer as administrator.
  2. Click Start, point to Settings, and then click Control Panel.
  3. Double-click Administrative Tools, and then double-click Data Sources (ODBC).
  4. Click the System DSN tab, and then click Add.
  5. Select the database driver that you want (for example, Microsoft Access Driver (*.mdb), and then click Finish.
  6. In the Data Source Name box, type the name that you want to use when you refer to this DSN in your ASP code. For example, Northwind.
  7. In the Description box, type an optional description for the DSN. For example, Northwind DSN.
  8. Click Select.
  9. In the Select Database dialog box, browse to and select the database that you want. For example, Northwind.mdb.

    NOTE: If the database is not on the Web server, click Network, and then click Browse. Locate the shared network folder that contains the database, and then click OK. Click Finish, and then select the database that you want.
  10. Click OK.
  11. Click Advanced.
  12. If you want to automatically provide login credentials to the database when you use this DSN, type them into the Login name and Password boxes. Click OK.
  13. Click OK, and then click OK.

For a Database Server

To create a DSN for an SQL Server:
  1. Log on to the Web server computer as administrator.
  2. Click Start, point to Settings, and then click Control Panel.
  3. Double-click Administrative Tools, and then double-click Data Sources (ODBC).
  4. Click the System DSN tab, and then click Add.
  5. Select SQL Server, and then click Finish.
  6. In the Name box, type the name that you want to use when you refer to this DSN in your ASP code. For example, Northwind.
  7. In the Description box, type an optional description for the DSN.
  8. In the Server list, do one of the following:
    • Select the name of an SQL Server on the network.
    • Select (local) if the SQL Server that you want is running on the Web server computer.
    • Type a name (alias) for a server that does not appear in the Server list.
  9. Click Next.
  10. Under How should SQL Server verify the authenticity of the login ID, click the authentication method that you want. For example, With Windows NT authentication using the network login ID. Click Next.
  11. Click to select the Change the default database to check box, select the database that you want from the list (for example, Northwind), and then click Next.
  12. Click Finish, click OK, and then click OK.
Back to the top

Create an ASP Script to Connect to the Database by Using the DSN

In your ASP script, create a connection to the database by using the ActiveX Data Objects (ADO) Database Access Component (DAC):
  • Use the Connection ADO object to create a connection to the database.
  • Use the Recordset object to retrieve, update, and delete existing database records.
Back to the top

Example

The following example illustrates how to connect to the Microsoft Access NorthWind sample database by using an ASP script.

NOTE: This example assumes the default installation of Windows 2000 on drive C, as well as the default installation of Microsoft Access along with the NorthWind sample database.

Step 1: Create a DSN

Follow the steps in the For a Database Program section of this article to create a DSN to the Microsoft Access Northwind.mdb sample database.

NOTE: By default, the Northwind.mdb file is located in the C:\Program Files\Microsoft Office\Office\Samples folder.

Step 2: Create an ASP Page

  1. Start Notepad.
  2. In Notepad, type the following code:
    <HTML>
<HEAD><TITLE>ASP Database Connection</TITLE></HEAD>
<BODY BGCOLOR=white>
<H1>Northwind Database Contacts</H1>
<%
Dim Connect, selectSQL, RecSet 
Set Connect = CreateObject ("ADODB.Connection")
Connect.Open "DSN=Northwind" 
selectSQL = "SELECT * FROM Customers" 
Set RecSet = Connect.Execute (selectSQL)
If NOT RecSet.EOF
THEN
DO UNTIL RecSet.EOF
Response.Write RecSet("Companyname") & ", " & RecSet("Contactname") & "<BR><BR>"
RecSet.MoveNext
Loop
End If
RecSet.Close
Connect.Close
Set RecSet = Nothing
Set Connect = Nothing
%>
</BODY></HTML>
  3. On the File menu, click Save As.
  4. In the Save As dialog box, navigate to C:\Inetpub\wwwroot in the Save in list, select All Files in the Save as type list, type database.asp in the File name box, and then click Save.
  5. Quit Notepad.

Step 3: Test the ASP Page

  1. Click Start, and then click Run.
  2. In the Open box, type http://localhost/database.asp, and then click OK. A Web page that displays the NorthWind sample database customer list is displayed in the browser window.
Back to the top

Troubleshooting

If you experience difficulty connecting to a database by using ASP Web pages, verify that you have sufficient permissions to access the database:
  • Verify that the DSN is using an account with sufficient permissions to access the database.
  • If you attempt to connect to a SQL Server over a network, ensure that you use a domain account as your anonymous IIS account.
  • If you attempt to connect to a Microsoft Access database, ensure that the IIS account has Write permissions to the folder in which the database is stored. This is to enable the creation of a temporary file when users access the database.
Back to the top

REFERENCES

For additional information about how to work with ASP pages, click the article numbers below to view the articles in the Microsoft Knowledge Base:
308164  (http://support.microsoft.com/kb/308164/EN-US/ ) HOW TO: Create and Configure ASP Web Applications
165492  (http://support.microsoft.com/kb/165492/EN-US/ ) HOWTO: Use ADO with a Visual Foxpro Database
For additional information about SQL Server, please view the following Microsoft Web site:
http://www.microsoft.com/SQL (http://www.microsoft.com/SQL)
For additional information about Microsoft Scripting technologies, please view the following Microsoft Web sites:
http://msdn.microsoft.com/en-us/library/ms950396.aspx (http://msdn.microsoft.com/en-us/library/ms950396.aspx)
http://msdn.microsoft.com/en-us/default.aspx (http://msdn.microsoft.com/en-us/default.aspx)
For additional information about ODBC, please view the following Microsoft Web site:
http://msdn.microsoft.com/en-us/library/ms710252(VS.85).aspx (http://msdn.microsoft.com/en-us/library/ms710252(VS.85).aspx)
Back to the top
APPLIES TO
  • Microsoft Windows 2000 Server
  • Microsoft Windows 2000 Advanced Server
  • Microsoft Windows 2000 Professional Edition
  • Microsoft Small Business Server 2000 Standard Edition
  • Microsoft Internet Information Services 5.0
Back to the top
Keywords: 
kbenv kbhowto kbhowtomaster KB313062
Back to the top