DIVIDE BY ZERO ERROR, WHEN RUN FROM ASP PAGE

Here is the scenario, i have a stored procedure, it calculates
commissions and runs fine in SQL. However when the SP is run through
my asp page it will come back with a divide by zero error, but only
for one sales person... I have been through this code a hundred times
and can't find any errors. Any help would be much appreciated.

SP:

ALTER PROCEDURE [dbo].[SP_COMMISSIONS]
@SALESMAN VARCHAR(50),
@ACTION VARCHAR(50)

AS

-- SUM PAID

IF @ACTION='SUMPAID'
SELECT COALESCE(SUM(TOTALPAID),0) AS SUMPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN

-- PAID REPORT

IF @ACTION='PAIDREPORT'
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATECOMPL) AS PDMONTH,
YEAR(DATECOMPL) AS PDYEAR, TOTAL_PROJ,
MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN AND
TOTALPAID!=0
ORDER BY YEAR(DATECOMPL) DESC, MONTH(DATECOMPL) DESC





-- NOT PAID REPORT PROJECTS

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
SELECT A.PERSON, A.NUMBER, A.DESCRIPTIO, MONTH(A.DATECOMPL) AS
PDMONTH, YEAR(A.DATECOMPL) AS PDYEAR, A.TOTAL_PROJ,
ROUND(((A.TOTAL_PROJ-A.TOTALCOST)/A.TOTAL_PROJ*100),0) AS MARGIN,
COALESCE(B.RATE, 0) AS RATE,
(A.TOTAL_PROJ*.035) AS BASEPAID,
(COALESCE(B.RATE, 0)*A.TOTAL_PROJ/100) AS MARGINPAID,
((COALESCE(B.RATE, 0)*A.TOTAL_PROJ/100)+(A.TOTAL_PROJ*.035)) AS
TOTALPAID
INTO #TEMPCOMM
FROM
(SELECT PERSON, NUMBER, DESCRIPTIO, DATECOMPL, (TOTAL_PART
+TOTAL_LBR) AS TOTAL_PROJ, TOTALCOST
FROM PROJMASTSQL
WHERE NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)
AND STATUS='D'
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'
AND TOTAL_PROJ!=0
AND PERSON = @SALESMAN) A
LEFT JOIN
(SELECT MARGIN, RATE
FROM COMMMARGINRATES) B
ON (ROUND(((A.TOTAL_PROJ-A.TOTALCOST)/A.TOTAL_PROJ*100),0)=B.MA RGIN)
ORDER BY A.PERSON, YEAR(A.DATECOMPL) DESC, MONTH(A.DATECOMPL) DESC

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=BASEPAID
WHERE MARGIN<40 AND TOTAL_PROJ<2000
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'
IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET MARGINPAID=0
WHERE MARGIN<40 AND TOTAL_PROJ<2000
AND RIGHT(NUMBER,LEN(NUMBER)-6)NOT LIKE 'SC%'

-- MONITORING

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ
FROM PROJMASTSQL
WHERE STATUS IN ('G')
AND TOTAL_PROJ!=0
AND PERSON=@SALESMAN
AND RIGHT(NUMBER,LEN(NUMBER)-6) NOT IN ('IV', 'FI', 'UI')
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAID=50
WHERE RATE IS NULL

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=50
WHERE RATE IS NULL

-- INSPECTIONS

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ
FROM PROJMASTSQL
WHERE STATUS IN ('G')
AND TOTAL_PROJ!=0
AND PERSON=@SALESMAN
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAID=(TOTAL_PROJ*.07)
WHERE RATE IS NULL
AND BASEPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=(TOTAL_PROJ*.07)
WHERE RATE IS NULL
AND TOTALPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6)='IV'

