You’re offline. This is a read only version of the page.
Hi @RandyHayes
When I save a record with a number text input that has 6 digits or more I get an error message:
Microsoft SQL Arithmetic overflow error converting numeric to data type.
The Char on SQL is 50. so I have not exceeded the character limit.
any ideas?
Hi @s1hl3_ukuvuma , @RandyHayes and @DavidJennaway ,
I received this error as well.
Here's how I fixed it:
Existing SQL:
Share
Report
All responses (
Answers (
-- Create a new table called 'mobileAssets' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.mobileAssets', 'U') IS NOT NULL
DROP TABLE dbo.mobileAssets
GO
-- Create the table in the specified schema
CREATE TABLE dbo.mobileAssets
(
Id INT NOT NULL IDENTITY (1,1) PRIMARY KEY, -- primary key column
ForeignId NVARCHAR(50),
column1 [DECIMAL](8,6),
column2 [DECIMAL](9,6),
column3 [DECIMAL](8,6),
column4 [DECIMAL](7,3),
column5 DATETIME2
);
Non-working Code:
Set(varNow,Now());
Patch('[dbo].[testTable]',
Defaults('[dbo].[testTable]'),
{
Id: "test",
column1: Value(column1.Text),
column2: Value(column2.Text),
column3: Value(column3.Text),
column4: Value(column4.Text),
column5: DateTimeValue(Text(varNow, "[$-en-US]yyyy-mm-ddThh:mm:ss:f"))
}
)
Working Code: Notice how I had to match the SQL data types with the Text() function. Had to put the correct number of chars before and after the decimal point, for each column.
Set(varNow,Now());
Patch('[dbo].[testTable]',
Defaults('[dbo].[testTable]'),
{
Id: "test",
column1: Value(Text(column1.Text, "[$-en-US]##.######")),
column2: Value(Text(column2.Text, "[$-en-US]###.######")),
column3: Value(Text(column3.Text, "[$-en-US]##.######")),
column4: Value(Text(column4.Text, "[$-en-US]##.#####")),
column5: DateTimeValue(Text(varNow, "[$-en-US]yyyy-mm-ddThh:mm:ss:f"))
}
)
Hope this helps!
It sounds like you need to increase the precision of the data-type in SQL. In SQL, numeric (and decimal) data types have a definable precision (total number of digits, including decimal places) and scale (number of decimal places) - see this
So then your TotalCapexAmount_value is defined as numeric and not text...that's fine. How is that field defined exactly in the DB?
The Value function does have a maximum and then it starts to round up and down, but from a character perspective, that is about 16 characters - so, that's not where the problem is coming from.
@RandyHayes The conversion is needed as when i remove the value conversion, it says the value not a text is expected.
remember that it works, and only returns the error when I exceeed 5 digits but it accepts anything less.
Your problem is that you are trying to convert a text value into a value to then have it convert back to a character value for the field.
Remove the Value function from around the TotalCapexAmount_value.Text and try it again. There is no need (from what I see) to do that conversion.
Can you provide the formula that is causing the error, or some screenshots to see what is going on?
#1
WarrenBelz
146,645
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,997
Most Valuable Professional
