active|server| access to SQL Server
Applicable to
Profile
This article describes how to use ActiveX Data Objects (ADO) to establish a connection to SQL Server in an ASP script, while leveraging the connection pooling capabilities of ODBC 3.0.
More information
Connection pool
Enable ODBC connection pooling.
For additional information about connection pooling and instructions on how to enable the connection pooling feature, click the following article number to view the article in the Microsoft Knowledge Base:
164221 How to enable connection pooling in an ODBC application
ODBC DSN
Use the ODBC administrator to create a system data source name (DSN) on the computer where Microsoft Internet information Server (IIS) is installed. Specify the connection properties one at a time, and then reuse this property on each page. For example, in the Session_OnStart event for a Global.asa file, the connection property is defined as:
Session ("ConnectionString") = "dsn=sqlsysdsn;uid=<username>;" & _ "Pwd=<strong password>;D atabase= Pubs App=asp Script "
Ensure that all of the following conditions are true:
The Trusted connection box is not selected in the system DSN definition.
SQL Server security mode is not "Windows NT integration."
The UID is not empty in the connection properties.
Otherwise, the connection to SQL Server may fail, and you 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
Whether to use the Global.asa file is optional. In its place, a project typically created from this file can be placed on the first page of the application invocation. Assuming that the ASP script is in a folder that is not defined as a virtual directory in Internet Service Manager, but is located under another virtual directory, the Global.asa file containing the session variable and the DSN definition must remain in this virtual directory. Otherwise, you receive the following error message:
Microsoft OLE DB Provider for ODBC Drivers error ' 80004005 '
[Microsoft] [ODBC Driver Manager] Data source name not found and no
Default driver specified
Connections in ASP Scripts
Take advantage of connection pooling by opening and closing a connection to a database on each Active Server Page. To open this connection, type the following statement in the <Body> section of the page:
<% Set objdbconnection = Server.CreateObject ("ADODB. Connection ") OBJdbConnection.Open session (" ConnectionString ")%>
To turn off this connection, place the following in the </Body> tag:
<% objdbconnection.close Set objdbconnection = Nothing%>
If you do not define the connection settings correctly as described earlier in this 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 a sample application that contains Global.asa and authors.asp files. This sample application will return four columns and all records in the pubs table named authors.
Note: You must first change the UID < username > and pwd =< strong password > to the correct value before you can run this code. Make sure that the UID has the appropriate permissions required to perform this operation in the database.
Global.asa
<script Language=vbscript runat=server> Sub Session_OnStart session ("ConnectionString") = "DSN=SQLSYSDSN;" uid=<username>; Pwd=<strong password>; "& _" Database=pubs; App=asp Script "session (" ConnectionTimeout ") = Session (" CommandTimeout ") = The end Sub
Sub Session_OnEnd
End Sub </SCRIPT>
Authors.asp
<HTML> <HEAD> <title>all authors</title> </HEAD> <body bgcolor= "#FFFFFF" >
<% Set objdbconnection = Server.CreateObject ("ADODB. Connection ") Objdbconnection.connectiontimeout = Session (" ConnectionTimeout ") Objdbconnection.commandtimeout = Session ("CommandTimeout") OBJdbConnection.Open the session ("ConnectionString") Set sqlstmt = Server.CreateObject ("ADODB.") Command ") Set RS = Server.CreateObject (" ADODB. Recordset ")%>
<p> <table border= "0" bordercolor= "#000000" > <% sqlstmt.commandtext = "SELECT * FROM authors" Sqlstmt.comm Andtype = 1 Set sqlstmt.activeconnection = OBJdbConnection RS. Open sqlstmt
Do but not RS. EOF%> <TR> <td Width = align=left> <font size=+1> <%= RS ("au_id")%> </font></td& Gt <TD></TD> <td Width = align=left> <font size=+1> <%= RS ("au_lname")%> </font>< ;/td> <td Width = align=left> <font size=+1> <%= RS ("au_fname")%> </FONT></TD> <t D Width = align=left> <font size=+1> <%= RS ("Phone")%> </FONT></TD> </TR> <% rs.m Ovenext Loop%> </table> For more information about Active Server Pages, see the Path Map (roadmap) provided by ASP Setup on the IIS server.
Reference
For more information about Microsoft Visual Basic. NET encoding Technology, visit the following Web site:
Http://www.microsoft.com/mspress/books/4909.asp
Coding techniques for Microsoft Visual Basic. NET (Microsoft Visual Basic. NET coding Technology)
Connell, John.
Microsoft Press, 20
