ASP串連sql server執行個體解析

標籤：http   os   io   strong   檔案   資料   for   ar   

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 ,,。。。。。

 

非常easy吧，你也去試試吧，事實上編碼都是非常簡單的事情。

 

 

補充，調用預存程序

<% ‘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.##‘[email protected]) is null
  begin
  set @sql=‘create table ##‘[email protected]+‘( userid varchar(6))‘
  exec(@sql);
  set @flag = 0
  end
Else
set @flag = 1
GO