Arithmetic overflow error converting numeric to data type numeric when working with server database

Question

0.00/5 (No votes)

See more:

I am using Stored Procedures to update tables or Insert into tables.
When working with local database (database in the same system and application also in the same system, No network or shared Database) all are working well without any errors.
My application is in Visual C#.

When I have converted the application to use in multiuser environment and used the same existing database with same stored Procedures with same values, and the same Sqlserver Express version, I have encountered the error
"Arithmetic overflow error converting numeric to data type numeric".
I have tried to limit the variables by declaring as a decimal with specified lengths and increased the table columns one decimal more than what I have declared in the stored procedure. But same error coming and unable to find the solution.

Is there any limitations with SQL server compared with local system database vs remote server database.
Both are same data, same values, same code, same application. Nothing changed except the connection string.

The stored procedure code am using is as below.

USE [inventoryDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
SET NUMERIC_ROUNDABORT OFF  -- this is added now because in some place someone said that it will overcome the error.
GO
CREATE PROCEDURE [dbo].[SalesMainInsert]
    (
      @cblno INT=0,
      @bldate DATETIME ='',
      @rname VARCHAR(50)='',
      @custname VARCHAR(50)='',
      @custcode VARCHAR(50)='',
      @smaname VARCHAR(50)='',
      @mblno VARCHAR(50)='',       
      @bldis1per decimal(20,2)=0,
      @bldis1amt decimal(20,2)=0,
      @bldis2 decimal(20,2)=0,
      @bldis3 decimal(20,2)=0,
      @dis3narr VARCHAR(500)='',
      @blcash BIT=0,
      @billnet decimal(20,2)=0,
      @bltax decimal(20,2)=0,
      @addless decimal(20,3)=0,
      @billtot decimal(20,3)= 0,
      @rndval decimal(9,2)=0,
      @blgross decimal(20,2)=0,
      @remarks VARCHAR(50)='',
      @TotalCessAmt decimal(20,3)=0,
      @TotalAddlCessAmt decimal(20,3)=0,
      @cstat AS BIT = 0,
      @bstat AS VARCHAR(10)='',
      @custtype AS VARCHAR(1)='',
      @ReturnValue AS INT = 0 OUT
    )
AS 
    BEGIN
    
    INSERT INTO dbo.salesHDB
(
cblno,
bldate,
rname,
custname,
custcode,
smaname,
mblno,       
bldis1per,
bldis1amt,
bldis2,
bldis3,
dis3narr,
blcash,
billnet,
bltax,
addless,
bltotal,
rndval,
blgross,
remarks,
TotalCessAmt,
TotalAddlCessAmt,
cstatus,
bstatus,
custtype
)
VALUES  ( 
@cblno,
                        @bldate,
                        @rname,
                        @custname,
                        @custcode,
                        @smaname,
                        @mblno,       
                        @bldis1per,
                        @bldis1amt,
                        @bldis2,
                        @bldis3,
                        @dis3narr,
                        @blcash,
                        @billnet,
                        @bltax,
                        @addless,
                        @billtot,
                        @rndval,
                        @blgross,
                        @remarks,
                        @TotalCessAmt,
                        @TotalAddlCessAmt,
                        @cstat,
                        @bstat,
                        @custtype
  )
                        
IF @@ERROR = 0 
    BEGIN
            SET  @ReturnValue = 1
    END
ELSE 
    BEGIN
            SET  @ReturnValue = -1
    END
END

What I have tried:

I Googled but failed to find any suitable solution

Posted 15-Oct-17 2:52am

vijay_bale

Updated 15-Oct-17 4:04am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2017-10-15T04:04:00

Solution 1

No, a local instance is the same code as the remote instance, assuming they are the same version of SQL.

I'd start by using the debugger on whatever is calling the stored procedure, and finding out exactly what you are passing to it in each parameter. It may be that you are not passing the value you think you are, and that causes the problem.

We can't do that for you - we don't even know what language your code is written in, much less what you are doing with it - and this kind of problem needs to be looked at while the app is running in order to "nail down" exactly what is happening.
And the debugger is the perfect tool for that, no matter what language you are using!

Posted 15-Oct-17 4:04am

OriginalGriff

Comments

vijay_bale 16-Oct-17 1:00am

visual c# and sql server express2014

OriginalGriff 16-Oct-17 1:38am

So use the Visual Studio debugger, and look at what is going on!

vijay_bale 16-Oct-17 2:17am

testing system is having sql2016 and server system is having sql 2014express

vijay_bale 17-Oct-17 6:44am

In debugging mode no values will come for store procedure na. Because here I am passing values from devx banded grid like below
cmd.Parameters.AddWithValue("@itmtax", SqlDbType.Float).Value = Convert.ToDouble(item["GAmount"]);
cmd.Parameters.AddWithValue("@itmtot", SqlDbType.Float).Value = Convert.ToDouble(item["NetAmount"]);
//cmd.Parameters.AddWithValue("@itmqty", SqlDbType.Float).Value = Convert.ToDouble(item["Qty"]);
cmd.Parameters.AddWithValue("@itmqty", SqlDbType.Float).Value = Convert.ToDouble(item["NewQty"]);
cmd.Parameters.AddWithValue("@cblno", SqlDbType.Int).Value = Convert.ToInt32(txtBillNo.Text);
cmd.Parameters.AddWithValue("@ItemId", SqlDbType.Int).Value = Convert.ToInt32(item["ItemId"]);
cmd.Parameters.AddWithValue("@itmusrcode", SqlDbType.VarChar).Value = Convert.ToString(item["itmusrcode"]);
cmd.Parameters.AddWithValue("@itmunit", SqlDbType.VarChar).Value = item["itmunit"].ToString();
cmd.Parameters.AddWithValue("@itmbyp", SqlDbType.Float).Value = Convert.ToDouble(item["itmbyp"]);