sql exception arithmetic overflow?

Posted by MyHeadHurts on Stack Overflow See other posts from Stack Overflow or by MyHeadHurts
Published on 2011-01-08T02:02:54Z Indexed on 2011/01/08 2:53 UTC
Read the original article Hit count: 294

Filed under:
|
|
|

In my program the user imports a date and it works whenever the year is in 2011 but if i try a date in 2010 i get this error which is weird

[

SqlException (0x80131904): Arithmetic overflow error converting int to data type numeric.]
           System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +1950890
           System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +4846875
           System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +194
           System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2392
           System.Data.SqlClient.SqlDataReader.HasMoreRows() +157
           System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +197
           System.Data.SqlClient.SqlDataReader.Read() +9
           System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) +78
           System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) +164
           System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +282
           System.Data.Common.LoadAdapter.FillFromReader(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) +19
           System.Data.DataTable.Load(IDataReader reader, LoadOption loadOption, FillErrorEventHandler errorHandler) +222
           System.Data.DataTable.Load(IDataReader reader) +14

(
    @YearToGet int, 
    @current datetime,
@y int,
@search datetime
) 
AS
    SET @YearToGet = 2006;
    WITH Years AS (
        SELECT DATEPART(year, GETDATE()) [Year]
        UNION ALL
        SELECT [Year]-1 FROM Years WHERE [Year]>@YearToGet 
    ),


q_00 as (
select
      DIVISION
     , DYYYY
     , sum(PARTY)         as asofPAX        
    , sum(InsAmount)     as asofSales        
from dbo.B101BookingsDetails 
INNER JOIN Years                     ON B101BookingsDetails.DYYYY = Years.Year
where Booked <= CONVERT(int, DateAdd(year, (Years.Year  - @y), @search))
  and DYYYY = Years.Year 
group by DIVISION, DYYYY, years.year
having  DYYYY = years.year
),
q_01 as (
select     
      DIVISION 
    , DYYYY 
    , sum(PARTY)         as YEPAX 
    , sum(InsAmount)     as YESales
from  dbo.B101BookingsDetails 
INNER JOIN Years                     ON B101BookingsDetails.DYYYY = Years.Year
group by DIVISION,  DYYYY , years.year
having  DYYYY = years.year
),
q_02 as (
select
      DIVISION
    , DYYYY
    , sum(PARTY)         as CurrentPAX        
    , sum(InsAmount)     as CurrentSales        
    from dbo.B101BookingsDetails 
INNER JOIN Years                     ON B101BookingsDetails.DYYYY = Years.Year
where Booked <= CONVERT(int,@current)
  and DYYYY = (year( getdate() ))
group by DIVISION,  DYYYY 

)

select
      a.DIVISION 
     , a.DYYYY
    , asofPAX        
    , asofSales        
   , YEPAX 
    , YESales
      , CurrentPAX 
    , CurrentSales   
  ,asofsales/ ISNULL(NULLIF(yesales,0),1) as percentsales,
           CAST((asofpax) AS DECIMAL(5,1))/yepax as percentpax 

from q_00 as a
join q_01 as b on (b.DIVISION = a.DIVISION  and b.DYYYY = a.DYYYY) 
join q_02 as c on (b.DIVISION = c.DIVISION)
JOIN Years as d on (b.dyyyy = d.year)
where A.DYYYY <> (year( getdate() ))
order by a.DIVISION,  a.DYYYY ;

© Stack Overflow or respective owner

Related posts about .NET

Related posts about sql