cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

SQL data source says it is read only so cant write new rows to the table

Hi,

 

I have set up a SQL connection to one of our servers/tables.  I am using the gateway option as its on premise sql server.  It connects successfully and can read data fine, however when I try to use patch() or submitform() the patch() function gives an error saying the datasource is read only and the submitform() function gives an error saying the datasource may be invalid.  I am logging in with my credentials (windows auth) and I definitely have permissions to read/write to the tables I am using and have double checked this by doing this throught sql management studio.

 

Anybody have any ideas why this will be happening, another connector which a colleague has set up uses the same Gateway and their app can write and read just fine.

 

Thanks

 

Jono

8 REPLIES 8
Mr-Dang-MSFT
Power Apps
Power Apps

Does your SQL table have a Primary Key?

Anonymous
Not applicable

Hi,

@Mr-Dang-MSFT.  One table im trying to add rows/edit rows has a column called StaffID which is a unique identifier in the table although it has not been defined as primary key in SQL server.  

THe other table im trying to add rows/update only has 1 column there so I guess this is also a unique identified but also not defined in SQL server as a primary key.  My colleague is successfully writing records to this same table but he made his app a year or so ago, however my powerapp cannot.

Can you make sure to define the primary key? It'll need one.

 

For SQL tables moving forward, please be sure to write your CREATE statement like this:

CREATE TABLE dbo.TableName (
    Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
    ...
)

Source: https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/connections/connection-azure-sqldatabas...

 

 

image.png

 

 

Anonymous
Not applicable

Hi,

 

That has helped but now I have a new question, I get the error the specified record was not found.

I googled and saw this is caused when trying to update the primary key.  The thing is I dont want to update any primary key I want to insert a new record, the form is set to NEW but yet it seems to still want to update a record rather than add a new row

In a form, it's not necessary to include a data card for every field. Delete the card for your primary key.

 

This error appears when you have a field that is unique and autogenerated. You cannot write to that field because it's determined for you.

Anonymous
Not applicable

Hi Yes, this is what I thought as well however as I said this table only has 1 column and it is not auto generated.

 

Therefore I cant chose another column or remove it from the form

Can you share a screenshot for more context? 

 

I'd like to see how you have set up the form's property for Item

Anonymous
Not applicable

Hey mate,

 

Here are the details.

 

The first page of the app looks like this (the blanked out stuff is just someones name)  first_page.PNG

So I use a browsegallery and then to choose which record I select the arrow near the relevant record.

This navigates us to the second page which looks like this

All I want to do is update agentlastname with a new value (for when people get married etc)

THe form points to item BrowseGallery1.Selected so that when I use submit() function nothing happens no error but also the record is not updated or when I use

Patch('[dbo].[stg_mdm_agent_list]',First(Filter('[dbo].[stg_mdm_agent_list]',StaffID = BrowseGallery1.Selected.StaffID)),{AgentLastName: TextInput1}) I get the error The requested operation is invalid. Server Response: A value must be provided for item. clientRequestId: 85e2f150-190e-408a-9d59-78678454418a

 second_page.PNG

Helpful resources

Announcements

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!      

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Community Roundup: A Look Back at Our Last 10 Tuesday Tips

