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

Question

I am creating Edit HTTP Post Action Method and when I press Edit button I get error

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

When I check table [dbo].[Garancija] I saw that Identity is ON

CREATE TABLE [dbo].[Garancija] (
    [ProduzenaGarancijaID]  INT            IDENTITY (1, 1) NOT NULL,
    [Broj_Police]           INT            NOT NULL,
    [GeneralniUgovor]       INT            NOT NULL,
    [ImeVlasnika]           NVARCHAR (MAX) NULL,
    [Adresa_Ulica]          NVARCHAR (MAX) NULL,
    [Adresa_Broj]           NVARCHAR (MAX) NULL,
    [Adresa_PBroj]          NVARCHAR (MAX) NULL,
    [Adresa_Mjesto]         NVARCHAR (MAX) NULL,
    [JMBG]                  NVARCHAR (MAX) NULL,
    [VrsteVozilaID]         INT            NOT NULL,
    [MarkaVozilaID]         INT            NOT NULL,
    [ModelVozilaID]         INT            NOT NULL,
    [Sasija]                NVARCHAR (MAX) NULL,
    [Snaga]                 NVARCHAR (MAX) NULL,
    [Zapremina]             NVARCHAR (MAX) NULL,
    [VrstaMjenjacaID]       INT            NOT NULL,
    [Is4x4]                 BIT            NULL,
    [Predjeni_Kilometri]    NVARCHAR (MAX) NULL,
    [Kupovna_Vrijednost]    NVARCHAR (MAX) NULL,
    [Namjena]               NVARCHAR (MAX) NULL,
    [GorivoID]              INT            NOT NULL,
    [DatumPrveRegistracije] NVARCHAR (MAX) NULL,
    [DatumPrveProdaje]      NVARCHAR (MAX) NULL,
    [DatumKupovineVozila]   NVARCHAR (MAX) NULL,
    [DatumPocetkaGarancije] NVARCHAR (MAX) NULL,
    [DatumIstekaGarancije]  NVARCHAR (MAX) NULL,
    [TrajanjeGarancije]     NVARCHAR (MAX) NULL,
    [LimitPokrica]          NVARCHAR (MAX) NULL,
    [OgranicenjeKm]         FLOAT (53)     NULL,
    [ProduktID]             INT            NOT NULL,
    [ProgramPokrica]        NVARCHAR (MAX) NULL,
    [CjenovnikID]           INT            NOT NULL,
    [Cijena]                FLOAT (53)     NOT NULL,
    [DatumUnosta]           NVARCHAR (MAX) NULL,
    [DatumPromjene]         NVARCHAR (MAX) NULL,
    [BrojPotvrde]           INT            NOT NULL,
    CONSTRAINT [PK_Garancija] PRIMARY KEY CLUSTERED ([ProduzenaGarancijaID] ASC),
    CONSTRAINT [FK_Garancija_VrsteMjenjaca_VrstaMjenjacaID] FOREIGN KEY ([VrstaMjenjacaID]) REFERENCES [dbo].[VrsteMjenjaca] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Garancija_VrsteVozila_VrsteVozilaID] FOREIGN KEY ([VrsteVozilaID]) REFERENCES [dbo].[VrsteVozila] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Garancija_Cjenovnik_CjenovnikID] FOREIGN KEY ([CjenovnikID]) REFERENCES [dbo].[Cjenovnik] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Garancija_Gorivo_GorivoID] FOREIGN KEY ([GorivoID]) REFERENCES [dbo].[Gorivo] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Garancija_MarkeVozila_MarkaVozilaID] FOREIGN KEY ([MarkaVozilaID]) REFERENCES [dbo].[MarkeVozila] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Garancija_Modeli_ModelVozilaID] FOREIGN KEY ([ModelVozilaID]) REFERENCES [dbo].[Modeli] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_Garancija_Produkti_ProduktID] FOREIGN KEY ([ProduktID]) REFERENCES [dbo].[Produkti] ([Id]) ON DELETE CASCADE
);

What I did so far is that I try to debug application but compiler is not enter into method

    using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using TriglavOsiguranje.Data;
using TriglavOsiguranje.Models.ViewModels;
using TriglavOsiguranje.Models;
using Microsoft.AspNetCore.Mvc.Rendering;
using System.Net;

namespace TriglavOsiguranje.Areas.Uposlenik.Controllers
{
    [Area("Uposlenik")]
    public class GarancijaController : Controller
    {
        private ApplicationDbContext _db;

