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

Edit form with SQL Azure source can create a new item but cannot edit existing one

I have created an application using Azure SQL as a data source. One of my forms is an edit form used to update a table in the database. I have been having incredible difficulty, though, because this form cannot edit existing records, it can only create new ones. This means that I can call it using the newform function and add new records to my hearts content, but when I try to use it in edit mode (with items set to gallery.selected, which pulls in the correct information), I get the message "an error occurred on the server" and the form does not submit. Checking my DB logs I see that it violated PK constraints - in other words, no matter what I do, the form tries to add a new record, even in edit mode not newform mode. 

I have many other forms which work fine; it's just this one form. I haven't seen this error before, and it was my understanding that a form in edit mode will, by definition, update an existing record. therefore, I have absolutely no idea as to where to turn to solve the problem. Items and all card values are pulling into the form correctly when I navigate from the gallery. In addition, nerform() works fine on both call and submission with the form, indicating that all cards are updating the correct field with the correct values. 

How can I stop my form from trying to add new records even in edit mode? 

 

Thanks, 

Dwayne

3 REPLIES 3
Community Support Team
Community Support Team

Re: Edit form with SQL Azure source can create a new item but cannot edit existing one

Hi @dthomson,

I have made a test on my side, and don't have the issue that you mentioned.

Please check if you have reset your Edit form control using EdirForm function when you edit an existing record.

Also please check if you have defined a proper Primary Key within your Azure SQL table. If you want to update an existing record within your SQL table, a PRIMARY KEY is required within your SQL table.

More details about the known issues ans limits of SQL Server connector in PowerApps, please check the following article:

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

In addition, you could also consider take a try to submit your form data into your SQL table using Patch function instead of SubmitForm functioin. The standard formula as below:

Patch(
    'YourSQLTable',
     Defaults('YourSQLTable'),
     {
     Column1: DataCardValue1. Text,
     Column2: DataCardValue2. Text,
     ...
     }
)

If the issue still exists, please take a try to turn off/disable the "Explicit column selection" option within the Advanced settings of App settings, then check if the issue is solved.

You could also consider take a try to re-create your app, then check if the issue is solved.

 

Best regards,

Kris

 

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.
dthomson
Level: Powered On

Re: Edit form with SQL Azure source can create a new item but cannot edit existing one

Kris, 

 

I did initiate the form as editform, that isn't the problem. I've isolated the issue to the function submitform(formname) for the form I am submitting to my database. there are no triggers on this table. there is an auto-incrementing primary key of type int identity(100,1) which is functioning perfectly fine for the creation of new records. this is the same primary key (with differing integer start points) that I use for all other tables in the db. 

Am I correct in my thinking that the PATCH function you recommended would add a new record, not edit an existing one? My issue is that I cannot edit existing records in the database; the form works just fine for new submissions. 

Thanks for your feedback; I'm totally at a loss on this one because I've done nothing different on this form than any of the others, and the table is not unique either. Very strange. 

JoaoN
Level: Powered On

Re: Edit form with SQL Azure source can create a new item but cannot edit existing one

having the same issue. Any update on this case?

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Demo Extravaganza Championship Voting Open

Voting Ends: October 30, 2019!

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

Top Kudoed Authors
Users Online
Currently online: 453 members 5,055 guests
Please welcome our newest community members: