cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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
Super User III
Super User III

@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.
Check out my PowerApps Videos too!

@RandyHayes  see attached

@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.
Check out my PowerApps Videos too!

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

@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.
Check out my PowerApps Videos too!

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

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
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Users online (4,124)