How to access SQL Server in Active Server Page

zhaozj2021-02-16 97

Suitable

summary

This article describes how to use "ActiveX Data Object" (ADO) to create a connection to SQL Server in the ASP script while utilizing the connection pool function of ODBC 3.0.

More information

The connection pool enables the ODBC connection pool.

For other information about connecting pools, click on how to enable connection pool features, click the article number below to view the article in the Microsoft Knowledge Base:

16422 How to enable connection pool in the ODBC application

ODBC DSN creates a "System" Data Source Name (DSN) on a computer installed in Microsoft Internet Information Server (IIS). Specify the connection attribute at a time, then repeat this property on each page. For example, in the global.asa file

In the session_onstart event, the connection property is defined as:

Session ("Connectionstring") = "DSN = SQLSYSDSN; UID = ;" & _ "PWD = ; database = pubs; app = asp script" Make sure all of the following conditions are satisfied:

The trusted connection box is not selected in the System DSN definition. SQL Server security mode is not "Windows NT Integration". In the connection properties, the UID is not empty. Otherwise, the connection to SQL Server may fail and receive the following error message:

Microsoft OLE DB Provider for ODBC Drivers Error '80004005'

[Microsoft] [ODBC SQL Server Driver] [SQL Server] Login Failed- User: _

Reason: NOT Defined As a Valid User of a Trusted SQL Server Connection.

Global.asa is optional using the Global.asa file. At its location, items typically created by this file can be placed on the first page of the application call. Assume that the ASP script is located in a folder, which is not defined as a virtual directory in "Internet Service Manager", but below another virtual directory, then the Global.asa file containing the session variable and DSN definition must Keep in this virtual directory. Otherwise, the following error message will appear:

Microsoft OLE DB Provider for ODBC Drivers Error '80004005'

[Microsoft] [ODBC Driver Manager] Data Source Name Not Found and NO

Default Driver Specified

The connection in the ASP script utilizes the connection pool by opening and closing the connection of the database on each Active Server page. To open this connection, type the following statement in the section of the page:

<% Set objdbconnection = server.createObject ("adodb.connection" ObjdbConnection.open session ("Connectionstring")%> To close this connection, put the following after the tag:

<% ObjdbConnection.close Set ObjdbConnection = NOTHING%> If you do not correctly define connection settings as you present in the article, you may receive the following two error messages: Microsoft Ole DB Provider for ODBC Drivers Error '80004005'

[Microsoft] [ODBC SQL Server Driver] [DBNMPNTW] Connection Broken.

- or -

Microsoft OLE DB Provider for ODBC Drivers Error '80004005'

[Microsoft] [ODBC SQL Server Driver] Communication Link Failure

Here is an example application that contains Global.asa and Authors.asp files. This sample application will return named

Authors

Four columns and all records in the PUBS table.

Note: You must first change the UID and PWD = to the correct value before you can run this code. Make sure the UID has the appropriate permissions required to do this in the database.

Global.asa