Can't write to the SQL database, no update SQL command sent by Powerapps to Azure

(0) Share

Report

Posted on by jf7575

Hello,

I have a strange problem with PowerApps connected to an Azure SQL database.

I can read the database perfectly, I can do LookUp etc.

In example, Lookup('[dbo].[products]',partNum=3,partName) returns the product name stored for product number 3.

I also put on the screen a gallery for that table, it shows up my products.

Then I tried to update that same record through a button, with OnSelect, to change the product 3 into a product 9.

Patch('[dbo].[products]',LookUp('[dbo].[products]',partNum=3),{partNum:9})

I run the App in the PowerApp studio, some sliding dots appear on top of the screen when I click th button then stop, but the record does not change in the Gallery, product 3 does not convert into Product 9.

A record for product 3 exists as a field with Lookup('[dbo].[products]',partNum=3,partName) displays the product name.

I then activated the Azure SQL Logs to see the activity on Azure, and then I have the surprise to see absolutely no activity, no SQL query coming from PowerApps.

The user account used by PowerApp for that test is admin on the Azure database and when I check the rights with DataSourceInfo('[dbo].[products]',DataSourceInfo.EditPermission) the result is "true".

To verify if the connection is OK I append Refresh('[dbo].[products]') to my button command and yes, I see 8 lines of SQL logs showing up. I then edit again my OnSelect command to just have Refresh('[dbo].[products]') then erase the SQL log.

I run the app and the Azure SQL log contains the same exact 8 lines.

Conclusion: the Patch() command does not make any call to the Azure SQL Server!

I tried with other PowerApps commands that create or patch records, I have the exact same behavior.

Would anybody have an idea on what happens? Thank you!

John

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

afernando 253 on at

Like (2)

Report

s Hi jf7575,

PowerApps doesn't support read/Write connections to Azure SQL but read only. Please give a kudo to exisiting requests for Read/Write connections and ability to see Access queries to make it happen sooner rather than later.

Cheers

Was this reply helpful? Yes No
Steelman70 873 on at

Like (0)

Report
Hello both, actually you CAN write to SQL Azure.
What happens when you do this?
Patch('[dbo].[products]',LookUp('[dbo].[products]',partNum=3),{partName:"test"})
Also, please place a text box on the screen with this text property:
First(Errors('[dbo].[products]')).Message
It should be blank at first but once you press the update button and the sliding dots appear then if there has been an error writing to the database, the error should appear here.
Does any text appear?

Was this reply helpful? Yes No
jf7575 40 on at

Like (1)

Report

Hello,

Thank you for your reply.
That's exactly the Patch() syntax I put in the Onclick property and nothing happens when that's done except the sliding dots.The Azure SQL logs show there is no SQL transaction happening at all for Patch().
I put a field with the First(Errors()) and there is an error message "The specified record was not found.", which is odd as:
1) There is a record with that searched field value
2) There is no SQL transaction happening with Azure SQL for Patch(), so how can the record not be found
3) If I replace the Patch() with just a Lookup function

All that make me think it is a defect in the SQL interface...

John

Was this reply helpful? Yes No
Steelman70 873 on at

Like (0)

Report
Hi, I am having no problems with Patch() on Azure DB with both cloud (Access Web App on SharePoint Online) and on-premise (Access Web App on SharePoint on-premise). I tried it again 5 minutes ago.
As far as I know the LookUp function is not yet delegated, but it works for me. Maybe you could try this to see if at least it finds any records?
Patch('[dbo].[products]', First('[dbo].[products]'), {partName:"test"})
Does the Remove() or RemoveIf() function work?

Was this reply helpful? Yes No
jf7575 40 on at

Like (0)

Report

Sorry, saved my answer too quickly.
3) If I replace Patch() with just a Lookup function I have the data, and if I replace it with Delete() the sliding dots appear but the record is not deleted.

Big mistery...

Was this reply helpful? Yes No
Steelman70 873 on at

Like (0)

Report

OK, I give up. The only thing I can suggest is the old trick of deleting and reinstalling PowerApps, but I am sure you have tried it.

Was this reply helpful? Yes No
prashantbabber Microsoft Employee on at

Like (1)

Report

Something which worked for me: Created another user (other than sql admin) which has read/write access on the DB

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Created another user (other than sql admin) which has read/write access on the DB worked for me thanks

Was this reply helpful? Yes No