ASP串連sql server執行個體解析

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