Need help w/Operation is not allowed when the object is closed

[Bigced21]

Here is my code:

OpenConnection()

Dim rs1
strSQL="Exec sp_GetBoilerTotals_ByCounty"
Set rs1=objConn.Execute(strSQL)

'Display current county totals
' Response.Write "<hr width='90%'>"
Response.Write "<table width='90%' align='center' rules='all'>"
Response.Write "<tr align='center'><td colspan='8'><font size=+1><b>Year To Date County Totals for " & Year(Now()) & "</b></font></td></tr>"
Response.Write "<tr bgcolor='#D2DCF2'align='center'><td><b>County</td><td><b>Boilers</td><td><b>Inspections</td><td><b>Pending</td><td><b>Certificates Printed</td><td><b>2nd Notices</td><td><b>State Owned</td><td><b>Paid Fees</td></tr>"

i = 0
Do while NOT rs1.EOF

If i Mod 2 = 0 then
Response.Write "<tr bgcolor='white' align='center'>"
Else
Response.Write "<tr bgcolor='#D2DCF2' align='center'>"
End If

If (Not rs1.EOF) Then
Response.Write "<td>" & rs1("Name") & "</td><td>" & formatNumber(rs1("Boiler_Total"),0) & "</td><td>" & formatNumber(rs1("Inspection_Total"),0) & "</td><td>" & formatNumber(rs1("Pending_Total"),0) & "</td><td>" & formatNumber(rs1("Cert_Printed_Total"),0) & "</td><td>" & formatNumber(rs1("Second_Notice_Total"),0) & "</td><td>" & formatNumber(rs1("State_Owned_Total"),0) & "</td><td>" & formatNumber(rs1("Fees_Paid_Total"),0) & "</td></tr>"
End If

i = i + 1
set rs1 = rs1.MoveNext()
Loop


Response.Write "</tr>"
Response.Write "</table>"

CloseConnection()

 

[ChrisHirst]

and the problem would be ?????



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Bigced21]

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

/boilers/blr_report_county_totals.asp, line 35

Do while NOT rs1.EOF

If i Mod 2 = 0 then
Response.Write "<tr bgcolor='white' align='center'>"
Else
Response.Write "<tr bgcolor='#D2DCF2' align='center'>"
End If

If (Not rs1.EOF) Then
Response.Write "<td>" & rs1("Name") & "</td><td>" & formatNumber(rs1("Boiler_Total"),0) & "</td><td>" & formatNumber(rs1("Inspection_Total"),0) & "</td><td>" & formatNumber(rs1("Pending_Total"),0) & "</td><td>" & formatNumber(rs1("Cert_Printed_Total"),0) & "</td><td>" & formatNumber(rs1("Second_Notice_Total"),0) & "</td><td>" & formatNumber(rs1("State_Owned_Total"),0) & "</td><td>" & formatNumber(rs1("Fees_Paid_Total"),0) & "</td></tr>"
End If

i = i + 1
set rs1 = rs1.MoveNext()
Loop

 

[amorous]

does this work...

i = i + 1
rs1.MoveNext()
Loop

-VJ

 

[Bigced21]

No

 

[amorous]

Try this:

IF rs1.EOF Then

Response.Write "No records found"
Else

Do until rs1.EOF

If i Mod 2 = 0 then
Response.Write "<tr bgcolor='white' align='center'>"
Else
Response.Write "<tr bgcolor='#D2DCF2' align='center'>"
End If

Response.Write "<td>" & rs1("Name") & "</td><td>" & formatNumber(rs1("Boiler_Total"),0) & "</td><td>" & formatNumber(rs1("Inspection_Total"),0) & "</td><td>" & formatNumber(rs1("Pending_Total"),0) & "</td><td>" & formatNumber(rs1("Cert_Printed_Total"),0) & "</td><td>" & formatNumber(rs1("Second_Notice_Total"),0) & "</td><td>" & formatNumber(rs1("State_Owned_Total"),0) & "</td><td>" & formatNumber(rs1("Fees_Paid_Total"),0) & "</td></tr>"

i = i + 1
rs1.MoveNext()
Loop

-VJ

 

[ChrisHirst]

and line 35 would be ???

a little help with your problem would be useful.



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Bigced21]

Nope, still getting the same error

 

[Bigced21]

Line 35 is the Do while not rs1.EOF.

It won't run on my ASP page but works perfectly in Query Analyzer.

this is the error I get:

ADODB.Recordset error '800a0e78'
Operation is not allowed when the object is closed.

Do while NOT rs1.EOF

If i Mod 2 = 0 then
Response.Write "<tr bgcolor='white' align='center'>"
Else
Response.Write "<tr bgcolor='#D2DCF2' align='center'>"
End If

If (Not rs1.EOF) Then
Response.Write "<td>" & rs1("Name") & "</td><td>" & formatNumber(rs1("Boiler_Total"),0) & "</td><td>" & formatNumber(rs1("Inspection_Total"),0) & "</td><td>" & formatNumber(rs1("Pending_Total"),0) & "</td><td>" & formatNumber(rs1("Cert_Printed_Total"),0) & "</td><td>" & formatNumber(rs1("Second_Notice_Total"),0) & "</td><td>" & formatNumber(rs1("State_Owned_Total"),0) & "</td><td>" & formatNumber(rs1("Fees_Paid_Total"),0) & "</td></tr>"
End If

i = i + 1
set rs1 = rs1.MoveNext()
Loop

 

[ChrisHirst]

I don't use MS SQL and stored procedures but,

shouldn't this

strSQL="Exec sp_GetBoilerTotals_ByCounty"
    Set rs1=objConn.Execute(strSQL)

be this

strSQL="sp_GetBoilerTotals_ByCounty"
    Set rs1=objConn.Execute(strSQL)

to return the recordset from the SP rather than execute the SP on the server.



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Bigced21]

no it's right because I have other pages using sp's and they work just fine. I'm probably missing something small b/c i've tried just about everything and I can't come to a conclusion as to why I'm getting this error

 

[Chopstik]

This may be superfluous, but have you tried this:

set rs1 = server.CreateObject("adodb.recordset")
set rs1 = objConn.Execute(strSQL)

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[shaddow]

As i see is OpenConnection should have been without () since it's not returning nothing (all function should be caled without () if they are not used in in the right of = or an operand.

try this way

'declare the conn obj outside if not you will lose it after getting out of function OpenConnection
dim objConn
OpenConnection
'code here
CloseConnection

sub OpenConnection
objConn.Open ConnStringHere
end sub

sub CloseConnection
'close object only if it's open
if objConn.State=1 then objConn.Close
end sub

________
George, M
Searches(faq333-4906),Carts(faq333-4911)

 

[Bigced21]

Chopstick & shaddow neither one of those worked. I'm hella stumped!!!!!!

 

[ChrisHirst]

logically it has to be something with that particular SP not returning a recordset.
Try hardcoding the code of the SP query into the SQL string and see if that gives the same error.

Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Bigced21]

although it runs in query analyzer????

 

[Chopstik]

I've often had stuff that worked in QA but failed in ASP because of something stupid that I was doing, like hard-coding or some other such. Before you attempt to do anything with your recordset (since this appears to be where the problem is), first check that you actually have a recordset being returned. Try something like this:

OpenConnection()

    Dim rs1
    strSQL="Exec sp_GetBoilerTotals_ByCounty"
    Set rs1=objConn.Execute(strSQL)
    [COLOR=red]if not rs1 is nothing then
      response.write rs1.recordcount
    else
      response.write "There is no recordset here."
    end if
    response.end 'this will end the server code. [/color]

    'Display current county totals
'    Response.Write "<hr width='90%'>"
    Response.Write "<table width='90%' align='center' rules='all'>"
        Response.Write "<tr align='center'><td colspan='8'><font size=+1><b>Year To Date County Totals for " & Year(Now()) & "</b></font></td></tr>"
        Response.Write "<tr bgcolor='#D2DCF2'align='center'><td><b>County</td>
<td><b>Boilers</td><td><b>Inspections</td><td><b>Pending</td><td><b>Certificates Printed</td>
<td><b>2nd Notices</td><td><b>State Owned</td><td><b>Paid Fees</td></tr>"

    i = 0
    Do while NOT rs1.EOF
        
        If i Mod 2 = 0 then
          Response.Write "<tr bgcolor='white' align='center'>"
        Else 
          Response.Write "<tr bgcolor='#D2DCF2' align='center'>"
        End If
    
        If (Not rs1.EOF) Then
            Response.Write "<td>" & rs1("Name") & "</td><td>" & formatNumber(rs1("Boiler_Total"),0) & "</td><td>" & formatNumber(rs1("Inspection_Total"),0) & "</td><td>" & formatNumber(rs1("Pending_Total"),0) & "</td><td>" & formatNumber(rs1("Cert_Printed_Total"),0) & "</td><td>" & formatNumber(rs1("Second_Notice_Total"),0) & "</td><td>" & formatNumber(rs1("State_Owned_Total"),0) & "</td><td>" & formatNumber(rs1("Fees_Paid_Total"),0) & "</td></tr>"
        End If

    i = i + 1
    set rs1 = rs1.MoveNext()
    Loop
        
        
    Response.Write "</tr>"
    Response.Write "</table>"    

    CloseConnection()

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[ChrisHirst]

it's a fault finding exercise. so each potential issue needs to be eliminated. If the problem remains then you move on to the next point.

as I said earlier I don't use SPs but fault finding is fault finding. it's detective work.

"When you have eliminated the impossible, whatever remains, however improbable, must be the truth" Sherlock Holmes



Chris.

Indifference will be the downfall of mankind, but who cares?

 

[Chopstik]

Chris, I thought that was Spock? ;-)

Bigced21, I meant to point out that based on your previous posts, I am not really certain exactly which line is hitting an error, so this is like Chris said, try to find exactly where the problem lies and then to troubleshoot from there.

------------------------------------------------------------------------------------------------------------------------
"The major difference between a thing that might go wrong and a thing that cannot possibly go wrong is that when a thing that cannot possibly go wrong goes wrong it usually turns out to be impossible to get at or repair."
--Dou

 

[shaddow]

Ehehe or maybe Tuvac from Voyager .
Anyway if you still get the error on the first post then somehow your connection object it's not opened
try an Response.Write objConn.State

________
George, M
Searches(faq333-4906),Carts(faq333-4911)