Query not attempting to insert into identity column but getting error : Cannot insert explicit ... identity column ... when Identity_Insert is off

Question

I am simply attempting to insert a new record into our SQL Server database. The insert is happening from within a WCF web service using LINQ.

The table d_coord_report_conflict is structured as follows:

conflict_report_id int NOT NULL IDENTITY PRIMARY KEY,
booking_id int,
conflict_date date,
coord_user_id int
guide_one_user_id int,
guide_two_user_id int,
product_id int,
price_id int

Everything is a foreign key except conflict_report_id and conflict_date. Here's the insert from the WCF service in C#:

d_coord_report_conflict conflict = new d_coord_report_conflict
{
         booking_id = bookingID,
         conflict_date = date,
         coord_user_id = Convert.ToInt32(item.coordID),
         guide_one_user_id = pax.user_id_guide,
         guide_two_user_id = Convert.ToInt32(item.guideID),
         product_id = Convert.ToInt32(item.productID),
         price_id = Convert.ToInt32(item.priceID)
};

db.d_coord_report_conflicts.InsertOnSubmit(conflict);
db.SubmitChanges();

It should be a straight forward insert but it keeps throwing back the following error:

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

As far as I can see this shouldn't be happening. Also, turning on IDENTITY_INSERT is not an acceptable solution as I'm sure you understand.

UPDATE: I recovered the values of each field is it is passed into the LINQ query and attempted a manual insert from within SQL Server Management Studio and it worked fine. no errors at all. Any ideas?

Thanks in advance.

related: http://stackoverflow.com/questions/637121/linq-to-sql-why-am-i-getting-identity-insert-errors — J Cooper, Feb 29 '12 at 15:30

score 0 · Answer 1 · answered May 15 '12 at 10:17

What query have u written in method db.d_coord_report_conflicts.InsertOnSubmit(conflict)? Whether it is stored proc, or an inline sql query in c#, check for correct column names and order. remember: you must not pass value/mention name of identity column in INSERT query.

if your table is like this

Create table Test 
{   
     testId int not null identity primary key,   
     testName varchar(50),   createdDate datetime 
}

Your INSERT query should be something like

INSERT INTO TEST(testName,createdDate) values ('New Test', getdate())

observe that i have not mentioned the identity column anywhere in the insert query, since its value is automatically filled Hope this makes sense!

Query not attempting to insert into identity column but getting error : Cannot insert explicit ... identity column ... when Identity_Insert is off

1 Answers1