6

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:

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

Table Definition

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
bendr
  • 2,415
  • 6
  • 19
  • 24
  • The system does allow repeat customers. The orders table is only used for temporarily storing information. – bendr Jul 11 '11 at 05:07
  • 1
    Sorry, this data model isn't making a lot of sense to me... – OMG Ponies Jul 11 '11 at 05:08
  • This data model is based on a clients existing Access Database he has. I'm sorry it doesn't make any sense, and I know it needs to be changed, but that's not something he's willing to change right now, as he is "too busy" with other work stuff. – bendr Jul 11 '11 at 06:02
  • Actually, since I'm bored as hell most days, I think I might see about changing it myself if he'll let me. – bendr Jul 11 '11 at 06:22
  • @OMG - No need to be sorry. :) I appreciate all the help you've given me over the months – bendr Jul 11 '11 at 06:23

6 Answers6

12

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • I understand that. But, I also have another table with the same definition. And I can insert into that no problems. – bendr Jul 11 '11 at 05:03
  • I'm not creating a new id. I am inserting an existing one. – bendr Jul 11 '11 at 05:04
  • 2
    @jase - No, you don't. It is obviously not the same definition, otherwise you would get the same error. There must be a difference. – Erik Funkenbusch Jul 11 '11 at 05:04
  • 1
    @jase - as far as *that table* is concerned, it doesn't care whether the number exists somewhere else. Re "another table with the same definition" - then *either* that table doesn't have the `IDENTITY`, or you already have `IDENTITY_INSERT` enabled on that table. – Marc Gravell Jul 11 '11 at 05:05
  • 1
    @jase - If you are inserting an existing ID, then the column should not be defined as an Identity field. An Identity field auto-generates it's ID and does not (normally) allow insertion. – Erik Funkenbusch Jul 11 '11 at 05:06
3

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.

Ash Eldritch
  • 1,504
  • 10
  • 13
2

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.

Erik Funkenbusch
  • 92,674
  • 28
  • 195
  • 291
1

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

Sai Kalyan Kumar Akshinthala
  • 11,704
  • 8
  • 43
  • 67
  • 1
    you *can*, and the error message already tells you *how*... changing the `IDENTITY` is not to be taken lightly - of course it is entirely possible it is *wrong* at the moment (but that is different to blindly removing it) – Marc Gravell Jul 11 '11 at 05:10
1

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.

Syeda
  • 1,215
  • 11
  • 23
0

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

Chris P
  • 1