locked
Arithmetic overflow error converting expression to data type int. RRS feed

  • Question

  • User1020562263 posted

    I am working with an ASP.net 4.0 page with SQL Server 2005. I am writing a query that gets data about paramedics and how much time they spend in certain status such as "on scene", "at hospital", etc. I then compare the individual paramedic to the organization as a whole to see if he is over the average or below. My time frame is for a whole year of data and I keep getting the error: 

    "Arithmetic overflow error converting expression to data type int"

    This query runs just fine in SQL Server Management Studio and returns a result within a few seconds. However with my ASP.net page I receive the error mentioned above. 

    There is a lot of data in these tables so I use CAST AS BIGINT when I sum up the seconds in status. This does not seem to help. Once again, this query runs fine and returns a result in SSMS.

    Can anyone see why this error continues to happen? Code for the whole page shown below.

    <%@ Page Title="" Language="C#" MasterPageFile="~/LCEMS.master" AutoEventWireup="true"
        CodeFile="PersonalPerformanceMetrics2.aspx.cs" Inherits="EMTParamedic_StrategicMetrics_PersonalPerformanceMetrics2" %>
    
    <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
    </asp:Content>
    <asp:Content ID="Content2" ContentPlaceHolderID="MainContent" runat="Server">
        <asp:SqlDataSource ID="qryPersonal" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="SELECT [POF_OfficerName], [POF_Officer_PK] FROM [POfficer] ORDER BY [POF_OfficerName]">
        </asp:SqlDataSource>
        <asp:SqlDataSource ID="qryPerformance" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
            SelectCommand="With paramedicData AS
    (
    SELECT
      M_kIncident
    , CASE
      WHEN M_Code IN ('USDP', 'USDR') THEN 'Dispatch'
      WHEN M_Code IN ('USER') THEN 'EnRoute'
      WHEN M_Code IN ('USOL', 'USEC') THEN 'OffLoading'
      WHEN M_Code IN ('USAV') THEN 'Available'
      WHEN M_Code IN ('USAR', 'USIH', 'UST1', 'USCR', 'USSA', 'USST', 'USS7') THEN 'OnScene'
      WHEN M_Code IN ('USTA', 'USEO') THEN 'TransportArrive'
      ELSE 'NA'
      END AS [Disposition]
    , SUM(CAST(M_SecondsAtStatus AS BIGINT)) AS [TimeInStatus]
    FROM MManpower
    LEFT OUTER JOIN POfficer ON (M_kOfficer1 = POF_Officer_PK) OR (M_kOfficer2 = POF_Officer_PK)
    WHERE POF_Officer_PK = @OfficerPK
    AND M_Code IN ('USDP', 'USDR', 'USER', 'USOL', 'USEC', 'USAV', 'USAR', 'USIH', 'UST1', 'USCR', 'USSA', 'USST', 'USS7', 'USTA', 'USEO')
    AND M_tTime BETWEEN @cal1 AND @cal2
    GROUP BY 
      M_kIncident
    , CASE
      WHEN M_Code IN ('USDP', 'USDR') THEN 'Dispatch'
      WHEN M_Code IN ('USER') THEN 'EnRoute'
      WHEN M_Code IN ('USOL', 'USEC') THEN 'OffLoading'
      WHEN M_Code IN ('USAV') THEN 'Available'
      WHEN M_Code IN ('USAR', 'USIH', 'UST1', 'USCR', 'USSA', 'USST', 'USS7') THEN 'OnScene'
      WHEN M_Code IN ('USTA', 'USEO') THEN 'TransportArrive'
      ELSE 'NA' END
    )
    ,
    
    everyoneElse AS
    (
    SELECT
      M_kIncident
    , CASE
      WHEN M_Code IN ('USDP', 'USDR') THEN 'Dispatch'
      WHEN M_Code IN ('USER') THEN 'EnRoute'
      WHEN M_Code IN ('USOL', 'USEC') THEN 'OffLoading'
      WHEN M_Code IN ('USAV') THEN 'Available'
      WHEN M_Code IN ('USAR', 'USIH', 'UST1', 'USCR', 'USSA', 'USST', 'USS7') THEN 'OnScene'
      WHEN M_Code IN ('USTA', 'USEO') THEN 'TransportArrive'
      ELSE 'NA'
      END AS [Disposition]
    , SUM(CAST(M_SecondsAtStatus AS BIGINT)) AS [TimeInStatus]
    FROM MManpower
    LEFT OUTER JOIN PUnit ON M_kUnit = PUN_Unit_PK
    WHERE M_Code IN ('USDP', 'USDR', 'USER', 'USOL', 'USEC', 'USAV', 'USAR', 'USIH', 'UST1', 'USCR', 'USSA', 'USST', 'USS7', 'USTA', 'USEO')
    AND PUN_UnitAgency = 'LC'
    AND M_tTime BETWEEN @cal1 AND @cal2
    GROUP BY 
      M_kIncident
    , CASE
      WHEN M_Code IN ('USDP', 'USDR') THEN 'Dispatch'
      WHEN M_Code IN ('USER') THEN 'EnRoute'
      WHEN M_Code IN ('USOL', 'USEC') THEN 'OffLoading'
      WHEN M_Code IN ('USAV') THEN 'Available'
      WHEN M_Code IN ('USAR', 'USIH', 'UST1', 'USCR', 'USSA', 'USST', 'USS7') THEN 'OnScene'
      WHEN M_Code IN ('USTA', 'USEO') THEN 'TransportArrive'
      ELSE 'NA' END
    )
    ,
    
    paramedicAverages AS
    (
    SELECT
      Disposition
    , COUNT(M_kIncident) AS [paramedicVolume]
    , AVG(CAST(TimeInStatus AS BIGINT)) AS [paramedicAverage]
    FROM paramedicData
    GROUP BY Disposition
    )
    ,
    
    everyoneAverages AS
    (
    SELECT
      Disposition
    , COUNT(M_kIncident) AS [everyoneVolume]
    , AVG(CAST(TimeInStatus AS BIGINT)) AS [everyoneAverage]
    FROM everyoneElse
    GROUP BY Disposition
    )
    
    SELECT 
      paramedicAverages.Disposition
    , paramedicVolume
    /*, CONVERT(VARCHAR(20), DATEADD(ms, paramedicAverage * 1000, 0), 108) AS [paramedicAverage]*/
    , ParamedicAverage
    , everyoneVolume
    /*, CONVERT(VARCHAR(20), DATEADD(ms, everyoneAverage * 1000, 0), 108) AS [everyoneAverage]*/
    , EveryoneAverage
    FROM paramedicAverages
    INNER JOIN everyoneAverages ON paramedicAverages.Disposition = everyoneAverages.Disposition
    
    " OnSelecting="qryPerformance_Selecting">
            <SelectParameters>
                <asp:ControlParameter ControlID="ddlMedic" Name="OfficerPK" PropertyName="SelectedValue" />
                <asp:ControlParameter ControlID="Calendar1" Name="cal1" PropertyName="SelectedDate" />
                <asp:ControlParameter ControlID="Calendar2" Name="cal2" PropertyName="SelectedDate" />
            </SelectParameters>
        </asp:SqlDataSource>
        <asp:DropDownList ID="ddlMedic" runat="server" DataSourceID="qryPersonal" DataTextField="POF_OfficerName"
            DataValueField="POF_Officer_PK">
        </asp:DropDownList>
        <asp:Calendar ID="Calendar1" runat="server"></asp:Calendar>
        <asp:Calendar ID="Calendar2" runat="server"></asp:Calendar>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="qryPerformance">
            <Columns>
                <asp:BoundField DataField="Disposition" HeaderText="Disposition" ReadOnly="True"
                    SortExpression="Disposition" />
                <asp:BoundField DataField="paramedicVolume" HeaderText="paramedicVolume" ReadOnly="True"
                    SortExpression="paramedicVolume" />
                <asp:BoundField DataField="ParamedicAverage" HeaderText="ParamedicAverage" ReadOnly="True"
                    SortExpression="ParamedicAverage" />
                <asp:BoundField DataField="everyoneVolume" HeaderText="everyoneVolume" ReadOnly="True"
                    SortExpression="everyoneVolume" />
                <asp:BoundField DataField="EveryoneAverage" HeaderText="EveryoneAverage" ReadOnly="True"
                    SortExpression="EveryoneAverage" />
            </Columns>
        </asp:GridView>
    </asp:Content>
    

    Monday, August 18, 2014 1:31 PM

Answers

  • User-1360095595 posted

    think your problem is relayed to the fact that you've only define three parameters in your <SelectParameters> when in fact you have 5. You need to add two more for: @cal1 & @cal2.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 18, 2014 1:54 PM