Adding Records to SQL Server Table with Auto-Increment Identity Column

(0) Share

Report

Posted on by sperry1625

763 Super User 2024 Season 1

I have a SQL Table defined like this:

CREATE TABLE [powrapps].[tbl_Amenity](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Title] [nvarchar](255) NOT NULL,
	[PropertyId] [int] NOT NULL,
	[Active] [bit] NOT NULL,
	[Created] [datetime] NULL,
	[CreatedBy] [nvarchar](255) NULL,
	[Modified] [datetime] NULL,
	[ModifiedBy] [nvarchar](255) NULL,
 CONSTRAINT [PK_tbl_Amenity] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I am trying to insert a new record into that table with the following code:

Patch(
 'powrapps.tbl_Amenity',
 Defaults( 'powrapps.tbl_Amenity' ),
 {
 PropertyId: 39,
 Title: "Testing 123",
 Active: 0,
 Created: Now(),
 CreatedBy: gblAppData.User.Email,
 Modified: Now(),
 ModifiedBy: gblAppData.User.Email
 }
)

That is throwing the following error:

The error makes no sense. I have also tried specifying the Id column as Blank(). Same result. It looks like the Patch() statement is trying to perform an update, based on the error. I also tried specifying an actual Id number but get the error:

This makes sense for what I tried to do.

I have seen older posts that this is not supported. Please say it isn't so! I really do NOT want to use stored procedures, they are a PITA to use. This is possible with EditForms, but don't like them either. Should Patch() work?

Thank you.

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Devvj 1,132 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi,
read about a guy having a similar problem with the patch function some time ago, for him it eventually worked when he restarted the SQL Server after the changes were made, and reconnected and refreshed the datasource in PowerApps.

Maybe that could help.

-------------------------------------------------------------------------
If this is the answer for your question, please mark the post as Solved.
If this answer helps you in any way, please give it a like.

Was this reply helpful? Yes No
sperry1625 763 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

It's an Azure SQL Server, cannot really stop and start it. Too many other items on the server. I can try refreshing the connection and see if that helps.

Was this reply helpful? Yes No
Verified answer

sperry1625 763 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
I found the solution to this. It was stupid simple, but it works. My original Patch() statement was as follows:

Patch( 'powrapps.tbl_Amenity', Defaults( 'powrapps.tbl_Amenity' ), { PropertyId: 39, Title: "Testing 123", Active: 0, Created: Now(), CreatedBy: gblAppData.User.Email, Modified: Now(), ModifiedBy: gblAppData.User.Email } )

The fix to get it to work is to drop the Defaults() parameter. The new Patch() statement is as follows and works to insert the new record:

Patch( 'powrapps.tbl_Amenity', { PropertyId: 39, Title: "Testing 123", Active: 0, Created: Now(), CreatedBy: gblAppData.User.Email, Modified: Now(), ModifiedBy: gblAppData.User.Email } )

That will insert a new record successfully.

The last hurdle for this is to grab the Id that was created for the record. I achieve this by doing the following:

UpdateContext( { ctxLastId: Last( 'powrapps.tbl_Amenity' ).Id } );

All comments welcome.

Thank you.

Was this reply helpful? Yes No
EddieE 4,641 Moderator on at

Like (0)

Report
Copy link

Link copied!
@sperry1625

It's generally a better solution to wrap your Patch inside your variable statement to ensure you do get the record just created by the patch. You can do this to achieve that

UpdateContext( { ctxLastId: Patch( 'powrapps.tbl_Amenity', { PropertyId: 39, Title: "Testing 123", Active: 0, Created: Now(), CreatedBy: gblAppData.User.Email, Modified: Now(), ModifiedBy: gblAppData.User.Email } ).Id } );

If you use Last() you may / may not always get the corresponding record due to multiple users using your app at the same time.

This method works in a similar way to the OnSuccess property of a Form.

Was this reply helpful? Yes No
sperry1625 763 Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
I agree with your statement in general, but it doesn't work without an update to the Patch() statement.

The Patch() statement as it stands will only return the columns passed to it, not the full record created. So, my Patch() will not return the Id column, which is the Auto-increment column, or any other column not passed (I have two datetime columns that have default values). To solve this, I adjusted my Patch() as follows:

With( Patch( 'powrapps.tbl_Amenity', { Id: Blank(), PropertyId: cmbHSMCAFProperty.Selected.Id, Title: txtHSMCAFName.Text, Active: If( radHSMCAFActive.Selected.Value = "Yes", 1, 0 ), Created: Blank(), CreatedBy: gblAppData.User.Email, Modified: Blank(), ModifiedBy: gblAppData.User.Email } ) As newAmenity, Collect( colAmenityGridItems, newAmenity ) );

Notice the additional columns, Id (auto-increment), Created (Default GETDATE()), and Modified (Default GETDATE()). With these changes Patch() will return the full record that I can use to add to my collection.

Thank you for the suggestion.

Was this reply helpful? Yes No