Webmaster Forum / ASP / Database Access / April 2007

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.MARGIN)
    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" %> <asp:Content
ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1"
Runat="Server">
   <atlas:ScriptManager ID="ScriptManager1" runat="server">
   </atlas:ScriptManager>
   <atlas:UpdatePanel ID="UpdatePanel1" runat="server">
       <ContentTemplate>
           <table style="width: 880px; background-color: #DCDCDC">
               <tr>
                   <td colspan="4" style="font-weight: bold; text-
decoration: underline">
                       Commission Report</td>
               </tr>
               <tr>
                   <td style="width: 10px">
                   </td>
                   <td style="width: 100px">
                       Salesman:</td>
                   <td style="width: 230px">
   <asp:DropDownList ID="DropDownList1" runat="server"
DataSourceID="SALESMEN" DataTextField="PERSON"
       DataValueField="PERSON">
   </asp:DropDownList></td>
                   <td rowspan="4" style="border-right: black 1px
solid; padding-right: 5px; border-top: black 1px solid;
                       padding-left: 5px; font-size: small; padding-
bottom: 5px; border-left: black 1px solid;
                       padding-top: 5px; border-bottom: black 1px
solid; background-color: #ffffff">
                       <span>Instructions:<br />
                           <br />
                           Currently only the Not Paid Report is
functional check back soon for the historical
                           paid report.</span></td>
               </tr>
               <tr>
                   <td style="width: 10px">
                   </td>
                   <td style="width: 100px">
                       Report Type:</td>
                   <td style="width: 230px">
   <asp:RadioButtonList ID="RadioButtonList1" runat="server">
       <asp:ListItem Value="PAIDREPORT">PAID REPORT</asp:ListItem>
       <asp:ListItem Value="NOTPAIDREPORT">NOT PAID REPORT</
asp:ListItem>
   </asp:RadioButtonList></td>
               </tr>
               <tr>
                   <td colspan="1" style="width: 10px">
                   </td>
                   <td colspan="2">
                       <asp:Button ID="Button1" runat="server"
Text="Button" /></td>
               </tr>
               <tr>
                   <td colspan="3" style="text-align: center">
           <atlas:UpdateProgress ID="UpdateProgress1" runat="server">
               <ProgressTemplate>
                   <asp:Image ID="Image1" runat="server" ImageUrl="~/
IMAGES/ajax-loader[1].gif" /><br />
                   <span style="color: #ff0000">Please Wait...</span>
               </ProgressTemplate>
           </atlas:UpdateProgress>

                   </td>
               </tr>
               <tr>
                   <td colspan="4" style="text-align: center">
   <asp:FormView ID="FormView1" runat="server"
BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px"
CellPadding="2" DataSourceID="SUMNOTPAID"
       ForeColor="Black">
       <FooterStyle BackColor="Tan" />
       <EditRowStyle BackColor="DarkSlateBlue"
ForeColor="GhostWhite" />
       <PagerStyle BackColor="PaleGoldenrod"
ForeColor="DarkSlateBlue" HorizontalAlign="Center" />
       <HeaderStyle BackColor="Tan" Font-Bold="True" />
       <ItemTemplate>
           Total:&nbsp;
           <asp:TextBox ID="TextBox1" runat="server" Text='<%#
Bind("SUMNOTPAID", "{0:C}") %>'></asp:TextBox>
       </ItemTemplate>
   </asp:FormView>
                   </td>
               </tr>
           </table>
           <asp:GridView ID="GridView1" runat="server"
BackColor="White" BorderColor="#DEDFDE"
               BorderStyle="None" BorderWidth="1px" CellPadding="4"
DataSourceID="COMMISSIONS"
               ForeColor="Black" GridLines="Vertical"
AutoGenerateColumns="False" Font-Size="X-Small" Width="860px"
AllowSorting="True">
               <FooterStyle BackColor="#CCCC99" />
               <RowStyle BackColor="#F7F7DE" />
               <SelectedRowStyle BackColor="#CE5D5A" Font-Bold="True"
ForeColor="White" />
               <PagerStyle BackColor="#F7F7DE" ForeColor="Black"
HorizontalAlign="Right" />
               <HeaderStyle BackColor="#6B696B" Font-Bold="True"
ForeColor="White" />
               <AlternatingRowStyle BackColor="White" />
               <Columns>
                   <asp:HyperLinkField DataNavigateUrlFields="NUMBER"
DataTextField="NUMBER" DataNavigateUrlFormatString="~/
PROJECTDETAIL.ASPX?PROJECT={0}" SortExpression="NUMBER" />
                   <asp:BoundField DataField="DESCRIPTIO"
HeaderText="Person" SortExpression="PERSON" />
                   <asp:boundfield DataField="PDMONTH"
HeaderText="Month" SortExpression="PDMONTH" />
                   <asp:BoundField DataField="PDYEAR"
HeaderText="Year" SortExpression="PDYEAR" />
                   <asp:BoundField DataField="TOTAL_PROJ"
HeaderTEXT="Total" SortExpression="TOTAL_PROJ" HtmlEncode="False"
DataFormatString="{0:C}" />
                   <asp:BoundField DataField="MARGIN"
HeaderText="Margin" SortExpression="MARGIN" HtmlEncode="False"
DataFormatString="{0:#.##}" />
                   <asp:boundField DataField="RATE" HeaderText="Rate"
SortExpression="RATE" HtmlEncode="False" DataFormatString="{0:#.##}" /

                   <asp:BoundField DataField="BASEPAID"
HeaderText="Base" SortExpression="BASEPAID" HtmlEncode="False"
DataFormatString="{0:c}" />
                   <asp:BoundField DataField="MARGINPAID"
HeaderText="Margin" SortExpression="MARGINPAID" HtmlEncode="False"
DataFormatString="{0:c}" />
                   <asp:BoundField DataField="TOTALPAID"
HeaderText="Total" SortExpression="TOTALPAID" HtmlEncode="False"
DataFormatString="{0:c}" />
               </Columns>
           </asp:GridView>

       </ContentTemplate>
   </atlas:UpdatePanel>

   <asp:SqlDataSource ID="COMMISSIONS" runat="server"
ConnectionString="<%$ ConnectionStrings:PROJECTSQL %>"
               SelectCommand="SP_COMMISSIONS"
SelectCommandType="StoredProcedure">
               <SelectParameters>
                   <asp:ControlParameter ControlID="DropDownList1"
Name="SALESMAN" PropertyName="SelectedValue"
                       Type="String" />
                   <asp:ControlParameter ControlID="RadioButtonList1"
Name="ACTION" PropertyName="SelectedValue"
                       Type="String" DefaultValue="" />
               </SelectParameters>
           </asp:SqlDataSource>
   <asp:SqlDataSource ID="SUMNOTPAID" runat="server"
ConnectionString="<%$ ConnectionStrings:PROJECTSQL %>"
       SelectCommand="SP_COMMISSIONS_SUMS"
SelectCommandType="StoredProcedure">
       <SelectParameters>
           <asp:ControlParameter ControlID="DropDownList1"
Name="SALESMAN" PropertyName="SelectedValue"
               Type="String" />
           <asp:Parameter DefaultValue="SUMNOTPAID" Name="ACTION"
Type="String" />
       </SelectParameters>
   </asp:SqlDataSource>
   <asp:SqlDataSource ID="SALESMEN" runat="server"
ConnectionString="< %$ ConnectionStrings:PROJECTSQL %>"
       SelectCommand="SELECT DISTINCT(PERSON) AS PERSON&#13;&#10;FROM
PROJMASTSQL&#13;&#10;ORDER BY PERSON">
   </asp:SqlDataSource>
</asp:Content>