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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Top Solution Authors
Top Kudoed Authors
Users online (4,387)