cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Microsoft SQL Arithmetic overflow error

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

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)

image.png

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!

View solution in original post

7 REPLIES 7
Highlighted
Super User
Super User

Re: Microsoft SQL Arithmetic overflow error

@s1hl3_ukuvuma 

Can you provide the formula that is causing the error, or some screenshots to see what is going on?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Frequent Visitor

Re: Microsoft SQL Arithmetic overflow error

@RandyHayes  see attached

Highlighted
Super User
Super User

Re: Microsoft SQL Arithmetic overflow error

@s1hl3_ukuvuma 

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. 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Frequent Visitor

Re: Microsoft SQL Arithmetic overflow error

@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.

Highlighted
Super User
Super User

Re: Microsoft SQL Arithmetic overflow error

@s1hl3_ukuvuma 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Solution Supplier
Solution Supplier

Re: Microsoft SQL Arithmetic overflow error

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

Highlighted
Super User
Super User

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)

image.png

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!

View solution in original post

Helpful resources

Announcements
secondImage

New Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

Experience what’s next for Power Apps

Join us for an in-depth look at the new Power Apps features and capabilities at the free Microsoft Business Applications Launch Event.

Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Users online (6,269)