Need Help Working Around 'Divide By Zero' Error From MS-SQL

Need Help Working Around 'Divide By Zero' Error From MS-SQL

Post by Michael Gardne » Wed, 30 Dec 1998 04:00:00



I am using a stored procedure to provide data to an Active Server Page via
ADO.  Many of the fields in the SQL statement in the procedure are actually
calculations (ie PerTot = A/(A+B)).  The statement operates on about 1000
records at a time.  A record with A and B = 0 is valid.  Running the query
produces a 'Divide by zero' error, even though data is returned for the
cases where A+B > 0.  In fact I really don't care about records where A + B
= 0.  The procedure runs fine in the Query Tool with just a warning at the
bottom saying 'divide by zero occured'.

The problem is when the Active Server Page tries to open the recordset no
data is returned an I get an ODBC error Divide by zero.  I tried using 'On
Error Resume Next' to get around it but it didn't work.

Is there a way to ignore the error and access the remaining records that
worked from either the web server creating the ADO or from the SQL server?
Or do I have to go modify my calculations to look something like:

    PerTot = If (A+B > 0 Then A/(A+B) Else 0)

Thanks in advance.

 
 
 

Need Help Working Around 'Divide By Zero' Error From MS-SQL

Post by Greg Bloc » Wed, 30 Dec 1998 04:00:00


Can you not exclude records where A + B = 0, as in :

        SELECT (select list), A/(A+B) AS PerTot
        FROM   (table name)
        WHERE  A + B <> 0

P.S. The reason the ON ERROR RESUME NEXT doesn't work is because the
error occurs inside the bowels of the stored procedure, not in the VB
code.


> I am using a stored procedure to provide data to an Active Server Page via
> ADO.  Many of the fields in the SQL statement in the procedure are actually
> calculations (ie PerTot = A/(A+B)).  The statement operates on about 1000
> records at a time.  A record with A and B = 0 is valid.  Running the query
> produces a 'Divide by zero' error, even though data is returned for the
> cases where A+B > 0.  In fact I really don't care about records where A + B
> = 0.  The procedure runs fine in the Query Tool with just a warning at the
> bottom saying 'divide by zero occured'.

> The problem is when the Active Server Page tries to open the recordset no
> data is returned an I get an ODBC error Divide by zero.  I tried using 'On
> Error Resume Next' to get around it but it didn't work.

> Is there a way to ignore the error and access the remaining records that
> worked from either the web server creating the ADO or from the SQL server?
> Or do I have to go modify my calculations to look something like:

>     PerTot = If (A+B > 0 Then A/(A+B) Else 0)

> Thanks in advance.


 
 
 

Need Help Working Around 'Divide By Zero' Error From MS-SQL

Post by Michael Gardne » Thu, 31 Dec 1998 04:00:00


Now why didn't I think of that.  Simplest solutions are always the best.
Great idea. Thanks for the help.

>Can you not exclude records where A + B = 0, as in :

> SELECT (select list), A/(A+B) AS PerTot
> FROM   (table name)
> WHERE  A + B <> 0

>P.S. The reason the ON ERROR RESUME NEXT doesn't work is because the
>error occurs inside the bowels of the stored procedure, not in the VB
>code.