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

Question

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.

Does this answer your question? [Cannot insert explicit value for identity column in table 'table' when IDENTITY\_INSERT is set to OFF](https://stackoverflow.com/questions/1334012/cannot-insert-explicit-value-for-identity-column-in-table-table-when-identity) — David Brossard, Nov 28 '20 at 07:48
Have a look at this article: https://appuals.com/how-to-fix-the-error-cannot-insert-explicit-value-for-identity-column-in-table-when-identity_insert-is-set-to-off/ — David Brossard, Nov 28 '20 at 07:49
You have to set `Identity` to `true` in your database. share a picture of your table design in SQL server — osman Rahimi, Nov 28 '20 at 07:49
I think the real problem is that your C# model class doesn't properly tell Linq-to-SQL that the column `IDProducts` is the database-generated identity column - can you please show us the C# model class? — marc_s, Nov 28 '20 at 07:52

score 1 · Accepted Answer · answered Nov 28 '20 at 07:58

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.

Thanks for the input. I ended up solving that part. I looked at my DataClasses.dbml and in the IdProducts properties the Auto Generated Value was set to false. Not to sure why it wasn't automatically set that way like my other tables. I set it to true and Auto-Sync to OnInsert and it started working. My MessageBox.Show is executing now, and if I submit multiple entries it will show it is incrementing, but I'm not seeing the data saved to the table and the increment will start over when I exit the program and launch again. I'll troubleshoot some and submit another post If I can't figure it out. — Nolan Melhart, Nov 28 '20 at 08:59

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

1 Answers1