ahmed salah

ahmed salah

  • 1.2k
  • 547
  • 63.7k

I get error when insert data to table IDENTITY_INSERT is set to OFF

Aug 20 2023 6:20 AM

I work on entity framework 7 asp.net core razor page . I get exception error when insert

data into ZebraSubPrinters from entity framework to SQL server table

I get exception error SqlException: Cannot insert explicit value for identity column in table 'ZebraSubPrinters' when IDENTITY_INSERT is set to OFF.

table ZebraSubPrinters have ID as identity column

public class ZebraSubPrinters
{
    public decimal ID { get; set; }
    public string BranchCode { get; set; }
    public string DisplayName { get; set; }
    public string Category { get; set; }
    public string PrinterName { get; set; }
    public string PrinterIP { get; set; }
    public bool IsActive { get; set; } = true;
}

generic repository used for insert and save data

public virtual TEntity Insert(TEntity entity)
{
    var result = dbSet.AddAsync(entity).Result.Entity;

    return result;
}
public void Save()
{
    try
    {
        _basecontext.SaveChanges();
    }
    catch (Exception ex)
    {

        throw;
    }
}

Function that do insert and save using generic repository :

public void InsertZebraSubPrinters(ZebraSubPrinters printer)
{ 
    ZebraSubPrinters ZEB = new ZebraSubPrinters();
    ZEB.IsActive = printer.IsActive;
    ZEB.PrinterName = printer.PrinterName;
    ZEB.PrinterIP = printer.PrinterIP;
    ZEB.Category = printer.Category;
    ZEB.DisplayName = printer.DisplayName;
    ZEB.BranchCode = printer.BranchCode;
    aDCSupportUnitOfWork.ZebraSubPrinters.Insert(ZEB);
    aDCSupportUnitOfWork.ZebraSubPrinters.Save();              
}

SQL profiler generated error for insert statement above as below:

exec sp_executesql N'SET IMPLICIT_TRANSACTIONS OFF;
SET NOCOUNT ON;
INSERT INTO [ZebraSubPrinters] ([ID], [BranchCode], [Category], [CreatedBy], [CreatedDate], [DisplayName], [IsActive], [PrinterIP], [PrinterName], [SavedDate], [UpdatedBy], [UpdatedDate])
VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11);
',N'@p0 decimal(18,2),@p1 nvarchar(4000),@p2 nvarchar(4000),@p3 nvarchar(4000),@p4 datetime2(7),@p5 nvarchar(4000),@p6 bit,@p7 nvarchar(4000),@p8 nvarchar(4000),@p9 datetime2(7),@p10 nvarchar(4000),@p11 datetime2(7)',@p0=0,@p1=N'10207',@p2=N'Green',@p3=NULL,@p4='0001-01-01 00:00:00',@p5=N'TANTAWY',@p6=1,@p7=NULL,@p8=NULL,@p9='0001-01-01 00:00:00',@p10=NULL,@p11='0001-01-01 00:00:00'

How to solve and prevent this error from happen when inserting?


Answers (4)