Error Handler - Microsoft VBScript runtime error '800a0006' Overflow 1

[specialist]

Greetings-

I am trying to write an error handler using an If/Then statement in order to prevent the below error
in case the value is zero. The idea here, is to determine the MTTR (Mean Time to Repair) in a table on an ASP page.
I have the formula working, but when I select a start and end date where the end result = zero, I get the following error:

Microsoft VBScript runtime error '800a0006'
Overflow
/test/SQLtest.asp, line 312

Here is the MTTR calculation used in order to display the result. This works perfectly by the way. If no records meet the criteria, I get the above mentioned error.

------------------------
sqlstr = "SELECT datediff(d, BeginingDate2, FinishDate2) AS totalday FROM tblInfo WHERE FinishDate2 BETWEEN '" & StartDate & "' AND '" & EndDate & "' AND Category = 3"
Set qryMTTR_Ala1 = db.Execute(sqlstr)

diffDay = 0
totdiffDay=0

Do While Not qryMTTR_Ala1.EOF
diffDay = qryMTTR_Ala1.fields("totalday") '*** each record's datediff from database ***
totdiffDay = totdiffDay + diffDay '*** accumulates all datediff records ***
qryMTTR_Ala1.MoveNext
Loop

-------------------------
result:
(Line 312) <%response.write round(totdiffDay/qryCA("prnRecs"))%>

I tried the following statment which returned the error:

If qryMTTR_Ala1 >= 1 Then
Value = qryCA("prnRecs")
Else
Value = 0
End If


FYI qryCA("prnRecs")) = Count of records.
Any advice on how to correct this would be great and warmly welcomed. Like I said, the MTTR calculation works very well, only problem is this error if the result is zero. Thank you in advance for any assistance.

Happy Holidays to all,

Mike

 

[DreXor]

i believe the error is a division by zero issue, just wrap the response.write in a conditional where if qryCA("prnRecs") = 0 response something other than the calculated amount.

also another method would be you denoted that if no records meet the criteria the error appears, do a if rs.eof conditional before any calculations and skip the loop cycle and so forth if there's no valid records ( "no matching records found" )


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[specialist]

DreXor-

I tried your first suggestion to no avail, I still get the error.

<%
If qryCA("prnRecs") = 0 Then
response.write "0"
End If
Else
response.write round(totdiffDay/qryCA("prnRecs"))%>

How would I go about writing the second suggestion? I'm kinda new to this!!

Mike

P.S. The error is on this line:
response.write round(totdiffDay/qryCA("prnRecs"))%>

 

[DreXor]

you have your conditional flopped around a little :

If qryCA("prnRecs") = 0 Then
response.write "0"
End If
Else
response.write round(totdiffDay/qryCA("prnRecs"))%>


change it around to
If qryCA("prnRecs") > 0 Then
response.write round(totdiffDay/qryCA("prnRecs"))%>
Else
response.write "Bad Math!"
End If

also might actually be better to change the page around like :

Connection stuff
SQL stuff
Recordset Execute
Validate not EOF on RS ( if not Rs.EOF Then )
Math / loop stuff
Validate values - the non zero stuff discussed
Response out result
Else out RS.EOF validation ( Else )
Response out Error ( " no valid records " )
End If
Clean up

DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!

 

[specialist]

DreXor

Worked like a charm!! Thank you very very much for your help!

If I get stuck again, I know where to go for the answers

I hope you dont mind, i changed the "Bad Math!" to "N/A"

Happy Holidays and thanks again for the direction!

Mike

 

[DreXor]

not a problem, glad it's working


DreX
aKa - Robert
if all else fails, light it on fire and do the happy dance!