Answered

"Cannot insert duplicate key row" error

(0) Share

Report

Posted on by Metron-4

After recreating a view I use as the data source for my Power App. I now cannot edit any entries. After opening the Edit screen, making a change, and trying to save I receive the following message:

"The requested operation is invalid.

Server Response: Cannot insert duplicate key row in object 'dbo.Events' with unique index 'IX_Events'. The duplicate key value is (2022-03-22)."

dbo.Events is one of a few tables I reference in the view. "2022-03-22" refers to a date in the EventDate column in the Events table. I have made no changes to the dbo.Events table. When recreating my view (dbo.EventsView) I added two new columns; one to format the EventDate to use a longer format (Tuesday, March 22, 2022, as LongDate) and a calculated column (MyAge) to calculate age based on a birthdate and the EventDate.

Not sure if this is related, but when I open my app in PA Studio I see a bunch of invalid 'ID' name errors associated with the save edits button. Please see attached image. Here is the EventsView code:

CREATE VIEW [dbo].[EventsView] AS
SELECT dbo.Events.EventDate, FORMAT(dbo.Events.EventDate, 'dddd, MMMM dd, yyyy') AS LongDate, dbo.Events.Event, dbo.Homes.Home, dbo.Eras.Era, dbo.Eras.EraSort, dbo.Employers.Employer, dbo.Events.EventFavorite,
DATEDIFF(YEAR, '1971-11-11 00:00:00:00', dbo.Events.EventDate) - 
        CASE 
            WHEN MONTH('1971-11-11 00:00:00:00') > MONTH(dbo.Events.EventDate) 
                 OR (MONTH('1971-11-11 00:00:00:00') = MONTH(dbo.Events.EventDate) AND DAY('1971-11-11 00:00:00:00') > DAY(dbo.Events.EventDate)) 
            THEN 1 
            ELSE 0 
        END AS MyAge
FROM dbo.Events, dbo.Homes, dbo.Eras, dbo.Employers
WHERE (dbo.Events.EventDate BETWEEN dbo.Homes.HomeStartDate AND dbo.Homes.HomeEndDate)
  AND (dbo.Events.EventDate BETWEEN dbo.Eras.EraStartDate AND dbo.Eras.EraEndDate)
  AND (dbo.Events.EventDate BETWEEN dbo.Employers.EmployerStartDate AND dbo.Employers.EmployerEndDate)

Rob

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

ronaldwalcott 3,862 Moderator on at

Like (0)

Report
Copy link

Link copied!

The message indicates you adding a new record not updating an existing record. I presume you are using a Patch which is probably incorrectly defined.

Was this reply helpful? Yes No
Metron-4 8 on at

Like (0)

Report
Copy link

Link copied!

Yes, you are correct, I am using a Patch function for this save edits control, and in my attached image it does show a bunch of ID errors related to a defined variable (galleryvar1) used to set the gallery screen to return to after saving edits. I never made any changes to the app itself so it has to be a data source issue. The only thing I did differently was recreate the view used in the app using Azure Data Studio to include two new columns as described above. I don't understand how this could cause a "duplicate key value" with the date.

I have been using this app flawlessly for years so it must be related to my view recreation. Well, I also deleted a Secure Store database from my Azure Portal server because I couldn't remember why I created it and it didn't appear to do anything, plus I wasn't using it as a data source in Power Apps. And it was costing me $3.02 a month.

I don't suppose recreating the view negated my ability to update records?

Was this reply helpful? Yes No
timl 36,774 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi CU21111819-0

Can you post your Patch formula? This is where the problem probably lies.

Was this reply helpful? Yes No
ronaldwalcott 3,862 Moderator on at

Like (0)

Report
Copy link

Link copied!

If you changed the data source that you are updating then you have effectively changed the Patch.

Was this reply helpful? Yes No
Metron-4 8 on at

Like (0)

Report
Copy link

Link copied!
Thank you. The dbo references are the same names i used in the view creation, so not sure why recreating the view would affect it. Here is the code (same as the image I attached):

Patch('[dbo].[Events]',If(galleryvar1=1,{ID:BrowseGallery1.Selected.ID},galleryvar1=2,{ID:BrowseGallery1_1.Selected.ID},galleryvar1=3,{ID:BrowseGallery1_2.Selected.ID},galleryvar1=4,{ID:BrowseGallery1_3.Selected.ID},galleryvar1=5,{ID:BrowseGallery1_4.Selected.ID},galleryvar1=6,{ID:BrowseGallery1_5.Selected.ID}),{Event: DataCardValue11.Text}, {EventDate: DataCardValue12.SelectedDate}); Back(ScreenTransition.None);Refresh('[dbo].[EventsView]')

Compare with the image to see what Power Apps finds problematic.

Was this reply helpful? Yes No
Verified answer

timl 36,774 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi Metron-4

The call to Patch attempts to update the record by ID value, but the ID column isn't returned in the View. Therefore I would modify the view to return the ID column.

CREATE VIEW [dbo].[EventsView] AS SELECT dbo.Events.ID, dbo.Events.EventDate, FORMAT(dbo.Events.EventDate, 'dddd, MMMM dd, yyyy') AS LongDate, dbo.Events.Event, dbo.Homes.Home, dbo.Eras.Era, dbo.Eras.EraSort, dbo.Employers.Employer, dbo.Events.EventFavorite, DATEDIFF(YEAR, '1971-11-11 00:00:00:00', dbo.Events.EventDate) - CASE WHEN MONTH('1971-11-11 00:00:00:00') > MONTH(dbo.Events.EventDate) OR (MONTH('1971-11-11 00:00:00:00') = MONTH(dbo.Events.EventDate) AND DAY('1971-11-11 00:00:00:00') > DAY(dbo.Events.EventDate)) THEN 1 ELSE 0 END AS MyAge FROM dbo.Events, dbo.Homes, dbo.Eras, dbo.Employers WHERE (dbo.Events.EventDate BETWEEN dbo.Homes.HomeStartDate AND dbo.Homes.HomeEndDate) AND (dbo.Events.EventDate BETWEEN dbo.Eras.EraStartDate AND dbo.Eras.EraEndDate) AND (dbo.Events.EventDate BETWEEN dbo.Employers.EmployerStartDate AND dbo.Employers.EmployerEndDate)

Was this reply helpful? Yes No
Metron-4 8 on at

Like (0)

Report
Copy link

Link copied!

Ah, thank you! I don't know how that dbo.Events.ID column went missing in my view code. Once I added it back in the app executed flawslessly.

Rob

Was this reply helpful? Yes No