Warm tip: This article is reproduced from serverfault.com, please click

Error "Cannot insert explicit value for identity column in table" using C#

发布于 2020-11-28 07:47:09

I'm having an issue with a simple local SQL database. I'm not very knowledgeable with databases or accessing them using C#, so any help would be appreciated. I'm trying to add an entry to one of the tables in my database using C# through Linq to SQL. Here is my a snippet of the code.

using (DataClasses1DataContext db = new DataClasses1DataContext())
{
    tbl_Inventory inv = new tbl_Inventory();
    inv.Title = addTitleTextBox.Text;
    inv.Model = addModelTextBox.Text;
    inv.Category = addCategoryTextBox.Text;
    inv.Quantity = int.Parse(addQuantityTextBox.Text);
    inv.Price = decimal.Parse(addPriceTextBox.Text);
    inv.Description = addDescriptionTextBox.Text;

    db.tbl_Inventories.InsertOnSubmit(inv);
    db.SubmitChanges();

    int id = inv.IdProducts;

    MessageBox.Show($"Item creation successful. Item number is {id}");
}

I keep getting the following error:

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

My table has a column called IDProducts which is used as the identity column to increment by 1. I can add entries within the Visual Studio design window and it adds the entry with no errors with the increments working correctly, but not when I run my code. The thing is my code is not trying to assign a value to IDProducts. I tried deleting IDProducts and adding it back, but I still get the same error. I have one other table I created through Visual Studio the exact same way with very similar code to that above which adds entries to the table and I have no issues. I'm not sure what I might have done differently.

Questioner
Nolan Melhart
Viewed
0
marc_s 2020-11-28 15:58:31

It appears as if your C# model class doesn't properly define the IDProducts column as an IDENTITY column - and thus your Linq-to-SQL code tries to insert a value into the identity column, which causes this error.

You need to make sure to properly annotate your column - since I don't know what your tbl_Inventory class looks like, I can only show you this:

[Table(Name="Inventory")]     // or whatever it really is ...
public class tbl_Inventory   
{
    [Column(IsPrimaryKey=true,IsDbGenerated=true)]
    public int IDProducts { get; set; }

    // all your other columns here
}  

You need to add the IsDbGenerated annotation to your IDProducts column so that Linq-to-SQL knows that this is a column where the value is being generated by the database, upon insert.