存储过程遇到难题,问问大家
我原来的代码,是一个asp程式
set cn = Server.CreateObject("ADODB.Connection")
set comm = Server.CreateObject("ADODB.Command")
set RcSet = Server.CreateObject("ADODB.RecordSet")
SET CN = GetConnection(gScriptSQL_test)
set comm.ActiveConnection = cn
comm.CommandType = 4
comm.CommandText = "dbo.CE_LIST_PRT" --sp返回一个数据集
RcSet.cursorlocation = adUseClient
set RcSet = comm.Execute
do until RcSet.eof
response.write RcSet("sn") & "<br>"
Rcset.movenext
loop
response.end
1.这是原来的代码,
连接的函数如下:
'=====Create Connection===
Function GetConnection( Param )
Dim conn
On Error Resume Next
Set GetConnection = Nothing
Set conn = Server.CreateObject("ADODB.Connection")
If Err.Number <> 0 Then Exit Function
conn.Open Param
If Err.Number <> 0 Then Exit Function
Set GetConnection = conn
End Function
一直没有问题,不过我的上司看我的代码的时候要我改为和他一样,不要用GetConnection( Param )这个连接方式,要用:
'=====得到SQL之Connection===[GetSQLServerRs使用到]
Function GetSQLServerConnection( Computer, UserID, Password, Db )
Dim Params, conn
Set GetSQLServerConnection = Nothing
Params = "Provider=SQLOLEDB.1"
Params = Params & ";Data Source=" & Computer
Params = Params & ";User ID=" & UserID
Params = Params & ";Password=" & Password
Params = Params & ";Initial Catalog=" & Db
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Params
conn.Execute "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"
Set GetSQLServerConnection = conn
End Function
于是我就修改set cn=GetSQLServerConnection( gSQLServerName, gSQLACCName, gSQLPassword, gSQLDBName)
我原来用的GetConnection(gScriptSQL_test),这个gScriptSQL_test是
gScriptSQL_test="driver={sql server};database=CERT_TEST_DEV;server=minerva;uid=misa;password='*****'"
,我觉得非常奇怪,如果用Provider=SQLOLEDB.1(上司用的这种机制),不是返回数据集就可以,update是可以,执行存储过程也可以,但是执行存储过程返回数据集就是有问题,错误为:
ADODB.Recordset (0x800A0E78)
Operation is not allowed when the object is closed.
---那就是数据集不存在
Provider=SQLOLEDB.1和driver={sql server}这2种不是都是微软提供的机制吗?
Provider=SQLOLEDB.1为什么不能返回我的数据集呢?
真是纳闷m(-_-)m
请大家多多指教!!!(我一直如此用法,从来没有想到换了种连接机制会有问题)