CODEWITHSUNDEEP
c#asp.netsql-serverasp.net-mvcentity-framework-5
A191919
A191919

Reputation: 3442

Getting Error: Cannot insert explicit value for identity column in table 'Table' when IDENTITY_INSERT is set to OFF

I have simple DataTable

CREATE TABLE [dbo].[Table] (
[Id]   INT        IDENTITY (1, 1) NOT NULL,
[Name] NCHAR (10) NULL,
PRIMARY KEY CLUSTERED ([Id] ASC)
);

With one record, Name="Test" Id get's automatic 0.

When I am using basic create operation 

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(Table table)
{
    if (ModelState.IsValid)
    {
        db.Table.Add(table);
        db.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(table);
}

I get error:

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

What I am doing wrong? How to fix this error?

Table class

public partial class Table
{
    [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Name { get; set; }
}

Upvotes: 1

Views: 2124

Answers (3)

Anup Shetty
Anup Shetty

Reputation: 571

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int Id { get; set; }

This worked for me.

Upvotes: 0

Leandro Soares
Leandro Soares

Reputation: 2972

It smells like the table that you are retrieving from the argument (Table table) is setting the property Id to other value than 0.

If that's the case, be sure that you set the Id to 0 before db.Table.Add(table);

If you want to update the item if it exists, then you need to check if the Id is 0 before adding it.

if(table.Id == 0)
   db.Table.Add(table);
db.SaveChanges();

Upvotes: 0

Salah Akbari
Salah Akbari

Reputation: 39946

You need to set IsDbGenerated to true. Add System.Data.Linq to your references then:

[System.Data.Linq.Mapping.Column(Storage = "Id", DbType = "Int NOT NULL IDENTITY", IsPrimaryKey = true, IsDbGenerated = true)]
public int Id { get; set; }

Upvotes: 1

Related Questions