CODEWITHSUNDEEP
sqlsql-serverstored-proceduresasp-classic
Jonathan Michiels
Jonathan Michiels

Reputation: 27

ASP Stored Procedure Operation is not allowed when the object is closed

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

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 :-)

Upvotes: 0

Views: 2845

Answers (3)

SylvainL
SylvainL

Reputation: 3938

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

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

Upvotes: 1

jaczjill
jaczjill

Reputation: 334

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

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

Upvotes: 0

podiluska
podiluska

Reputation: 51494

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

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

Upvotes: 1

Related Questions