Asp sqlserver executes the stored procedure to return the temporary solution that the operation is not allowed when the record set object is closed. If you have a better way, please tell me. If you want to get the return value, you need to use the Command method.
First of all, there are two types of return values. One is to directly return a value in the stored procedure, just like the function return value of C and VB; the other is to return multiple values, and the variable names to store these values need to be specified in the calling parameters first.
This example needs to handle multiple parameters, input parameters, output parameters, return record sets and a direct return value (is it complete enough?)
The stored procedure is as follows:
Copy the code code as follows:
use pubs
GO
-- Create a stored procedure
create procedure sp_PubsTest
--Define three parameter variables. Note that the third one is specially marked for output.
@au_lname varchar (20),
@intID int,
@intIDOut int OUTPUT
AS
SELECT @intIDOut = @intID + 1
SELECT *
FROM authors
WHERE au_lname LIKE @au_lname + ''%''
--Return a value directly
RETURN @intID + 2
The asp program that calls this stored procedure is as follows:
Copy the code code as follows:
<%@ Language=VBScript %>
<%
Dim CmdSP
DimadoRS
Dim adCmdSPStoredProc
Dim adParamReturnValue
Dim adParaminput
Dim adParamOutput
Dim adInteger
Dim iVal
Dim oVal
Dim adoField
Dim adVarChar
'These values are predefined constants in VB and can be called directly, but they are not predefined in VBScript
adCmdSPStoredProc = 4
adParamReturnValue = 4
adParaminput = 1
adParamOutput = 2
adInteger = 3
adVarChar = 200
iVal = 5
oVal = 3
''Create a command object
set CmdSP = Server.CreateObject(ADODB.Command)
''Create link
CmdSP.ActiveConnection = Driver={SQL Server};server=(local);Uid=sa;Pwd=;Database=Pubs
''Define the command object call name
CmdSP.CommandText = sp_PubsTest
''Set the command call type to be a stored procedure (adCmdSPStoredProc = 4)
CmdSP.CommandType = adCmdSPStoredProc
''Add parameters to the command object
''Define the stored procedure to have a direct return value, and it is an integer. The default value is 4
CmdSP.Parameters.Append CmdSP.CreateParameter(RETURN_VALUE, adInteger, adParamReturnValue, 4)
''Define a character input parameter
CmdSP.Parameters.Append CmdSP.CreateParameter(@au_lname, adVarChar, adParaminput, 20, M)
''Define an integer input parameter
CmdSP.Parameters.Append CmdSP.CreateParameter(@intID, adInteger, adParamInput, , iVal)
''Define an integer output parameter
CmdSP.Parameters.Append CmdSP.CreateParameter(@intIDOut, adInteger, adParamOutput, oVal)
''Run the stored procedure and get the returned recordset
Set adoRS = CmdSP.Execute
''Print out each record. The fields are virtual and can be ignored.
While Not adoRS.EOF
for each adoField in adoRS.Fields
Response.Write adoField.Name & = & adoField.Value & <br> & vbCRLF
Next
Response.Write<br>
adoRS.MoveNext
Wend
''Print two output values:
Response.Write <p>@intIDOut = & CmdSP.Parameters(@intIDOut).Value & </p>
Response.Write <p>Return value = & CmdSP.Parameters(RETURN_VALUE).Value & </p>
''Cleaning
Set adoRS = nothing
Set CmdSP.ActiveConnection = nothing
Set CmdSP = nothing
%>
When using asp to call the stored procedure and return the record set, it keeps reporting the error that the operation is not allowed when the object is closed. I searched a lot of questions, but I couldn't find the right solution. I found a piece of more reliable code.
Finally, I found that the problem occurred in the stored procedure. There was no problem with the stored procedure in the example, but when I used my own stored procedure, an error occurred.
The final solution is to put it in the stored procedure I use
Before each Insert statement, add a set nocount on sentence, that is, add a sentence inside the cursor loop.
I haven’t delved into the specific reasons yet. Anyone who knows can point it out. Thank you.