cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sebgedge
Helper I
Helper I

Performing CRUD with a SQL DB

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 

8 REPLIES 8
timl
Super User
Super User

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.

 

 

@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

 

 

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?

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

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.

@timl 

 

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


Seb

mogulman
Impactful Individual
Impactful Individual

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

v-siky-msft
Community Support
Community Support

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

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (3,704)