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

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:

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