cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
josecardoso
Level: Powered On

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
josecardoso
Level: Powered On

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

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

5 REPLIES 5
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]

 

 

 

 

 

 

 

josecardoso
Level: Powered On

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

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

josecardoso
Level: Powered On

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?

AraSaber12
Level 8

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!

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,669)