-- SUB CONTRACT LABOR

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
INSERT INTO #TEMPCOMM (PERSON, NUMBER, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ, MARGIN)
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATEIN) AS PDMONTH,
YEAR(DATEIN) AS PDYEAR, TOTAL_PROJ,
((TOTAL_PROJ-COALESCE(TOTALCOST,0))/TOTAL_PROJ*100) AS MARGIN
FROM PROJMASTSQL
WHERE STATUS IN ('D')
AND PERSON=@SALESMAN
AND TOTAL_PROJ!=0
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND NUMBER NOT IN (SELECT NUMBER FROM COMMLOG)

IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET TOTALPAID=(TOTAL_PROJ*.01)
WHERE RATE IS NULL
AND TOTALPAID IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND MARGIN>16.5


IF @ACTION='NOTPAIDREPORT'OR @ACTION='POSTCOMMISSIONS' OR
@ACTION='SUMNOTPAID'
UPDATE #TEMPCOMM
SET BASEPAID=(TOTAL_PROJ*.01)
WHERE RATE IS NULL
AND RIGHT(NUMBER,LEN(NUMBER)-6) LIKE 'SC%'
AND MARGIN>16.5


-- PULL REPORT DATA

IF @ACTION='NOTPAIDREPORT'
SELECT LTRIM(NUMBER) AS NUMBER, PERSON, DESCRIPTIO, PDMONTH, PDYEAR,
TOTAL_PROJ, MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM #TEMPCOMM
IF @ACTION='NOTPAIDREPORT'
DROP TABLE #TEMPCOMM

-- POST COMMISSIONS
IF @ACTION='POSTCOMMISSIONS'
INSERT INTO COMMLOG (PERSON, NUMBER, DESCRIPTIO, TOTAL_PROJ, MARGIN,
RATE, BASEPAID, MARGINPAID, TOTALPAID, PAIDDATE)
SELECT A.PERSON, A.NUMBER, A.DESCRIPTIO, A.TOTAL_PROJ, A.MARGIN,
A.RATE, A.BASEPAID, A.MARGINPAID, A.TOTALPAID, GETDATE() AS PAIDDATE
FROM #TEMPCOMM A

IF @ACTION='POSTCOMMISSIONS'
SELECT PERSON, NUMBER, DESCRIPTIO, MONTH(DATECOMPL) AS PDMONTH,
YEAR(DATECOMPL) AS PDYEAR, TOTAL_PROJ,
MARGIN, RATE, BASEPAID, MARGINPAID, TOTALPAID
FROM COMMLOG
WHERE PERSON=@SALESMAN AND
TOTALPAID!=0 AND
MONTH(PAIDDATE)=MONTH(GETDATE()) AND
YEAR(PAIDDATE)=YEAR(GETDATE()) AND
DAY(PAIDDATE)=DAY(GETDATE())
ORDER BY YEAR(DATECOMPL) DESC, MONTH(DATECOMPL) DESC

IF @ACTION='POSTCOMMISSIONS'
DROP TABLE #TEMPCOMM


-- SUM NOT PAID

IF @ACTION='SUMNOTPAID'
SELECT COALESCE(SUM(TOTALPAID),0) AS SUMNOTPAID
FROM #TEMPCOMM
WHERE PERSON=@SALESMAN

IF @ACTION='SUMNOTPAID'
DROP TABLE #TEMPCOMM


The ASP PAGE IS:

<%@ Page Language="VB" MasterPageFile="~/MasterPage.master"
AutoEventWireup="false" CodeFile="COMMISSIONS.aspx.vb"
Inherits="Default2" title="Commissions Report" %> ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">

Re: DIVIDE BY ZERO ERROR, WHEN RUN FROM ASP PAGE

norrisinc wrote:
> Here is the scenario, i have a stored procedure, it calculates
> commissions and runs fine in SQL. However when the SP is run through
> my asp page it will come back with a divide by zero error, but only
> for one sales person... I have been through this code a hundred times
> and can't find any errors. Any help would be much appreciated.
>
> SP:
>
> ALTER PROCEDURE [dbo].[SP_COMMISSIONS]
> @SALESMAN VARCHAR(50),
> @ACTION VARCHAR(50)
>
If this procedure runs correctly in Query Analyzer, then the rest of
this procedure code is irrelevant. I assume you have used that specific
salesman when testing in QA and the procedure runs fine ...? Given that,
then the issue is the parameter values being passed from ASP.

