Skip to main content

Notifications

Power Apps - Building Power Apps
Unanswered

"Cannot insert duplicate key row" error

Posted on 21 Nov 2024 19:26:19 by 2
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:
  • ronaldwalcott Profile Picture
    ronaldwalcott 704 on 22 Nov 2024 at 18:49:52
    "Cannot insert duplicate key row" error
    If you changed the data source that you are updating then you have effectively changed the Patch.
  • timl Profile Picture
    timl 31,838 on 22 Nov 2024 at 18:26:12
    "Cannot insert duplicate key row" error
    Hi CU21111819-0
     
    Can you post your Patch formula? This is where the problem probably lies. 
  • CU21111819-0 Profile Picture
    CU21111819-0 2 on 22 Nov 2024 at 14:54:28
    "Cannot insert duplicate key row" error
    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?
  • ronaldwalcott Profile Picture
    ronaldwalcott 704 on 22 Nov 2024 at 00:22:59
    "Cannot insert duplicate key row" error
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

October 2024 Newsletter…

October 2024 Community Newsletter…

Community Update Oct 28…

Power Platform Community Update…

Tuesday Tip #4 How to Conntact Support…

Welcome to a brand new series, Tuesday Tips…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 143,061

#2
RandyHayes Profile Picture

RandyHayes 76,308

#3
Pstork1 Profile Picture

Pstork1 63,795

Leaderboard