Arithmetic overflow error converting real to data type numeric issue any number 5 digits onwards below 5 digits working fineRSS

• • Reply
  cplusplus1

  Member

  104 Points

  490 Posts

  Arithmetic overflow error converting real to data type numeric issue any number 5 digits onwards...

  Nov 07, 2017 08:44 PM|cplusplus1|LINK

  getting exception when the search value is in 5 digits onwards: less than 10000 is fine, it used to work and throwing below exception arithmetic overflow. not sure what is causing the problem.

  Arithmetic overflow error converting real to data type numeric.

  right on this line: 

              objAdapter.Fill(objDataSet) causing issue, please need help, using vs 2008, sql server been using for last 6 years with no issue.

  
  
  
     Public Sub ExecStoredProc(ByVal sSPName As String, ByVal arrParamValues As ArrayList, _
            ByRef arrOutputValues As ArrayList, ByRef objDataSet As DataSet)
  
          Dim objConn As SqlConnection = Nothing
          Dim objAdapter As SqlDataAdapter
          Dim cmdCommand As New SqlCommand
          Dim iCount As Integer
          Dim ii As Integer
          Dim objParam As SqlParameter
          Dim sConnection As String
  
          Try
              'sConnection = ConfigurationSettings.AppSettings("QMain")
              sConnection = ConfigurationManager.ConnectionStrings("QMain").ConnectionString
              objAdapter = New SqlDataAdapter
              objConn = New SqlConnection(sConnection)
              objConn.Open()
              objAdapter.SelectCommand = cmdCommand
              objAdapter.SelectCommand.CommandTimeout = 900
              objAdapter.SelectCommand.Connection = objConn
              objAdapter.SelectCommand.CommandType = CommandType.StoredProcedure
              objAdapter.SelectCommand.CommandText = sSPName
  
              'sConnection.Connection.ConnectionTimeout = 30;
  
  
  
  
              SqlCommandBuilder.DeriveParameters(objAdapter.SelectCommand)
              ii = 0
              For iCount = 1 To arrParamValues.Count
                  If arrParamValues(ii).ToString = "" Then
                      objAdapter.SelectCommand.Parameters(iCount).Value = DBNull.Value
                  Else
                      objAdapter.SelectCommand.Parameters(iCount).Value = arrParamValues(ii)
                  End If
                  ii += 1
              Next
  
              objAdapter.Fill(objDataSet)
  
              For Each objParam In objAdapter.SelectCommand.Parameters
                  If (objParam.Direction = ParameterDirection.Output) Or _
                      (objParam.Direction = ParameterDirection.InputOutput) Then
                      arrOutputValues.Add(objParam.Value.ToString)
                  End If
                  objParam = New SqlParameter
              Next
              objConn.Close()
          Catch Ex As Exception
              Dim s As String = Ex.Message
              objConn.Close()
              'SimpleMessageBox(Ex.Message) PLEASE Do NOT force an Alert here. Let the programmer decide if the user has to see an alert or not. Luis.
              Throw Ex
          End Try
      End Sub
  

  Cplusplus
• • Reply
  KathyW

  Contributor

  4931 Points

  1925 Posts

  Re: Arithmetic overflow error converting real to data type numeric issue any number 5 digits onwa...

  Nov 07, 2017 11:14 PM|KathyW|LINK

  http://www.sql-server-helper.com/error-messages/msg-8115-numeric-to-numeric.aspx

  "This error is usually encountered with decimal or numeric data types wherein the precision of the column or variable is not enough to hold the value being assigned to it."

  If numbers 10000 or higher used to work, what did you change recently?

• • Reply
  Eric Du

  Contributor

  6710 Points

  2715 Posts

  Re: Arithmetic overflow error converting real to data type numeric issue any number 5 digits onwa...

  Nov 08, 2017 08:01 AM|Eric Du|LINK

  Hi cplusplus1,

  According to your description and error message, as far as I know, this issue maybe related to the definition of the numeric type field in sql server. 

  Please check the table definition, check if you define the field type to Numeric(X,Y)(The first value is the precision and the second is the scale) x below and equal to 5, then you put the value over 5 digit?  For more details, please check the following tutorial:

  https://www.w3schools.com/sql/sql_datatypes.asp 

  Best Regards,

  Eric Du

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.