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

ahmed salah

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?

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Naimish Makwana2y ago
You’re not explicitly setting the ID in your InsertZebraSubPrinters method. However, the issue might be arising due to the way Entity Framework handles entities. When you create a new instance of ZebraSubPrinters, the ID property is automatically initialized to its default value, which is 0 for a decimal type. When you try to save this entity, Entity Framework might be trying to insert 0 into the ID column, which is not allowed because it’s an identity column.

To resolve this, you can try making the ID property nullable in your ZebraSubPrinters class:
```
public class ZebraSubPrinters
{
    public decimal? ID { get; set; }
    // Other properties...
}
```
By making ID nullable, when you create a new ZebraSubPrinters instance without setting ID, its value will be null instead of 0. When Entity Framework sees null, it knows not to include ID in the INSERT statement, and SQL Server will automatically generate a value for it.

Thanks

Jaimin Shethiya2y ago

Hello,

You need to manually assign the ID value.

public void InsertZebraSubPrinters(ZebraSubPrinters printer)
{
    ZebraSubPrinters ZEB = new ZebraSubPrinters();
    ZEB.ID = 11; // fetch the max number and add by 1 and associate that value here.
    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();
}

Thanks

Tahir Ansari3y ago
this occurs because you're trying to explicitly insert a value into the identity column ID of the ZebraSubPrinters table. In your code, the InsertZebraSubPrinters function is creating a new instance of ZebraSubPrinters and setting various properties, including the ID property.

Since the ID column is defined as an identity column, SQL Server automatically generates its value when you insert a new row. You should not provide a value for the ID column during insertion.
Tahir Ansari3y ago
SET IDENTITY_INSERT ZebraSubPrinters ON