Last post Jul 05, 2019 08:18 AM by jonnygareth30
Jul 03, 2019 11:37 AM|jonnygareth30|LINK
Within my MSSQL DB Table a user has entered a value of 65 into a decimal(5, 2) column but an error message is thrown:
I have tried changing the numeric values within the decimal following advice I have found online but the issue still persists.
Any help would be greatly appreciated.
Jul 03, 2019 02:57 PM|PatriceSc|LINK
It's really just a table with no computed columns at all or triggers? The problem is that you do have a SQL Server side conversion which I don't expect if you are really doing nothing else than inserting data.
Edit: what are you using for doing the insert? Maybe a stored procedure or ADO.NET with explicit defined sizes for your SQL parameters? IMO you assign a decimal to another decimal which is not wide enough for example the following shows the same message
DECLARE @a DECIMAL(5,2)
DECLARE @b DECIMAL(1,1)
SET @a=65 -- Works
SET @b=@a -- Arithmetic overflow error converting numeric to data type numeric.
Edit: changed with just the same definition and value so at some point you likely going through a decimal value (parameter, trigger, etc...) which is not wide enough
Jul 04, 2019 02:53 PM|jonnygareth30|LINK
Thank you for your response, that is right. There are no computed columns or triggers in place. The insert is using ADO.NET using an INSERT COMMAND to insert into the table. there are no Stored Procedures used to insert the data. my apologies for the lack
of information, I have picked this up from a previous developer who has used DevExpress to build the software so not sure if that would cause any issues?
The database table column datatype is set to decimal(5, 2) and the Arithmetic overflow error is thrown.
I have a record stored in the same database with a value of 67 and i do not have any issues opening the record. I have changed the value of the record I am having issues with from 65 to 60 and the record opens no problem, it seems to be 60 + this 1 record
I have an issue with. Seems strange as if it was to do with the DECIMAL(5,2) would the error be thrown up for all values above 60 in my case?
Again thank you very much for your help.
Jul 05, 2019 06:20 AM|Wei Zhang|LINK
I have a record stored in the same database with a value of 67 and i do not have any issues opening the record. I have changed the value of the record I am having issues with from 65 to 60 and the record opens no problem, it seems to be 60 + this 1 record I
have an issue with. Seems strange as if it was to do with the DECIMAL(5,2) would the error be thrown up for all values above 60 in my case?
According to your description, decimal(5,2) means this number has 5 digits, 2 of which are decimals,so the max data should be 999.99,so 60+ numbers shouldn't show any error.
Then what do you mean that 60 to 65 is no problem but 60+ this record has problem?
Jul 05, 2019 07:26 AM|PatriceSc|LINK
And your ADO.NET doesn't specify sizes explicitely ? Columns are in the correct order (swapping decimal columns could perhpas cause this issue). Also I would use SQL Server Profiler or VS tracing tool to see the SQL statement which runs.
Jul 05, 2019 08:18 AM|jonnygareth30|LINK
my apologies for the mistake in my previous message, What I meant was when entering 60 as the value and committing to the database the error did not occur. When entering a value of 61 + that's where the error occurred. I have eventually found where the error
was being caused by a Stored Procedure which submitted the INSERT by using (DECIMAL(3,1). Again my apologies as I had previously said that there was no Stored Procedure committing to the database but I have finally found where it was.
Again thank you very much for your help it has been greatly appreciated.