cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Regular Visitor

Re: PowerApps with SQL server connection

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

Highlighted
Super User
Super User

Re: PowerApps with SQL server connection

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
Highlighted
Super User
Super User

Re: PowerApps with SQL server connection

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]

 

 

 

 

 

 

 

Highlighted
Regular Visitor

Re: PowerApps with SQL server connection

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

Highlighted
Super User
Super User

Re: PowerApps with SQL server connection

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

Highlighted
Regular Visitor

Re: PowerApps with SQL server connection

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?

Highlighted
Anonymous
Not applicable

Re: PowerApps with SQL server connection

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!

Highlighted
Helper III
Helper III

Re: PowerApps with SQL server connection

@timl , thank you, it solved my problem.

Highlighted
Helper IV
Helper IV

Re: PowerApps with SQL server connection

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.

Highlighted
Advocate I
Advocate I

Re: PowerApps with SQL server connection

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
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Robotic Process Automation

Let's talk about the solution provided by Microsoft for Robotic Process Automation (RPA)

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

Top Solution Authors
Top Kudoed Authors
Users online (8,173)