cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

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
Highlighted
Power Apps
Power Apps

Does your SQL table have a Primary Key?

Highlighted

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.

Highlighted

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

 

 

Highlighted

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

Highlighted

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.

Highlighted

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

Highlighted

Can you share a screenshot for more context? 

 

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

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (10,278)