Unanswered

Performing CRUD with a SQL DB

(0) Share

Report

Posted on by sebgedge

Hi,

What is the best practice for performing CRUD operations with SQL? when I create an app I get several errors such as the table is lock and read-only, the primary key is required.

Thanks,

Seb

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

timl 36,715 Super User 2026 Season 1 on at

Like (2)

Report
Copy link

Link copied!

Hi Seb,

PowerApps can only add and update records in SQL Server tables with primary keys. Without a primary key, PowerApps isn't able to uniquely identify the rows in the table, and this is the reason why you see 'read-only' type messages. A primary key on a table is the main requirement for the CUD part of CRUD. The Windows or SQL Server account that you're using must also have add/update permissions on the table.

Another important thing that catches some people out is that PowerApps doesn't support tables with triggers. PowerApps isn't able to add or update rows in tables with triggers.

Was this reply helpful? Yes No
sebgedge 40 on at

Like (0)

Report
Copy link

Link copied!

@timl

so I'm using the following to create a record but it's throwing the following errors.

Patch('[dbo].[DataCollection]',Defaults('[dbo].[DataCollection]'),{Title: TitleInput.Text})

I have add and update permissions on the SQL DB as well the specified record was not found. server Response resource not found.

Seb

Was this reply helpful? Yes No
timl 36,715 Super User 2026 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Just to confirm, when you added the primary key field to your table, did you refresh the data source under the View>Data sources menu?

What did you set as the primary key field? Did you set the title field as the primary key?

Was this reply helpful? Yes No
sebgedge 40 on at

Like (0)

Report
Copy link

Link copied!

HI, @timl I've refreshed the source but have a separate column for the primary key. the table contains the following columns

ELECT TOP (1000) [Title]
,[First_Name]
,[Last_Name]
,[Age]
,[Email]
,[Mobile_No]
,[Address1]
,[Imagedata]
,[PictureData]
,[CreatedBy]
,[CreatedOn]
,[ModifiedBy]
,[ModifiedOn]
,[UId]
,[PKID] - primary key
FROM [dbo].[DataCollection]

Seb

Was this reply helpful? Yes No
timl 36,715 Super User 2026 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @sebgedge

In your Patch statement, are you providing a value for your PKID field? If not, this would result in an error.

Patch('[dbo].[DataCollection]',Defaults('[dbo].[DataCollection]'), {Title: TitleInput.Text, PKID:1} )

If you don't want to manually assign a PKID value, you can configure PKID as an identity column in SQL Server. By doing this, SQL Server will autogenerate the value.

Was this reply helpful? Yes No
sebgedge 40 on at

Like (0)

Report
Copy link

Link copied!

@timl

i've set the column to Identity but am still getting the same error.

Seb

Was this reply helpful? Yes No
mogulman 764 on at

Like (2)

Report
Copy link

Link copied!

Here is an example of the columns I use.

CREATE TABLE dbo.Client(
ClientID int NOT NULL IDENTITY PRIMARY KEY,
ClientName varchar(80) NOT NULL,
BillingStreet varchar(256) DEFAULT(''),
TerminationDate date NULL,
LastChange ROWVERSION NULL,
rowguid uniqueidentifier ROWGUIDCOL NULL, -- I use this in Azure Function as record id.
ModifiedDate datetimeoffset NULL -- use offset for timezone issues
)
GO

Was this reply helpful? Yes No
v-siky-msft Microsoft Employee on at

Like (1)

Report
Copy link

Link copied!

Hi @sebgedge ,

Could you please share more detail with your SQL database?
Set the primary key is the first essential condition to perform CRUD with SQL in PowerApps.
You also need to check if you have defined a Trigger within your SQL Table. If you defined a Trigger within your SQL Table, the Insert and update operation from PowerApps app to your table does not work. Please consider remove the Trigger from your SQL Table, then re-create a new connection to your SQL Table from your app, check if the issue is solved.
If you have set the primary key and removed the trigger, you need to delete the SQL connection and re-create a new connection to your SQL Table from your app, then check if the issue is solved.
Reference: Known issues and limitations of SQL server
If the issue still exists, please consider remove the SQL Table from your Azure SQL, then re-create a new one, re-create a new app based on the new created SQL Table, check if the issue is solved.
Note: It takes a while for changes to SQL to be reflected in PowerApps, please be patient.
Best regards,
Sik

Was this reply helpful? Yes No