📄 0057.htm
字号:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<style>
<!--
body, table {font-size: 9pt; font-family: 宋体}
a {text-decoration:none}
a:hover {color: red;text-decoration:underline}
.1 {background-color: rgb(245,245,245)}
-->
</style>
<title>网络新时代,软件教程:操作系统 主页制作 服务器 设计软件 网络技术 编程语言 文字编辑</title>
</head>
<body>
<p align="center"><script src="../../1.js"></script> </p>
<p align="center"><big><strong>怎样用ASP访问SQL Server</strong></big></p>
<p align="right">---摘自《Microsoft》</p>
<pre>
The information in this article applies to:
Microsoft SQL Server versions 4.2x, 6.0, 6.5
Microsoft Internet Information Server versions 3.0, 4.0
Microsoft SQL Server 2000
SUMMARY
This article describes how to establish connections to SQL Server within an ASP script using ActiveX Data
Objects (ADO), while taking advantage of the connection pooling feature of ODBC 3.0.
MORE INFORMATION
Connection Pooling
Enable ODBC connection pooling. For general information on connection pooling and instructions on how to
enable this feature, see the following article in the Microsoft Knowledge Base:
Q164221 : How to Enable Connection Pooling in an ODBC Application
ODBC DSN
Using the ODBC Administrator, create a System DSN on the computer where Microsoft Internet Information
Server (IIS) is installed. Specify the connection attribute once and reuse it on every page. For example,
in the Session_OnStart event within Global.asa, define the connection attribute as:
Session("ConnectionString") =
"dsn=SQLSysDSN;uid=sa;pwd=;DATABASE=pubs;APP=ASP Script"
Make sure all of the following conditions are true:
The Trusted Connection box is not checked in the System DSN definition.
The SQL Server security mode is not Windows NT Integrated.
Within the connection attribute, the uid is not blank.
Otherwise, a connection to SQL Server may fail with the following 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
Using the Global.asa file is optional. In its place, entries usually made in this file can be put on the
first page called by the application. Assuming the ASP scripts are located in a directory that is not
defined as a virtual directory within the Internet Service Manager, but below another virtual directory,
the Global.asa file containing Session variables and DSN definitions must be kept in the virtual
directory. Otherwise, the following message will be returned:
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 the ASP Script
Take advantage of connection pooling by opening and closing the connection to the database on every active
server page. To open the connection, type the following statements in the <Body> section of the page:
<%
Set OBJdbConnection = Server.CreateObject("ADODB.Connection")
OBJdbConnection.Open Session("ConnectionString")
%>
To close the connection, put the following immediately after the </Body> tag:
<%
OBJdbConnection.Close
Set OBJdbConnection = Nothing
%>
You may encounter the following two error messages if the connection settings are not properly defined as
outlined above:
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver][DBNMPNTW]Connection broken.
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC SQL Server Driver]Communication link failure
Below is a sample application that consists of Global.asa and Authors.asp. This sample application will
return four columns and all records in the pubs table called authors.
Global.asa
<SCRIPT LANGUAGE=VBScript RUNAT=Server>
Sub Session_OnStart
Session("ConnectionString") =
"DSN=SQLSysDSN;UID=sa;PWD=;DATABASE=pubs;APP=ASP script"
Session("ConnectionTimeout") = 15
Session("CommandTimeout") = 30
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 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.CommandType = 1
Set SQLStmt.ActiveConnection = OBJdbConnection
RS.Open SQLStmt
Do While Not RS.EOF
%>
<TR>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("au_id") %>
</FONT></TD>
<TD></TD>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("au_lname") %>
</FONT></TD>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("au_fname") %>
</FONT></TD>
<TD Width = 150 ALIGN=LEFT>
<FONT SIZE=+1>
<%= RS("phone") %>
</FONT></TD>
</TR>
<%
RS.MoveNext
Loop
%>
</table>
<hr>
<p>
</BODY>
<% OBJdbConnection.Close
Set OBJdbConnection = Nothing
%>
</HTML>
For more information on Active Server Pages, refer to the Roadmap provided by the ASP setup program on the
IIS server.
Additional query words: browser explorer web sqlfaqtop
</pre>
<p align="center"><script src="../../2.js"></script> </p>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -