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

 

[Bigced21]

Ok, something new I added to my sp - I added SET ANSI_WARNINGS OFF
and I received a new error:
Microsoft VBScript runtime error '800a000d'
Type mismatch: 'formatNumber'

/boilers/blr_report_county_totals.asp, line 44

Here is line 44(look for >>>>>)

i = 0
Do while NOT rs.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 rs.EOF) Then
LINE 44 >>>>>>> Response.Write "<td>" & rs("Name") & "</td><td>" & formatNumber(rs("Boiler_Total"),0) & "</td><td>" & formatNumber(rs("Inspection_Total"),0) & "</td><td>" & formatNumber(rs("Pending_Total"),0) & "</td><td>" & formatNumber(rs("Cert_Printed_Total"),0) & "</td><td>" & formatNumber(rs("Second_Notice_Total"),0) & "</td><td>" & formatNumber(rs("State_Owned_Total"),0) & "</td><td>" & formatNumber(rs("Fees_Paid_Total"),0) & "</td></tr>"
End If

i = i + 1
rs.MoveNext()
Loop

 

[Chopstik]

The type mismatch error indicates that you are trying to format a non-numeric variable to a number. Either it's null or it has alpha characters in the variable.

------------------------------------------------------------------------------------------------------------------------
"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

 

[Bigced21]

Chopstick your code returned a -1

 

[Bigced21]

chopstick name is non-numeric, and Fees_Paid_total is null
when i run the query analzyer

 

[Chopstik]

To prevent the -1 from being returned and get an accurate recordcount from your recordset, see faq333-4615.

You cannot formatnumber a null field, so you will need to protect against it by converting it to a numeric amount like zero before you attempt to format it. Something like:

if isnull(rs1("Fees_Paid_total")) then
  myVar = 0
else
  myVar = rs1("Fees_Paid_total")
end if

and then using the new variable in your code in place of it.

------------------------------------------------------------------------------------------------------------------------
"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

 

[Bigced21]

CHOPSTICK.....YOU DA MAN!!!!!!!!!!!!!!!
THANKS A BUNCH AS WELL AS THE REST OF YOU HAVE HELPED ME WITH THIS PROBLEM. YOU ALL ARE THE BEST!!!!