在线等!很菜的问题!

下面是存储过程, 库是SQL中的Pubs库:


CREATE procedure sp_PubsTest

-- 定义三个参数变量,注意第三个,特别标记是用于输出
@au_lname varchar (20),

@intID int,

@intIDOut int OUTPUT

AS

SELECT @intIDOut = @intID + 1

SELECT *
FROM authors
WHERE au_lname LIKE @au_lname + '%'

--直接返回一个值
RETURN @intID + 2

GO


调用如下:

1@ Language=VBScript 
 1   
 2Dim CmdSP   
 3Dim adoRS   
 4Dim adCmdSPStoredProc   
 5Dim adParamReturnValue   
 6Dim adParaminput   
 7Dim adParamOutput   
 8Dim adInteger   
 9Dim iVal   
10Dim oVal   
11Dim adoField   
12Dim adVarChar   
13adCmdSPStoredProc = 4   
14adParamReturnValue = 4   
15adParaminput = 1   
16adParamOutput = 2   
17adInteger = 3   
18adVarChar = 200   
19iVal = 5   
20oVal = 3   
21Set CmdSP = Server.CreateObject("ADODB.Command")   
22CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=123;Database=Pubs"   
23CmdSP.CommandText = "sp_PubsTest"   
24CmdSP.CommandType = adCmdSPStoredProc   
25CmdSP.Parameters.Append CmdSP.CreateParameter("@RETURN_VALUE", adInteger, adParamReturnValue, 4)   
26CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname", adVarChar, adParaminput, 20, "M")   
27CmdSP.Parameters.Append CmdSP.CreateParameter("@intID", adInteger, adParamInput, , iVal)   
28CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut", adInteger, adParamOutput, oVal)   
29Set adoRS = CmdSP.Execute   
30While Not adoRS.EOF   
31For each adoField in adoRS.Fields   
32Response.Write adoField.Name & "=" & adoField.Value & "

<br/>

1" & vbCRLF   
2Next   
3Response.Write "

<br/>

1"   
2adoRS.MoveNext   
3Wend   
4Response.Write CmdSP.Parameters("@intIDOut").Value   
5Response.Write CmdSP.Parameters("@RETURN_VALUE").Value   
6Set adoRS = nothing   
7Set CmdSP.ActiveConnection = nothing   
8Set CmdSP = nothing   

现在下面这两句输出都没有值!
Response.Write CmdSP.Parameters("@intIDOut").Value
Response.Write CmdSP.Parameters("@RETURN_VALUE").Value
请帮忙!现在没有分了请见谅!!

---------------------------------------------------------------

Set adoRS = CmdSP.Execute
改为
CmdSP.Execute()
---------------------------------------------------------------

1@ Language=VBScript 
 1   
 2Dim CmdSP   
 3Dim adoRS   
 4Dim adCmdSPStoredProc   
 5Dim adParamReturnValue   
 6Dim adParaminput   
 7Dim adParamOutput   
 8Dim adInteger   
 9Dim iVal   
10Dim oVal   
11Dim adoField   
12Dim adVarChar   
13adCmdSPStoredProc = 4   
14adParamReturnValue = 4   
15adParaminput = 1   
16adParamOutput = 2   
17adInteger = 3   
18adVarChar = 200   
19iVal = 5   
20oVal = 3   
21Set CmdSP = Server.CreateObject("ADODB.Command")   
22CmdSP.ActiveConnection = "Driver={SQL Server};server=(local);Uid=sa;Pwd=123;Database=Pubs"   
23CmdSP.CommandText = "sp_PubsTest"   
24CmdSP.CommandType = adCmdSPStoredProc   
25CmdSP.Parameters.Append CmdSP.CreateParameter("@RETURN_VALUE",adInteger,adParamReturnValue,,4)   
26CmdSP.Parameters.Append CmdSP.CreateParameter("@au_lname",adVarChar, adParaminput, 20, "M")   
27CmdSP.Parameters.Append CmdSP.CreateParameter("@intID",adInteger,adParamInput,,iVal)   
28CmdSP.Parameters.Append CmdSP.CreateParameter("@intIDOut",adInteger,adParamOutput,,oVal)   
29Set adoRS = CmdSP.Execute   
30While Not adoRS.EOF   
31For each adoField in adoRS.Fields   
32Response.Write adoField.Name & "=" & adoField.Value & "

<br/>

1" & vbCRLF   
2Next   
3Response.Write "

<br/>

1"   
2adoRS.MoveNext   
3Wend   
4adoRS.close()   
5Set adoRS = nothing '须先关闭才能取到返回值与输出值!   
6Response.Write "

<p>@intIDOut = "&amp; CmdSP.Parameters("@intIDOut").Value &amp; "</p>

1"   
2Response.Write "

<p>Return value = " &amp; CmdSP.Parameters("@RETURN_VALUE").Value &amp; "</p>

1"   
2  
3Set CmdSP.ActiveConnection = nothing   
4Set CmdSP = nothing   

1

Published At
Categories with Web编程
comments powered by Disqus