ASP连接sql server实例解析

1、首先确定自己的iis没有问题

2、其次确定自己sqlserver没有问题

 

然后在iis的目录wwwroot里，建立一个文件 名为testSqlServer.asp，编写代码如下即可

<%'ole db连接set cnn1 = Server.CreateObject("ADODB.Connection") '连接串需要注意sqlserver的实例名，是否是默认，非默认必须要写出来cnn1.Open "provider=sqloledb;data source=./sqlexpress;initial catalog=master;user id=sa;password=000000;" sql = "select * from master..spt_values"set rs= Server.CreateObject("ADODB.RecordSet")rs.Open sql,cnn1 ,1,1'获得连接信息与查询结果行数Response.write("连接1："&cnn1.ConnectionString&"<br/> spt_values行数："&rs.recordcount&"<br/><br/>")'循环结果输出do While not rs.eofResponse.write(rs(0)&","&rs(1)&","&rs(2)&","&rs(3)&","&rs(4)&"<br/>") rs.MoveNextLooprs.close()cnn1.close()%><%'odbc连接set cnn2 = Server.CreateObject("ADODB.Connection") cnn2.Open "Driver={SQL Server};Server=./sqlexpress;Database=master;UID=sa;PWD=000000;" sql = "select * from master..spt_values"set rs= Server.CreateObject("ADODB.RecordSet")rs.Open sql,cnn2 ,1,1Response.write("连接2："&cnn2.ConnectionString&"<br/> spt_values行数："&rs.recordcount&"<br/>")do While not rs.eofResponse.write(rs(0)&","&rs(1)&","&rs(2)&","&rs(3)&","&rs(4)&"<br/>") rs.MoveNextLooprs.close()cnn2.close()%>

 

在ie地址输入http://localhost/testsqlserver.asp，即可看到如下结果

连接1：Provider=SQLOLEDB.1;Password=000000;User ID=sa;Initial Catalog=master;Data Source=./sqlexpress;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=PC-201003062254;Use Encryption for Data=False;Tag with column collation when possible=Falsespt_values行数：2346rpc,1,A ,,pub,2,A ,,sub,4,A ,,dist,8,A ,,dpub,16,A ,,rpc out,64,A ,,data access,128,A ,,collation compatible,256,A ,,system,512,A ,,use remote collation,1024,A ,,lazy schema validation,2048,A ,,。。。。。。。。。。。。。。。serial writes,32,V ,,read only,4096,V ,0,1deferred,8192,V ,0,1连接2：Provider=MSDASQL.1;Extended Properties="DRIVER=SQL Server;SERVER=./sqlexpress;UID=sa;PWD=000000;APP=Microsoft? Windows? Operating System;WSID=PC-201003062254;DATABASE=master"spt_values行数：2346rpc,1,A ,,pub,2,A ,,sub,4,A ,,dist,8,A ,,dpub,16,A ,,rpc out,64,A ,,data access,128,A ,,collation compatible,256,A ,,system,512,A ,,use remote collation,1024,A ,,lazy schema validation,2048,A ,,。。。。。

 

很容易吧，你也去试试吧，其实编码都是很简单的事情。

 

 

补充，调用存储过程

<% 'ole db连接 set Conn= Server.CreateObject("ADODB.Connection") '连接串需要注意sqlserver的实例名，是否是默认，非默认必须要写出来 Conn.Open "provider=sqloledb;data source=192.168.1.101;initial catalog=master;user id=sa;password=000000;" %><%Const adInteger= 3 Const adVarChar= 200 Const adParamInput= 1 Const adParamOutput= 2 Const adCmdSPStoredProc = 4 Set adoComm = CreateObject("ADODB.Command")With adoComm .ActiveConnection = Conn .CommandType = adCmdSPStoredProc.Prepared = true .CommandText = "sp_checklogin" .Parameters.append .CreateParameter("@userid",adVarChar,1,6,"NO001") '参数名，参数类型，输入输出类型，长度，值 .Parameters.append .CreateParameter("@flag",adInteger,2) '返回参数,返回总记录数,数据长度可以省略..execute End Withflag = adoComm(1)Set adoComm = Nothingresponse.write "执行本程序后，存储过程返回值Flag："&flag&"<br>"if flag=0 then response.write "提示：成功登录！（该帐户今天第一次登录）" elseresponse.write "提示：该帐号已有人登录，不可再登录" end if %><%sql = "select * from master..spt_values" set rs= Server.CreateObject("ADODB.RecordSet") rs.Open sql,Conn ,1,1 '获得连接信息与查询结果行数 Response.write("连接1："&Conn.ConnectionString&"<br/> spt_values行数："&rs.recordcount&"<br/><br/>") '循环结果输出 do While not rs.eof Response.write(rs(0)&","&rs(1)&","&rs(2)&","&rs(3)&","&rs(4)&"<br/>") rs.MoveNext Loop rs.close() 'Conn.close() %>

 

 

SQL存储过程如下：
CREATE procedure sp_checklogin 
@userid varchar(6) , -- 操作帐号
@flag int out -- 输出参数 0:没有登录 1:已经登录
as
declare @sql nvarchar(100)
IF object_id('tempdb.dbo.##'+@userid) is null
  begin
  set @sql='create table ##'+@userid+'( userid varchar(6))'
  exec(@sql);
  set @flag = 0
  end
Else
set @flag = 1
GO