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

[dthomson]

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

[v-xida-msft]

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

[dthomson]

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]

having the same issue. Any update on this case?

[Anonymous]

I am struggling vice a versa, Every time I submit my form I want to save these records as a new record either in same table or preferably in new other existing table but I can just edit the same existing record.

Unable to create new updated record in the table.

Could anyone please help.