Microsoft SQL Arithmetic overflow error

Like (0) Share

Report

Posted on 20 Jun 2019 13:23:23 by s1hl3_ukuvuma

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?

All responses (7)

Answers (1)

Verified answer

seadude 1,855 on 20 Feb 2020 at 05:16:47

Like (1)

Report
Re: Microsoft SQL Arithmetic overflow error

Hi @s1hl3_ukuvuma , @RandyHayes and @DavidJennaway ,

I received this error as well.

Appears to be a new issue.

I've used SQL Server pretty extensively and have not faced an issue before when wrapping a Text field in Value().

ONLY happens when app is used on iPhone. Does NOT happen when Patch is executed in PowerApps editor (in browser on laptop)

Here's how I fixed it:

Existing SQL:

-- 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!