Sam M
|
| Posted: 02/28/2002, 6:34 AM |
|
Good morning guys,
I was handed some code that doesn't look broken, and I was wondering if anyone could tell me why I'm getting these errors or what direction to go to fix them:
Microsoft VBScript runtime error '800a0006'
Overflow
/temp/dpars/new/scorecard.asp, line 709
Which is part of a custom show even which looks like this:
formatNumber((rsRC("Actual") - rsRC("Standard"))-rsRC("Support"),0)
But it doesn't show up every time. Only certain records in the database cause this error.
The other error I have been getting is this:
Microsoft VBScript runtime error '800a000b'
Division by zero
/temp/dpars/new/scorecard.asp, line 626
And 626 is part of another custom event which looks like this:
formatPercent((rsO("EarnAdm") / rsO("ActAdm")),0)
And again, this one only occurs when the numbers it is trying to devide are at or below zero.
I ran the same query in Access's query editor and had no problem. Is there a way to devide below the zero point in ASP?
Any thoughts would be great.
Thanks
Sam M
|
|
|
 |
Ken Hardwick
|
| Posted: 02/28/2002, 7:11 AM |
|
Sam,
/temp/dpars/new/scorecard.asp, line 709
Which is part of a custom show even which looks like this:
formatNumber((rsRC("Actual") - rsRC("Standard"))-rsRC("Support"),0)
Most likely one of your field values are null.
You can use a function in your SQL to keep this from happening.
nz (field, char)
return char if field is null (Access 97 upwards).
nz(fee,"no fee given") returns either the value of the fee or the given text
Select nz(actual,0) as actual, nz(standard,0)...etc
Divide by zero problem..
you may need to do the nz() function on this field also...
if rsO("ActAdm") = 0 then
fldPercent = 0
else
fldPercent =formatPercent((rsO("EarnAdm") / rsO("ActAdm")),0)
end if
Ken Hardwick
Norman,OK
|
|
|
|
Sam
|
| Posted: 02/28/2002, 9:44 AM |
|
I've run into the Nz thing before in Access.
Problem with using this function is that it confuses IIS. Access knows what to do with it, but putting it into your asp code will generate a "variable undefined" error.
Interesting approach though. I will try it.
Thanks again bro.
-Sam
|
|
|
|
Tom
|
| Posted: 02/28/2002, 10:57 AM |
|
I tend to push my more complex queries down to the database and query the query. The queries also run faster since they are already parsed. Perhaps you can create a query with the info you need and just select from that.
fyi
|
|
|
|
Sam
|
| Posted: 03/01/2002, 10:55 AM |
|
Darn,
I thought I was the only one who had figured that one out, lol.
The Nz function though is quite evil though. You're much better off with the IIF statement, which is much easier for your applications to interpret. :)>
|
|
|
|
Ken Hardwick
|
| Posted: 03/01/2002, 11:19 AM |
|
Sam/Tom,
I would also agree with Tom on the use of queries. Most of the work I do at work is against Oracle tables.
And, quite often, create Oracle views (similar to queries in Access).
And, if it a view that I am going to display amount out of or do calculations on,more often than not, I use the NVL function (Nvl(field,0) would convert a null value to zero) so that I can do sums and format the results without first have to check to see if it is null or not.
Of course, each situation dictates how you handle it, it is nice with a forum like this one, that we all can learn about the various options that are available.
|
|
|
|
Sam M
|
| Posted: 03/01/2002, 7:40 PM |
|
Well,
I replaced most of the table calls with queries using the IIF to compensate for null values.
This thing was a real mess. It's an access database that grabs all of it's information from a series of excel spreadsheets. It slows down Access considerably, but it's still faster than downloading a 200mb zip file with the spreadsheets in it. Biggest problem was that the origenal format of the access database the last guy used didn't match the format of the spread sheets. Well... I'm sure you know how that goes.
But it seems to work, so I'm happy.
Ken, thanks for reminding me about nulls. It skipped my mind. Do you know if the Nvl function will work on SQL server 2000? I'm upgrading my personal server this month.
|
|
|
|
Sam M
|
| Posted: 03/01/2002, 7:40 PM |
|
Well,
I replaced most of the table calls with queries using the IIF to compensate for null values.
This thing was a real mess. It's an access database that grabs all of it's information from a series of excel spreadsheets. It slows down Access considerably, but it's still faster than downloading a 200mb zip file with the spreadsheets in it. Biggest problem was that the origenal format of the access database the last guy used didn't match the format of the spread sheets. Well... I'm sure you know how that goes.
But it seems to work, so I'm happy.
Ken, thanks for reminding me about nulls. It skipped my mind. Do you know if the Nvl function will work on SQL server 2000? I'm upgrading my personal server this month.
|
|
|
|
|
