Problems with IDENTITY_INSERT Set to OFF? :-/

I'm trying to insert order details into my DB, and it keeps saying:

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

All I am trying to do is simply insert the users UserId into the UserId column, by using WebSecurity.CurrentUserId - Why is this not working?

I have:

开发者_如何学Pythondbase.Execute("INSERT INTO Orders 
                 (UserId, OrderId, Status) 
               VALUES 
                 (@0, @1, @2)", 
               WebSecurity.CurrentUserId, 
               Session["OSFOID"], 
               "Confirmed");`

So, as you can see, it's pretty simple. But, why won't it work?

My table definition is:

Unless you enable the ability to do identity-insert (by setting identity-insert on for that table), you are NOT ALLOWED to touch that column - the database owns it.

Either enable identity insert briefly, or: don't try to insert the UserId (let the DB create a new id).

As per books online, SET IDENTITY_INSERT:

SET IDENTITY_INSERT Orders ON
INSERT INTO Orders (UserId, OrderId, Status) VALUES (@0, @1, @2)
SET IDENTITY_INSERT Orders OFF

More likely, though: if this is the Orders table, should the identity not be on OrderId ? You'd still have the same problem since you are trying to control the OrderId, of course.

Generally you would not want to insert an integer into a primary key column. You would usually set the column's "Identity" flag to true only where you wanted to have SQL Server set an auto-incrementing integer into this column on insert.

As with Marc Gravell's answer, you can enable identity insert using

SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

But doing this in regular application code is really unhealthy -- you'll end up with concurrency issues and quite likely duplicate identities. Better, don't insert the Order's ID -- let the DB do it for you automatically, and then simply query for the new ID using @@IDENTITY (or better, SCOPE_IDENTITY()).

If for some reason you definitely need to store the user's session id, make this a separate column on the order table, or better still, on a separate User table, with the UserId being a foreign key.

You do not want the UserID to be an Identity, and I also do not think you want the UserID to be the primary key either. More than likely, you want the OrderID to be the primary key, or at best shared primary key with the userid and orderid.

In your table's definition set Is Identity? as False, with out setting that you cant insert a value manually to the UserID

You have set IS Identity to YES that's why now you cant insert value for this column DB will automatically insert it by incrementing values..

And the thing i am seeing you set UserId as the primary key of the table which is wrong Order OoderID should be the primary key of the column.

Set UserID IsIdentify to false and OrderID IsEdentitfy to yes and made it primary key column.

Interestingly I found that when I created a table using a "Select.....Into [new table] from [tables containing data]" I subsequently could not Insert new records, getting the Insert_Identity set to off message. I got around this by deleting the table then using a Create Table script to rebuild it and now have no problems inserting as many new IDs as needed