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

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

Does your SQL table have a Primary Key?

Highlighted
Regular Visitor

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

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

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

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
Regular Visitor

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

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

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

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
Regular Visitor

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

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

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

Can you share a screenshot for more context? 

 

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

Highlighted
Regular Visitor

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

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,180)