当对象被关闭ASP存储过程不允许操作 [英] ASP Stored Procedure Operation is not allowed when the object is closed

问题描述

我有一个从SQL数据库访问ASP通过存储过程的问题​​。
这是我的code的记录：

I have a problem accessing a Stored Procedure via ASP from an SQL Database. This is my code for the recordset:

Dim com_AntwoordenPerVraag__mem_id
com_AntwoordenPerVraag__mem_id = "0"
If Session("MM_MemberID") <> "" Then
    com_AntwoordenPerVraag__mem_id = Session("MM_MemberID")
End If

Dim com_AntwoordenPerVraag__cat_id
com_AntwoordenPerVraag__cat_id = "0"
If Request.QueryString("cat_id") <> "" Then
    com_AntwoordenPerVraag__cat_id = Request.QueryString("cat_id")
End If

set com_AntwoordenPerVraag = Server.CreateObject("ADODB.Command")
com_AntwoordenPerVraag.ActiveConnection = MM_modular_STRING
com_AntwoordenPerVraag.CommandText = "dbo.spAantal_antwoorden_per_vraag_per_member"
com_AntwoordenPerVraag.Parameters.Append com_AntwoordenPerVraag.CreateParameter("@RETURN_VALUE", 3, 4)
com_AntwoordenPerVraag.Parameters.Append com_AntwoordenPerVraag.CreateParameter("@mem_id", 3, 1,2,com_AntwoordenPerVraag__mem_id)
com_AntwoordenPerVraag.Parameters.Append com_AntwoordenPerVraag.CreateParameter("@cat_id", 3, 1,2,com_AntwoordenPerVraag__cat_id)
com_AntwoordenPerVraag.CommandType = 4
com_AntwoordenPerVraag.CommandTimeout = 0
com_AntwoordenPerVraag.Prepared = true
set rs_AntwoordenPerVraag = com_AntwoordenPerVraag.Execute

rs_AntwoordenPerVraag_numRows = 0

我收到以下错误信息：

I get the following error message:

ADODB.Recordset error '800a0e78'

Operation is not allowed when the object is closed.

我在这里得到的消息：

I get the message here:

If rs_AntwoordenPerVraag.EOF And rs_AntwoordenPerVraag.BOF Then

修改

我找到了解决办法。

在

set rs_AntwoordenPerVraag = com_AntwoordenPerVraag.Execute

我把：

If rs_AntwoordenPerVraag.State <> 1 Then
While rs_AntwoordenPerVraag.State <> 1
Set rs_AntwoordenPerVraag = rs_AntwoordenPerVraag.NextRecordset
Wend
End If

和现在的作品： - ）

And now it works :-)

推荐答案

您已经调用存储过程的问题​​是由通过SQL服务器的最终结果集之前返回关闭的Recordset内的每个查询感动的行数。你可以寻找下一个记录，你已经找到，或者你可以在你的SP的开始，以消除为每个查询的行数的发送添加下面的指令。

The problem that you have calling the stored procedure is the number of lines touched by each query that is returned by SQL-Server inside a closed recordset before the final resultset. You can either look for the next recordset as you have already found or you can add the following instruction at the beginning of your SP in order to eliminate the sending of the number of lines for each query.

SET NOCOUNT ON

我preFER，因为它使VBScript的code简单，但它只是一个品味的问题最后的解决方案。

I prefer this last solution as it make the VBScript code simpler but it's just a matter of taste.

这篇关于当对象被关闭ASP存储过程不允许操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！