Operation is not allowed when the object is closed

rrocket

I am trying send information to the DB and the SP will return a recordset. The problem is that I keep getting this error (ADODB.Recordset error '800a0e78' Operation is not allowed when the object is closed). I saw a bunch of posts using queries, but none using SPs and cannot resolve the issue.

Here is a sample of the code... If more is needed let me know

Expand|Select|Wrap|Line Numbers

 
Dim IsRate    Dim cmd

    Dim rs
 
    Set cmd = createobject("ADODB.Command")

    Set rs = createobject("ADODB.RecordSet")

    with cmd

        .ActiveConnection=gObjConnect

        .CommandType=adCmdStoredProc

        .CommandText="RateList_test"

        .Parameters.Append .CreateParameter("@ZipFrom",adVarChar, adParamInput,6,mZipFr)

        .Parameters.Append .CreateParameter("@ZipTo", adVarChar, adParamInput,6,mZipTo)
 
'........
 
.Parameters.Append .CreateParameter("@RateUsed", adInteger, adParamOutput,,0)

        Set rs = cmd.Execute

        IsRate = cmd.Parameters("@RateUsed")

        response.Write rs.Status

        end with

        set cmd = nothing
 
    Dim sqlCar, RScar, SFlag    

    SweeneyFlag = 0

    if not rs.eof then '-->Fails here

rrocket

If this does not make sense or more info is needed, please let me know.

I guess what I am ultimately asking is how to open the rs connection when the command object is used.

rrocket

Anyone have a clue where I could be going wrong?

jhardman

I don't use sp's as often as I should, but this is the most obvious (and easiest to troubleshoot) way to call it from asp:

Expand|Select|Wrap|Line Numbers

 query = "RateList_test " & firstInput & ", " & secondInput & ", " & nthInput

'response.write query 'for troubleshooting only
 
Set rs = gObjConnect.Execute(query) 'execute sql call

'or this last line could be

'rs.open query, gObjConnect

The big advantage this has is you can print out the query, drop it into your query analyzer and see if it returns anything. When you use an ADODB.command I don't know of any way to print out or analyze the command it sends to the db. Anyway, let me know if this helps.

Jared

jhardman

btw, many of our experts search for unanswered posts, so replying to your own thread probably hurt your chance of getting an answer. Sending a polite PM to one of the forum experts asking them to look into your thread (which you did) is just fine.

Jared, moderator

Operation is not allowed when the object is closed

re: Operation is not allowed when the object is closed

re: Operation is not allowed when the object is closed

re: Operation is not allowed when the object is closed

re: Operation is not allowed when the object is closed

Similar ASP / Active Server Pages bytes

/bytes/development

/bytes/it

/bytes/about