学步园

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

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

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

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

在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=False<br /> spt_values行数：2346</p> <p>rpc,1,A ,,<br /> pub,2,A ,,<br /> sub,4,A ,,<br /> dist,8,A ,,<br /> dpub,16,A ,,<br /> rpc out,64,A ,,<br /> data access,128,A ,,<br /> collation compatible,256,A ,,<br /> system,512,A ,,<br /> use remote collation,1024,A ,,<br /> lazy schema validation,2048,A ,,<br /> 。。。。。。。。。。。。。。。</p> <p>serial writes,32,V ,,<br /> read only,4096,V ,0,1<br /> deferred,8192,V ,0,1<br /> 连接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"<br /> spt_values行数：2346<br /> rpc,1,A ,,<br /> pub,2,A ,,<br /> sub,4,A ,,<br /> dist,8,A ,,<br /> dpub,16,A ,,<br /> rpc out,64,A ,,<br /> data access,128,A ,,<br /> collation compatible,256,A ,,<br /> system,512,A ,,<br /> use remote collation,1024,A ,,<br /> lazy schema validation,2048,A ,,<br /> 。。。。。

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

补充，调用存储过程

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

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