Performing CRUD with a SQL DB

[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 

[timl]

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.

[sebgedge]

@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

[timl]

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?

[sebgedge]

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

[timl]

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.

[sebgedge]

@timl 

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

Seb

[mogulman]

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]

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