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.
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.