cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jono_tower
Level: Powered On

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
Power Apps Staff Mr-Dang-MSFT
Power Apps Staff

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?

jono_tower
Level: Powered On

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.

Power Apps Staff Mr-Dang-MSFT
Power Apps Staff

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

 

 

jono_tower
Level: Powered On

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

Power Apps Staff Mr-Dang-MSFT
Power Apps Staff

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
jono_tower
Level: Powered On

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

Power Apps Staff Mr-Dang-MSFT
Power Apps Staff

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

jono_tower
Level: Powered On

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
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,920)