cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerUserTobi
Level: Powered On

How to do server side data validation with SQL Server connector

Hi,

 

when doing crud operations I have to validate the data on my azure sql db (simple validation like min./max. values for a column and complex validation like checking sums for groups of records to be in a defined range).

If the data is not valid I need to throw an error to powerapps so that the user can change the data and try again.

 

Regarding the connector documentation (https://docs.microsoft.com/en-us/connectors/sql/) it's not possible to use triggers:

 

Insert and update to a table does not work if you have a SQL server side Trigger defined on the table.

To workaround this issue, you can do either of the following:

  • Use a Stored Procedure
  • Remove the Trigger of your SQL table

Ok, they say I should use stored procedures, but they also say:

 

Stored Procedures cannot be invoked from PowerApps.

 

How can I call stored procs for crud operations, when it's not supported?

 

So, my question is how can I do (complex) server side data validation - that can not be done in powerapps - for crud operations with SQL Server connector and pass errors back to powerapps, if some exists?

 

thanks & regards

Tobias

 

 

 

4 REPLIES 4
PowerUserTobi
Level: Powered On

Re: How to do server side data validation with SQL Server connector

Hi,

 

is there any official microsoft support I can contact regarding this issue  or powerapps in genereal (as a Microsoft Gold Partner)?

 

thanks & regards

Tobias

Super User
Super User

Re: How to do server side data validation with SQL Server connector

If you want to run a stored procedure after an item is added or updated in SQL then you can create a Flow that triggers the stored procedure.



--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.
PowerUserTobi
Level: Powered On

Re: How to do server side data validation with SQL Server connector

Yes, but the stored procedure (or sql trigger) needs to run in the same transaction as the insert/update statement, so I can validate the data before it gets changed and throw an error to powerapps with a nice error message if needed.

 

Calling a stored proc asynchronously (without even being able to pass the result set/error back to powerapps) is not an option.

 

I can't imagine a business application that does not check the data that gets inserted/updated/deleted with server side code (on app server and/or db server). But it seems to be impossible with Powerapps and Sql Server adapter.

 

 

 

 

 

 

drewski
Level: Powered On

Re: How to do server side data validation with SQL Server connector

Has an official answer ever been given on this? Also trying to figure out how to easily do validation in PowerApps with our On-Prem SQL connection and it doesn't seem to be very intuitive..

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Users Online
Currently online: 86 members 4,601 guests
Please welcome our newest community members: