AutoNumber ID in database

Question

What to do when the data is inserted in the database, the "ID" column is autonumber. I tried this:

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int IdMema { get; set; }
public string Autor { get; set; }
public int? Like { get; set; }
public int? Dislike { get; set; }

Properties "Memy" table:

CREATE TABLE [dbo].[Memy] (
    [Id_mema] INT  NOT NULL IDENTITY,
    [autor]   TEXT NULL,
    [like]    INT  NULL,
    [dislike] INT  NULL,
    PRIMARY KEY CLUSTERED ([Id_mema] ASC)
);

When I try to insert data, then I see:

"SqlException: Cannot insert explicit value for identity column in table 'Memy' when IDENTITY_INSERT is set to OFF."

How to resolve this?

the reason is you haven't set `auto increment` in your id column. — Syed Ali Taqi, Apr 11 '19 at 05:14
@kamilm758 if recreating table is an easy option than just delete the table and re run `create` query with this change `[Id_mema] INT IDENTITY(1,1) NOT NULL PRIMARY KEY` — Syed Ali Taqi, Apr 11 '19 at 05:20
@SyedAliTaqi It is auto-increment PK already ( IDENTITY, PRIMARY KEY) — Risto M, Apr 11 '19 at 05:42

Risto M · Answer 1 · 2019-04-11T05:40:35.310

1

You have auto-increment field Id_mema. Auto-increment is set with IDENTITY keyword. Exception says it:

SqlException: Cannot insert explicit value for identity column in table 'Memy' when IDENTITY_INSERT is set to OFF.

When you insert data to your table, you cannot give value to identity field. DB engine gives value to Id_mema-field (auto-increment 0,1,2,3..)

Following should work (straight sql):

INSERT INTO MEMY(autor, like, dislike) values ('text',1,0)

and in case of entity framework, be sure not to set Id_mema in your code.

edited Apr 11 '19 at 05:40

answered Apr 11 '19 at 05:32

Risto M

2,919
1
14
27

1

but the OP hasn't set ID column to be auto increment, I'm confused what value would be stored in case of query you provided. – Syed Ali Taqi Apr 11 '19 at 05:35
It is auto-increment! `[Id_mema] INT NOT NULL IDENTITY` and PRIMARY KEY – Risto M Apr 11 '19 at 05:38

score 0 · Answer 2 · answered Apr 11 '19 at 05:56

You don't need to pass Id_mema value when you going to insert values like below.

INSERT INTO MEMY values ('kamilm',10,5);

OR

if you don't want to auto incremented column then change the DatabaseGeneratedOption.Identity to DatabaseGeneratedOption.None it will allow you to insert value in Id(Key) column.

 [Key]
 [DatabaseGenerated(DatabaseGeneratedOption.None)]
 public int IdMema { get; set; }
 public string Autor { get; set; }
 public int? Like { get; set; }
 public int? Dislike { get; set; }

AutoNumber ID in database

2 Answers2