As we continue to grow and learn together, it's important to reflect on the valuable insights we've shared. For today's #TuesdayTip, we're excited to take a moment to look back at the last 10 tips we've shared in case you missed any or want to revisit them. Thanks for your incredible support for this series--we're so glad it was able to help so many of you navigate your community experience!   Getting Started in the Community An overview of everything you need to know about navigating the community on one page!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Ranks and YOU Have you ever wondered how your fellow community members ascend the ranks within our community? We explain everything about ranks and how to achieve points so you can climb up in the rankings! Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Powering Up Your Community Profile Your Community User Profile is how the Community knows you--so it's essential that it works the way you need it to! From changing your username to updating contact information, this Knowledge Base Article is your best resource for powering up your profile. Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Community Blogs--A Great Place to Start There's so much you'll discover in the Community Blogs, and we hope you'll check them out today!  Community Links: ○ Power Apps ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Unlocking Community Achievements and Earning Badges Across the Communities, you'll see badges on users profile that recognize and reward their engagement and contributions. Check out some details on Community badges--and find out more in the detailed link at the end of the article! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio    Blogging in the Community Interested in blogging? Everything you need to know on writing blogs in our four communities! Get started blogging across the Power Platform communities today! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Subscriptions & Notifications We don't want you to miss a thing in the community! Read all about how to subscribe to sections of our forums and how to setup your notifications! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Getting Started with Private Messages & Macros Do you want to enhance your communication in the Community and streamline your interactions? One of the best ways to do this is to ensure you are using Private Messaging--and the ever-handy macros that are available to you as a Community member! Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Community User Groups Learn everything about being part of, starting, or leading a User Group in the Power Platform Community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Update Your Community Profile Today! Keep your community profile up to date which is essential for staying connected and engaged with the community. Community Links: ○ Power Apps  ○ Power Automate  ○ Power Pages  ○ Copilot Studio   Thank you for being an integral part of our journey.   Here's to many more Tuesday Tips as we pave the way for a brighter, more connected future! As always, watch the News & Announcements for the next set of tips, coming soon!

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

November 2023 User Group Update: Welcoming New Groups and Upcoming Events

A new month means it's time to celebrate and welcome the new user groups that have joined our community. We are excited to announce that we have more than 25 NEW GROUPS, which is no surprise after the amazing Microsoft Power Platform Conference. This month, we are breaking them out by the different community categories. If your group is listed here, give this post a kudo so we can celebrate with you!Don't forget to take a look at the many events happening near you or virtually! It's a great time of year to connect and engage with User Groups both locally and online.   Please Welcome Our NEW User Groups   Fabric:Fabric & Power BI User GroupData Governance EnthusiastsDFW Fabric Unified Data and AnalyticsMelbourne fabric user groupMicrosoft Fabric User GroupUK Automotive + Transport | Fabric | PowerBI | DataTeesside University PowerBI by Women for WomenManufacturing User Group Power Platform:New Hampshire Power Platform User GroupUnited States Virtual Power Platform User GroupMiami Power UsersBlack Excellence In The Power PlatformPower Platform User Group UgandaDevSecOps IntegrationBlack Women in Tech Oct 9 GroupHealthcare Life Sciences Power User GroupMicrosoft Learn Student Ambassador - Dominican RepublicNWA Power Platform User GroupOklahoma Power Platform CommunityPower Platform User Group Aba   Power Apps:RTP Power Apps User Group Powerapps Honduras   Dynamics365:Dynamics 365 CRM User Group - Dallas and Surrounding AreasDUG4MFG | Microsoft Dynamics & Power Platform - Worldwide   Events:   In Person:Introduction to Microsoft Fabric - In PersonNovember Cleveland Power Platform User Group meeting- In PersonDynUG Høstkonferanse 2023- In PersonPowerAddictsNL Live @Rubicon- In PersonNew Zealand Business Applications Summit- In Person Manchester November 2023 In Person Meeting- In PersonQ4- South West Dynamics 365 and Power Platform User group- In PersonReading Dynamics 365 & Power Platform User Group (Q4)- In PersonDynUG Høstkonferanse 2023- In PersonCambridge in-person meeting - 16th Nov 2023- In Person   Virtual: Git Integration with Power BI- VirtualFabric Product Team present | Fabric for Power BI Users- Virtual DynUG Høstkonferanse 2023- VirtualAzure Data Factory Design Patterns- VirtualZürich - 51st Fabric User Group [ONLINE]- Virtual PAK Time (Power Apps Kwentuhan) #4- Virtual Portallunsj - November 2023- Hybrid Dallas Nov 2023 Power Platform UG Meetup- Virtual What I learned at Summit 2023- Virtual

Top Solution Authors
Top Kudoed Authors
Users online (4,443)