当对象关闭时，不允许使用ASP存储过程操作。

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

我从SQL数据库中通过ASP访问存储过程有问题。这是我的记录集的代码:

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

EDIT

编辑

I found the solution.

我找到了解决方案。

After:

后:

set rs_AntwoordenPerVraag = com_AntwoordenPerVraag.Execute

I put:

我把:

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

And now it works :-)

现在它起作用了:-)

3 个解决方案

#1

Your command needs a connection object, and it needs to be opened, rather than just a connection string.

您的命令需要一个连接对象，它需要被打开，而不是一个连接字符串。

See http://support.microsoft.com/kb/300382

参见http://support.microsoft.com/kb/300382

Additionally, your code will be clearer if you import the ADODB constants file, and use those (ie: http://www.4guysfromrolla.com/webtech/faq/Beginner/faq7.shtml )

此外，如果您导入ADODB常量文件并使用它们(例如:http://www.4guysfromrolla.com/webtech/faq/beginner/faq/beginner/faq/beginner/faq7.shtml)，您的代码将会更加清晰。

#2

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.

您调用存储过程的问题是，在最终结果集之前，由SQL-Server返回的每个查询所涉及的行数。您可以查找下一个记录集，就像您已经找到的那样，或者您可以在SP开始时添加以下指令，以消除每个查询的行数的发送。

SET NOCOUNT ON

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

我更喜欢这个最后的解决方案，因为它使VBScript代码更简单，但它只是一个味道的问题。

#3

OP itself have found the solution and it works! Really Thanks to him.

OP本身已经找到了解决方案，而且很有效!真的感谢他。

After:

后:

set rs_AntwoordenPerVraag = com_AntwoordenPerVraag.Execute

put:

把:

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