cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

PowerApps with SQL server connection

Hello.

I'm a newbie with PowerApps and I'm starting a very simple App with SQL server. So, I've created a database table with just two columns, id an name, for testing purposes.

The id field is an int PK, auto increment and not null. Name is a varchar (50).

The connection is made with by a gateway to a local SQL database and everything is OK.

So, I've used the wizzard to create the app and tried to insert a value.

The problem is that, when I input a name on Name field, the record is not saved because an error is comming saying that the id field is mandatory.

So, if the field on the database is a PK with auto increment, why shall I input a number for the id?

I expect to hide the id field and that this field was automatically filled on the database.

What am I doing wrong?

Thanks in advance.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

Dear Timl,

I've created a new table as you proposed, with just a small adjusment on the name:

CREATE TABLE [dbo].[ZTestTable]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY]

Now, when I try to create the App exactly the same way, there are only Browse Screen and Detail Screen. The Edit Screen is not created by default.

 

Am I missing something here?

Thanks in advance.

View solution in original post

Hi @josecardoso

 

Sorry about that, I forgot to script the creation of the primary key, which would explain why the the designer doesn't create the edit screen.

 

In Management Studio, if you select the ID column and click the primary key icon (or select the option in the right-click context menu), that should set up the table in exactly the same way as me. And then hopefully, that should work for you!

 

PKSetup.PNG

 

 

View solution in original post

8 REPLIES 8
Super User III
Super User III

Hi @josecardoso

 

What you've done there should work. Did you definitely set the identity property on your id field?

 

I've just tested this with the following table and it works for me. Could you create an app with the same table and see if it works?

 

CREATE TABLE [dbo].[TestTable](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]

 

 

 

 

 

 

 

Dear Timl,

I've created a new table as you proposed, with just a small adjusment on the name:

CREATE TABLE [dbo].[ZTestTable]( [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NULL ) ON [PRIMARY]

Now, when I try to create the App exactly the same way, there are only Browse Screen and Detail Screen. The Edit Screen is not created by default.

 

Am I missing something here?

Thanks in advance.

View solution in original post

Hi @josecardoso

 

Sorry about that, I forgot to script the creation of the primary key, which would explain why the the designer doesn't create the edit screen.

 

In Management Studio, if you select the ID column and click the primary key icon (or select the option in the right-click context menu), that should set up the table in exactly the same way as me. And then hopefully, that should work for you!

 

PKSetup.PNG

 

 

View solution in original post

OK, both replys were the solution. I think I was missing the

IDENTITY(1,1)

on id field.

 

I'm also trying to use MySQL and the behaviour is exactly the same, although I have AUTO_INCREMENT, which I think is the same as IDENTITY for MSSQL.

 

Do you have any knowledge on this?

Anonymous
Not applicable

Hi @josecardoso @timl,

 

I have the same problem. my table property is this: CREATE TABLE [DSL].[MDS_EMPLOYEE_SICKNESS](
[Code] [int] IDENTITY(1,1) NOT NULL, etc.

 

When on PowerApps i try to add some new row/record then i must insert a Primary key manually. How can i fix this that when i submit on powerapps that the Primary key field will automatic be created?

 

Thank you!

@timl , thank you, it solved my problem.

Hi, having the same issue although I have tables in place.  Rather than create is there a way to modify an existing table?

 

For instance, I have a dbo.contacts with a Contacts_ID column that I set as the PK.

 

Thanks in advance.

Advocate I
Advocate I

Hi @josecardoso  & @timl 

Has there been a solution for this? I have the same issue but it only happens for some of the users. Some users can input the data without any issues and some get the error "Can not insert null into PK column". 

I have checked my SQL table design and the "Identity Specification" is set to "yes" for the PK column. 

How is it that it only happens to some users and not all?!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Community Highlights

Community Highlights

Check out the Power Platform Community Highlights

Top Solution Authors
Top Kudoed Authors
Users online (12,979)