        [BindProperty]
        public GarancijaViewModel GarancijaVM { get; set; }

        public GarancijaController(ApplicationDbContext db)
        {
            _db = db;

            GarancijaVM = new GarancijaViewModel()
            {
                Gorivo = _db.Gorivo.ToList(),
                Cijenovnik = _db.Cjenovnik.ToList(),
                MarkeVozila = _db.MarkeVozila.ToList(),
                ModeliVozila = _db.Modeli.ToList(),
                Produkt = _db.Produkti.ToList(),
                VrstaMjenjaca = _db.VrsteMjenjaca.ToList(),
                VrstaVozila = _db.VrsteVozila.ToList(),
                Garancija = new ProduzenaGarancija()
            };
        }
        public async Task<IActionResult> Index()
        {
            var garancija = _db.Garancija.Include(m => m.MarkaVozila).Include(m => m.ModelVozila).Include(m => m.VrsteVozila);
            return View(await garancija.ToListAsync());
        }

        //GET : Product Create
        public IActionResult Create()
        {
            return View(GarancijaVM);
        }

        [HttpPost, ActionName("Create")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> CreatePOST()
        {
            if (ModelState.IsValid)
            {
                _db.Garancija.Add(GarancijaVM.Garancija);
                await _db.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            return View(GarancijaVM);
        }



        //GET Edit
        public async Task<IActionResult> Edit(int? Id)
        {
            if (Id == null)
            {
                return NotFound();
            }

            GarancijaVM.Garancija = await _db.Garancija.Include(m => m.Gorivo)
                 .Include(m => m.MarkaVozila).Include(m => m.ModelVozila)
                .Include(m => m.Produkt).Include(m => m.VrstaMjenjaca)
                .Include(m => m.VrsteVozila).Include(m => m.Cjenovnik)
                .SingleOrDefaultAsync(m => m.ProduzenaGarancijaID == Id);

            return GarancijaVM.Garancija == null ? NotFound() : (IActionResult)View(GarancijaVM);
        }


        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Edit(int id, ProduzenaGarancija garancija)
        {
            if (id != garancija.ProduzenaGarancijaID)
            {
                return NotFound();
            }

            if (ModelState.IsValid)
            {
                _db.Update(garancija);
                await _db.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            return View(garancija);
        }


        //GET: Details Garancija
        public async Task<IActionResult> Details(int? Id)
        {
            if (Id == null)
            {
                return NotFound();
            }

            GarancijaVM.Garancija = await _db.Garancija.Include(m => m.Gorivo)
                .Include(m => m.Gorivo).Include(m => m.MarkaVozila).Include(m => m.ModelVozila)
                .Include(m => m.Produkt).Include(m => m.VrstaMjenjaca).Include(m => m.VrsteVozila)
                .SingleOrDefaultAsync(m => m.ProduzenaGarancijaID == Id);

            if (GarancijaVM.Garancija == null)
            {
                return NotFound();
            }
            return View(GarancijaVM);
        }

        //GET: Delete GarancijaVM
        public async Task<IActionResult> Delete(int? Id)
        {
            if (Id == null)
            {
                return NotFound();
            }

            GarancijaVM.Garancija = await _db.Garancija.Include(m => m.Gorivo)
                .Include(m => m.Gorivo).Include(m => m.MarkaVozila).Include(m => m.ModelVozila)
                .Include(m => m.Produkt).Include(m => m.VrstaMjenjaca).Include(m => m.VrsteVozila)
                .SingleOrDefaultAsync(m => m.ProduzenaGarancijaID == Id);

            if (GarancijaVM.Garancija == null)
            {
                return NotFound();
            }
            return View(GarancijaVM);
        }

        [HttpPost, ActionName("Delete")]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Delete(int id)
        {
            ProduzenaGarancija produzena = await _db.Garancija.FindAsync(id);

            if (produzena == null)
            {
                return NotFound();
            }
            else
            {
                _db.Garancija.Remove(produzena);
                await _db.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
        }
    }
}

Second think which I did is in my model I am put propery but I doesnt fix the error

[DatabaseGenerated(DatabaseGeneratedOption.Identity)]

Here is my Model

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;

namespace TriglavOsiguranje.Models
{
    public class ProduzenaGarancija
    {
        /*Podaci o vlasniku*/
        [Key]    
        public int ProduzenaGarancijaID { get; set; }

        public int Broj_Police { get; set; }

        public int GeneralniUgovor { get; set; }

        public string ImeVlasnika { get; set; }

        public string Adresa_Ulica { get; set; }

        public string Adresa_Broj { get; set; }

        public string Adresa_PBroj { get; set; }

        public string Adresa_Mjesto { get; set; }

        public string JMBG { get; set; }

                 /* Podaci o vozilu */

        [Display(Name ="Vrste Vozila")]
        public int VrsteVozilaID { get; set; }

        [ForeignKey("VrsteVozilaID")]
        public virtual VrstaVozila VrsteVozila { get; set; }

        [Display(Name = "Marka vozila")]
        public int MarkaVozilaID { get; set; }

        [ForeignKey("MarkaVozilaID")]
        public virtual MarkeVozila MarkaVozila { get; set; }

        [Display(Name = "Model vozila")]
        public int ModelVozilaID { get; set; }

        [ForeignKey("ModelVozilaID")]
        public virtual ModeliVozila ModelVozila { get; set; }

        public string Sasija { get; set; }

        public string Snaga { get; set; }

        public string Zapremina { get; set; }

        [Display(Name = "Vrsta mjenjaca")]
        public int VrstaMjenjacaID { get; set; }

        [ForeignKey("VrstaMjenjacaID")]
        public virtual VrstaMjenjaca VrstaMjenjaca { get; set; }

        public bool Is4x4 { get; set; }

        public string Predjeni_Kilometri { get; set; }

        public string Kupovna_Vrijednost { get; set; }

        public string Namjena { get; set; }

        [Display(Name = "Vrsta goriva")]
        public int GorivoID { get; set; }

        [ForeignKey("GorivoID")]
        public virtual Gorivo Gorivo { get; set; }

        /* Podaci o garanciji */

        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public string DatumPrveRegistracije { get; set; }

        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public string DatumPrveProdaje { get; set; }

        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public string DatumKupovineVozila { get; set; }

        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public string DatumPocetkaGarancije { get; set; }

        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public string DatumIstekaGarancije { get; set; }

        public string TrajanjeGarancije { get; set; }

        public string LimitPokrica { get; set; }

        public double OgranicenjeKm { get; set; }

        [Display(Name = "Produkt/Nacin prodaje")]
        public int ProduktID { get; set; }

        [ForeignKey("ProduktID")]
        public virtual Produkt Produkt { get; set; }

        public string ProgramPokrica { get; set; }

        [Display(Name = "Cjenovnik / Zapremina Vozila")]
        public int CjenovnikID { get; set; }

        [ForeignKey("CjenovnikID")]
        public virtual Cjenovnik Cjenovnik { get; set; }

        public double Cijena { get; set; }

        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public string DatumUnosta { get; set; }

        [DisplayFormat(ApplyFormatInEditMode = true, DataFormatString = "{0:MM/dd/yyyy}")]
        public string DatumPromjene { get; set; }

        public int BrojPotvrde { get; set; }


    }
}

I am also using VM to bind dropdown from db

using Microsoft.AspNetCore.Mvc.Rendering;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TriglavOsiguranje.Models.ViewModels
{
    public class GarancijaViewModel
    {
        public ProduzenaGarancija Garancija { get; set; }

        public IEnumerable<Cjenovnik> Cijenovnik { get; set; }
        public IEnumerable<Gorivo> Gorivo { get; set; }
        public IEnumerable<MarkeVozila> MarkeVozila { get; set; }
        public IEnumerable<ModeliVozila> ModeliVozila { get; set; }
        public IEnumerable<Produkt> Produkt { get; set; }
        public IEnumerable<VrstaMjenjaca> VrstaMjenjaca { get; set; }
        public IEnumerable<VrstaVozila> VrstaVozila { get; set; }




               }
}

I am also checked this link here

Link 1

Link 2

Link 3

I have no idea where this error comes from but I suppose its problem with database. A couple of time I update-database and check Is Identity = true but doesnt solve my issue Any suggestion or comment what could be problem ?

EDIT

Project.Areas.Uposlenik.Controllers.GarancijaController.Create(ProduzenaGarancija garancija) in GarancijaController.cs
-
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create(ProduzenaGarancija garancija)
        {
            if (ModelState.IsValid)
            {
                _db.Garancija.Add(garancija);
                await _db.SaveChangesAsync();
                return RedirectToAction(nameof(Index));
            }
            return View(garancija);
        }

Your edit is trying to set your table's `Garancija.ProduzenaGarancijaID` column to a certain value, but because that column is defined as an `identity` column, MSSQL doesn't allow you to do that. — Ian Kemp, May 09 '19 at 07:05
Even If I disable Identity it's say that Cannot insert the value NULL into column 'ProduzenaGarancijaID', table 'Triglav.dbo.Garancija'; column does not allow nulls. INSERT fails. The statement has been terminated. — , May 09 '19 at 07:10

score 1 · Answer 1 · answered May 13 '19 at 11:55

After a couple of days checking hold application I notice error in my View

<div class="form-group text-center">
            <input type="submit" class="btn btn-primary" asp-asp-route-id="@Model.Garancija.Id" value="Izmijeni" />
            <a asp-action="Index" class="btn btn-success">Nazad</a>
        </div>

In some reason asp-asp-route-id="@Model.Garancija.Id" I dint check that I have double asp-asp that's why I get error Thank you guys however ! ! !

akg179 · Accepted Answer · 2019-05-09T08:06:06.760

0

When 'IDENTITY_INSERT' is set to 'OFF', we cannot explicitly insert data to identity column.

In order to insert data into identity column, we need to set 'IDENTITY_INSERT' to 'ON' for that table.

Also, while explicitly inserting data to identity column, we need to keep in mind that the column will be a Primary Key of the table as well. Which means that neither null nor duplicate values will be allowed to be inserted explicitly to that column.

If working on Entity Framework, you can try these two options to explicitly insert data into identity column:

execute below code where you are calling _db.Update(garancija) in the Edit method

_db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Garancija ON");
_db.Update(garancija);
_db.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Garancija OFF");
await _db.SaveChangesAsync();

For the Key column i.e. ProduzenaGarancijaID in the model class, you can set the attribute [DatabaseGenerated(DatabaseGeneratedOption.None)]

[DatabaseGenerated(DatabaseGeneratedOption.None)]    
public int ProduzenaGarancijaID { get; set; }

edited May 09 '19 at 08:06

answered May 09 '19 at 07:22

akg179

1,287
1
6
14

SET IDENTITY_INSERT [dbo].[Garancija] ON You mean like this or .. ? – May 09 '19 at 07:26
After I set this to ON again same problem – May 09 '19 at 07:38
Again same error Cannot insert explicit value for identity column in table 'Garancija' when IDENTITY_INSERT is set to OFF. – May 09 '19 at 07:44
did u update database after setting IDENTITY_INSERT to ON? – akg179 May 09 '19 at 07:45
Sure, and also Add-Migration – May 09 '19 at 07:47
edited my answer to provide some code which helps in inserting data explicitly to identity columns. Hope it helps. – akg179 May 09 '19 at 08:07

score 0 · Answer 3 · answered May 09 '19 at 07:52

0

set identity_insert [tablename] on
insert into [tablename] (columnlist) values(valuelist)
set identity_insert [tablename] off

answered May 09 '19 at 07:52

Fleshy

182
7

I have no idea is it good to delete table and create again. Will someting change ?? Exactly like this try a couple of times and nothing happened – May 09 '19 at 07:56
Are you creating this table with Code First? – Fleshy May 09 '19 at 07:59
Yes, I did using EF – May 09 '19 at 08:01
The model looks good, and matches the table...very odd. Does the exception occur on this line: await _db.SaveChangesAsync(); – Fleshy May 09 '19 at 08:03
Yes Exactly in _db.SaveChangesAsync(); I updated my question – May 09 '19 at 08:06
Adding this "[DatabaseGenerated(DatabaseGeneratedOption.Identity)]" will make no difference....the [Key] attribute defaults to identitiy. It concerns me that the debugger does not jump into that code block...have you figured that out yet? – Fleshy May 09 '19 at 08:07
After adding this cople of code I get this error Violation of PRIMARY KEY constraint 'PK_Garancija'. Cannot insert duplicate key in object 'dbo.Garancija'. The duplicate key value is (0). The statement has been terminated. – May 09 '19 at 08:15
I belive that GarancijaVM make problem, because I add VM for drop down, and In my form there are a couple of drop downs which most probably cause the error – May 09 '19 at 08:18
This is a tough one...for some reason EF is not recognising that your table demands Identity. Maybe try rebuilding the table. – Fleshy May 09 '19 at 10:48

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

3 Answers3