PowerApps with SQL server connection

[josecardoso]

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.

[timl]

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 · Accepted solution]

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.

[timl · Accepted solution]

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!

[josecardoso]

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]

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!

[StevenZhang]

@timl , thank you, it solved my problem.

[ez1138]

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.

[Sahba-S]

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?!