Classic ASP Error: Operation is not allowed when t

问题:

I have cruised and implemented code from some of the other responses to this question, but I'm still having no luck. I am still getting the error.

    If ((bReport And bIsDate And CheckPermissions("lotsales")) Or Request.QueryString("report")) Then
    OpenDB
    Dim oRs, sSQL, sSQL2, iCancellations, iSales, sDate, sInitDate, sEndDate, iPhaseID, iPhaseNumber, rowCount

    sInitDate = Request("startDate")
    sEndDate = Request("endDate")
    sSQL = "sp_get_lot_sales_test '" & sInitDate & "', '" & sEndDate & "', " & sPhase & ", '" & sReportView & "'"

    'response.write vbNewLine & "<!-- sql: " & sSQL & "-->" & vbNewLine
    'response.write sSQL
    'response.Flush
    Set oRs = ExecuteCommand(sSQL,1) 
End If

And then here is where the error occurs -

If (oRs.EOF) Then <-- fails here
       Response.Write("<TR><TD ALIGN=""center"">There is no data to report on!</TD></TR>")
    Else
        Do While Not oRs.EOF

As a last resort I am going to go back to the stored procedure and deconstruct it to make sure all is well there. Does anyone have any insight as to why I might be getting the error? I am not issuing a close anywhere.

Here is the ExecuteCommand function -

Function ExecuteCommand(s,i)
    On Error Resume Next
    Set ExecuteCommand = oDBc.Execute(s, , i)
End Function

回答1:

You need a connection object.

set conn = server.CreateObject("adodb.connection")
set oRs = conn.execute(sSql)

回答2:

This may be old, but I frequently come across that error (operation is not allowed when object is closed).

What I do is in the stored procedure, I add the follwing:

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

right below the AS in the procedure.

That's all I do and the problem goes away.

回答3:

I am maintaining some old Classic ASP code for a client, code that we took over from a prior developer, and this bug drove me crazy for 4 hours.

I finally discovered a few PRINT statements in the associated SQL stored procedure, which were there for troubleshooting or checking values but don't actually return rows, yet they caused this to fail:

Set cnContentDB = Server.CreateObject("ADODB.Connection")
cnContentDB2.Open sString

sSQL = "EXEC YourStoredProc"

Set oRS2 = Server.CreateObject("ADODB.Recordset")
oRS2.Open sSQL, cnContentDB

if not oR2.EOF then   'THIS WAS GIVING THE ERROR,
                      'EVEN THOUGH THE STORED PROC ALWAYS RETURNS RECORDS

I removed the Print statements, and the error went away.