>
>
> The ASP PAGE IS:
>
> <%@ Page Language="VB" MasterPageFile="~/MasterPage.master"

Oh no. You do NOT have an ASP page. You have an ASP.Net page ... a
totally different technology! For dotnet questions you need to go to
microsoft.public.dotnet.framework.aspnet.
There are also forums at www.asp.net where you can find a lot of people
to help you.

I would suggest using SQL Profiler to verify that the parameter values
being sent to SQL Server are the ones you expect them to be.

PS. You've shown entirely too much code here. The only relevant portions
are (assuming the procedure runs correctly in QA - if not, you need to
go to sqlserver.programming) the initial part of the ALTER PROCEDURE
which I left unsnipped above, and the page-behind code (server-side)
code from your aspx page. All of that html only serves to glaze people's
eyes over and cause them to skip to the next message. Show them the
VB.Net code only.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: DIVIDE BY ZERO ERROR, WHEN RUN FROM ASP PAGE

Have you run that SalesMan through QA and what is the result? What
values are you passing? Could this sales man have a resulting 0 or
Null value that you are trying to divide by?

My best bet without knowing more is to try using the 'Case' function
in your statements on the colums where you are dividing values. Check
the values to make sure they are not null or 0 before you do the math.

Let me know how the QA results are.

Re: DIVIDE BY ZERO ERROR, WHEN RUN FROM ASP PAGE

nvanhaaster@caitele.com wrote:
> Have you run that SalesMan through QA and what is the result? What
> values are you passing? Could this sales man have a resulting 0 or
> Null value that you are trying to divide by?
>
> My best bet without knowing more is to try using the 'Case' function
> in your statements on the colums where you are dividing values. Check
> the values to make sure they are not null or 0 before you do the math.
>
> Let me know how the QA results are.

You just replied to me and asked the same questions that I asked.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: DIVIDE BY ZERO ERROR, WHEN RUN FROM ASP PAGE

Thanks for the tip on not showing as much code, i was just frustrated
and cut and pasted. I have run SQL Server Profiler and believe that
the error is being caused by one of the parameters in the network
protocol: LPC it shows:

-- network protocol: LPC
set quoted_identifier on
set arithabort off
set numeric_roundabort off
set ansi_warnings on
set ansi_padding on
set ansi_nulls on
set concat_null_yields_null on
set cursor_close_on_commit off
set implicit_transactions off
set language us_english
set dateformat mdy
set datefirst 7
set transaction isolation level read committed

I thought that i had it pinned to the arithabort and or
ansi_warnings. I set these specifically for the database, and it
seemed to work fine, for a short time. then the problem came back. i
have tried so many things at this point i'm out of ideas. does anyone
know of a way to change these login options?

Re: DIVIDE BY ZERO ERROR, WHEN RUN FROM ASP PAGE

norrisinc wrote:
> Thanks for the tip on not showing as much code, i was just frustrated
> and cut and pasted. I have run SQL Server Profiler and believe that
> the error is being caused by one of the parameters in the network
> protocol: LPC it shows:

None of this seems relevant.

>
> -- network protocol: LPC
> set quoted_identifier on
> set arithabort off
> set numeric_roundabort off
> set ansi_warnings on
> set ansi_padding on
> set ansi_nulls on
> set concat_null_yields_null on
> set cursor_close_on_commit off
> set implicit_transactions off
> set language us_english
> set dateformat mdy
> set datefirst 7
> set transaction isolation level read committed
>
> I thought that i had it pinned to the arithabort and or
> ansi_warnings. I set these specifically for the database, and it
> seemed to work fine, for a short time. then the problem came back. i
> have tried so many things at this point i'm out of ideas. does anyone
> know of a way to change these login options?

Wait a minute. Are you saying that the error occurs when you run the
procedure using Query Analyzer? Or does it only occur when it is running
from ASP?

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.