cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
farhandsome
Helper IV
Helper IV

PowerApps to SQL write - not always working

Hi all,

 

Having a bit of a dilemma here. I have a PowerApp that writes to a SQL server. I am using the Patch() command to write several things back to the server. However, it looks like there are times where not everything gets patched properly. To circumvent this, I've tried to instead trigger a flow within the app that executes a stored procedure to write to the SQL server. This has occasionally failed the same way. Here's a snippet of both ways of writing this:

 

  1. Patch('[dbo].[Booms DBS]',OrderSelect,{Status:"Started"}, {Operator:'Operator Value'.SelectedText.Value}, {StartTime: Now()});
  2. BoomsBuildStart.Run(OrderSelect.ID, "Started", 'Operator Value'.SelectedText.Value, Now());

The strange thing is that not all of the patches will fail. It's usually just the "status" one. But on occasion, it does not write at all. I am not sure if anyone has ever experienced this. Is there a different foolproof method to writing back to SQL? Or is there a way I can ensure that these patches or stored procedures will work?

 

Thanks,

Farhan

 

 

7 REPLIES 7
WarrenBelz
Super User
Super User

Hi @farhandsome ,

What is OrderSelect?

PaulD1
Super User
Super User

Is the Patch failing to overwrite null values in the SQL database? E.g. writing a status value to a record where the status value is currently null?

I have seen issues with PowerApps failing to overwrite null values (not just in SQL, also in SharePoint and even local collections) in the past, but just did a test and can't currently reproduce (perhaps that bug has finally been fixed?).

Can you try using Update or UpdateIf instead of Patch? That was the old work-around for the Patching-over-a-null-fails bug.

Failing that - is there some constraint on your SQL table perhaps?

Thanks @PaulD1 ,

I was heading that direction next (you will see a lot of UpdateIf in my posts).

Just wondering what the single-value criteria was.

 

v-xida-msft
Community Support
Community Support

Hi @farhandsome ,

Based on the issue that you mentioned, I have made a test on my side, and don't have the issue that you mentioned. Have you defind proper Primary Key in your SQL Table already.

 

For your Patch formula, please consider modify it as below:

Patch(
     '[dbo].[Booms DBS]',
      LookUp('[dbo].[Booms DBS]', ID = OrderSelect.ID), // Find the record you want to update
      {
         Status: "Started",
         Operator: 'Operator Value'.SelectedText.Value,
         StartTime: Now()
      }
);

Note: I assume that the ID column is a Primary Key column in your SQL Table '[dbo].[Booms DBS]'

 

For your BoomsBuildStart.Run() formula, I could not find any syntax error with it. Please make sure you have define proper parameters in your flow, and you have provided proper value for these arguments in your BoomsBuildStart.Run() function.

 

Also please take a try re-creating a new connection to your SQL Table from your canvas app, then try above Patch formula again, check if the issue is solved.

 

In addition, please make sure the Network is in a good status. The Network would also affect the function execution in your canvas app.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-xida-msft for the detail.

I was heading this direction after I confirmed what the Patch criteria @farhandsome was using.

Hi all,

 

Thanks for the responses - I have edited the patch formula to reflect this change. I am thinking it may be a network condition as we do have limited wifi in some areas of our production floor.

 

I will try this and if not, I may have to do a workaround.

 

Thanks,

Farhan

abendele
Frequent Visitor

When did this problem start, was it last week with the release of 3.20051.16?  I'm having very similar problems after the release last week.  See my post https://powerusers.microsoft.com/t5/Building-Power-Apps/3-20051-16-PowerApp-Patching-SQL-Data/m-p/57...

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Top Solution Authors
Top Kudoed Authors
Users online (1,313)