How do I prevent runtime errors from breaking my code?

(0) Share

Report

Posted on by stapes

894

My App is updating a Database when OnChange is activated:

Patch('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]', First(Filter('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID=SelectedItem.ID)),{DateDelivered:dtDateDelivered.SelectedDate, UpdatedBy:User().FullName,UpdatedOn: Now()});Set(Updated, true)

It all worked fine until I added a Trigger to the SQL Table being updated.

Now, my statement is flagged in red, with the error message:

GoodsInV2 Screen4 - Inspection Tab - trigger error.jpg

I have now removed the Trigger from the DB, but my app is still broken. The error message persists, and my commands are still marked as invalid?

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Mr-Dang-MSFT Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @stapes ,

Have you tried removing and re-adding the SQL table to the app?

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!
Hi @stapes ,

Based on the issue that you mentioned, I think you have some misunderstanding on the known limits with SQL Server connector. Insert and update to a table does not work if you have a SQL server side Trigger defined on the table. Please check the following known limits with SQL Server connector:

https://docs.microsoft.com/en-us/connectors/sql/#known-issues-and-limitations

As an fixed solution, please consider remove the SQL Table connection from your app, then re-create a new connection to your SQL Table from your app, then try your formula again, check if the issue is solved.

In addition, please also consider modify your Patch formula as below:

Patch( '[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]', LookUp('[dbo].[TBL_JS_GOODS_IN_CONTROLSHEET]',ID = SelectedItem.ID), { DateDelivered: dtDateDelivered.SelectedDate, UpdatedBy: User().FullName, UpdatedOn: Now() } ); Set(Updated, true)

Best regards,

Was this reply helpful? Yes No
stapes 894 on at

Like (0)

Report
Copy link

Link copied!

That is not what I am asking here. I am asking how I prevent these issues from breaking the code on the app screen, not about the trigger issue specifically.

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @stapes ,

Currently, within PowerApps, there is no direct way or functions supported to prevent these issues from breaking the code.

As an fixed solution, please consider remove the Trigger from your SQL Table, then re-create a new connection to your SQL Table from your app, then re-type your formula within the OnSelect property of the button.

Best regards,

Was this reply helpful? Yes No
stapes 894 on at

Like (0)

Report
Copy link

Link copied!

I have already removed the trigger from the table - as suggested above. It still was not the question I was asking in this post. We covered that issue in my previous post (https://powerusers.microsoft.com/t5/Building-Power-Apps-Formerly/SQL-Triggers-not-working-with-Canvas-App/m-p/415425#M122717)

What I am asking is how these errors can be prevented from breaking the canvas app in the edit screen. Instead I have to delete & re add the formulas to remove the errors before it will run again. The formulas remain unchanged.

Was this reply helpful? Yes No
stapes 894 on at

Like (0)

Report
Copy link

Link copied!

Hi v-xida-msft

Thanks for the heads up on the Lookup command. Much faster!

Was this reply helpful? Yes No
AchatesChen 51 on at

Like (0)

Report
Copy link

Link copied!

Is there a solution now for this issue? I have to use trigger, so this issue makes the app impossible to be completed. This is really ridiculous! I was using MySQL and all were ok. Now I need to migrate to MSSQL. I thought it is Microsoft product so should have better compatibility, but now...

Was this reply helpful? Yes No