14

We have recently added a new "level" to our database - added a key "Company_ID" to be above/before the existing ID Identity field in the tables throughout the database.

For example, if a Table had ID then fields, it now has Company_ID, then ID, then the fields. The idea is that this allows ID to auto-increment for each different Company_ID value that is provided to the functionality (Company_ID 1 can have ID 1, 2, 3 etc ; Company_ID 2 can have ID 1, 2, 3, etc).

The auto-increment field remains as ID. An example table is :

    [dbo].[Project](
      [Company_ID] [int] NOT NULL,
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [DescShort] [varchar](100) NULL,
      [TypeLookUp_ID] [int] NULL,
      [StatusLookUp_ID] [int] NULL,
      [IsActive] [bit] NOT NULL,
    CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED 
    (
      [Company_ID] ASC,
      [ID] ASC
    )

Before the Company_ID was introduced, to perform a CREATE, we simply populated the DescShort, TypeLookUp_ID, StatusLookUp_ID and IsActive fields, and left ID to be whatever it was by default, possibly 0.

The record was saved successfully, and ID was auto-populated by the database, and then used to perform a SHOW via a View, and so on.

Now, however, we want to set Company_ID to a specified value, leave ID, and populate the fields as before.

    _db.Project.Add(newProject);
    _db.SaveChanges();

Yes, we want to specify the Company_ID value. We want the ID to be auto-populated, as per before. We are getting the error message :

Cannot insert explicit value for identity column in table "Project" when IDENTITY_INSERT is set to OFF

Is this caused by specifying the Company_ID, or by the ID field? Do you know how we can rectify this issue?

Zong
  • 6,160
  • 5
  • 32
  • 46
and_E
  • 293
  • 1
  • 3
  • 11
  • 1
    The problem with your description of what you want and what you actually have is that the `IDENTITY` specification is ever increasing and you **cannot insert a value to it without `IDENTITY_INSERT` switched on for that field**. You won't get `Company_ID` 1 having `ID` 1, 2, 3 and `Company_ID` 2 having `ID` 1,2,3 etc. You'll get `Company_ID` 1 having `ID` 1,2,3 and `Company_ID` 2 having 4`ID` 4,5,6. This might not be an issue for you, but just to make you aware, `ID` won't care what `Company_ID` has in it and will not change it's behaviour. – Steve Pettifer May 22 '14 at 14:24
  • Interesting. I was shown documentation on "Composite keys" and that was how it was explained. I have tested it just now and can see what you are saying. It is almost as if Company_ID is moot when determining the ID value. To meet that criteria we could do it manually ... – and_E May 22 '14 at 14:41
  • 1
    I see what happened here - all references that I provided to c# and MS Visual Studio in my original question were removed by an edit. I don't think this is solely related to SQL - I understand the error message and it would be straightforward if we were ONLY in SQL, but the fact that the call is from Visual Studio is part of the issue ... – and_E May 22 '14 at 15:11
  • 1
    @steve-pettifer: after further investigation and deliberation on this, "the powers that be" have decided that it is best to leave the `Company_ID` 1 having `ID` 1, 2, 3 and `Company_ID` 2 having `ID` 4, 5, 6. Thank you for bringing this to our attention - the initial investigator on this has been sacked! ;) If our client Companies were to merge (which has occurred to us in the past), having "duplicated" `ID` values in the database would not allow for easy merging of data. This way, we "simply" update the `Company_ID` values for the data and away we go. Thank you again. – and_E May 23 '14 at 01:51
  • Glad you got it sorted! Always a tricky task when you need to expand the identifier for data to include some new level of entity. – Steve Pettifer May 23 '14 at 07:30

10 Answers10

15

The problem is on ID. If you set a field as IDENTITY you can't normally assign it a value - the IDENTITY property marks it as allowing the database to automatically assign an incrementing value to the column.

To solve this problem, either remove the automatic IDENTITY property from ID (if you want to auto-increment it, you can always do this in your handling code - get the highest value in the field, add one to it and then assign that value) or go to the DB and set IDENTITY _INSERT on the table, which temporarily allows you to assign values to IDENTITY fields.

SET IDENTITY_INSERT [yourTableName] ON

--go back and run your C# code>

SET IDENTITY_INSERT [yourTableName] OFF
Bob Tway
  • 9,301
  • 17
  • 80
  • 162
  • I don't understand the difference between what we had and what we now have. We already had IDENTITY on ID, and still have that. Wouldn't the same error have occurred previously ? We are leaving the ID field to whatever it defaults to in C# - and always have done so (INT defaults to 0, right? ) – and_E May 22 '14 at 14:14
  • 1
    Sorry if this is a dumb question, but is your new code mistakenly trying to assign to iD where it should be assigning to Company_ID? If you're using ordinals rather than named fields, it's easily done. – Bob Tway May 22 '14 at 14:20
  • thanks for helping here. We are using Visual Studio 2012 (didn't exactly mention this in my question). I am examining the values being used via debug - NO, ID is 0, Company_ID has the value we want. All other fields have correct/appropriate values as well. It appears that the ID = 0 is a value, but we have not physically set it, ever. – and_E May 22 '14 at 14:25
  • Not a dumb question at all, seems like that is the most likely explanation as **something** is trying to insert into that field :) – Steve Pettifer May 22 '14 at 14:25
  • Ah I think I see the problem here: Doesn't matter what value `ID` has in your app code, if you specify that column in the insert then you're in trouble. Leave the `ID` column out of your `INSERT` and you should be golden. Chances are you have a call to a stored proc or some inline SQL in _db.SaveChanges, yes? – Steve Pettifer May 22 '14 at 14:26
  • That is an interesting concept : how to remove a field from Visual Studio, when using the regular code _db.Project.Add(newProject); then _db.SaveChanges(); as mentioned in the original question ? No, no inline SQL or stored proc. – and_E May 22 '14 at 14:28
  • That's right. If you're working with the field as an integer, then the default value will be zero, even if you never assign to it. Don't assign to ID and you should be fine. – Bob Tway May 22 '14 at 14:29
  • This could be something that is default in Visual Studio, when using composite keys - the second field (ID) could be being passed to the database with value = 0. Previously, when defined as just the ID (with no Company_ID), it may have been automatically excluded from the call. Thanks for the tip. – and_E May 22 '14 at 14:45
  • I will mark THIS as the answer. Thanks to all who pitched in. – and_E May 22 '14 at 15:13
  • PS. After sleeping, I found this, for Visual Studio c# code : `[DatabaseGenerated(DatabaseGeneratedOption.Identity)]`. This (in my words), defined for each of my ID fields, tells Visual Studio that the field is an Identity and to "leave it alone" when sending values to the database. When Company_ID occurs first, and has a value, telling Visual Studio that there is an Identity field elsewhere allows the `_db.Project.Add(newProject);` and then `_db.SaveChanges();` to function as required. – and_E May 23 '14 at 01:39
11

After sleeping, I found this, for Visual Studio c# code : [DatabaseGenerated(DatabaseGeneratedOption.Identity)]. This (in my words), defined for each of my ID fields, tells Visual Studio that the field is an Identity and to "leave it alone" when sending values to the database. When Company_ID occurs first, and has a value, telling Visual Studio that there is an Identity field elsewhere allows the _db.Project.Add(newProject); and then _db.SaveChanges(); to function as required. This part of the answer is for the Visual Studio side of things. I understand the SQL requirements of IDENTIY_INSERT so thanks to @matt-thrower, @steve-pettifer and the others who contributed.

and_E
  • 293
  • 1
  • 3
  • 11
7

What worked for me was a simple EDMX update. As I had set Identity Off before and then changed it to auto. But did not update the Edmx . After updating it worked fine.

Preshma Linet Pereira
  • 350
  • 3
  • 10
4

What worked for me in this instance is to set an attribute on the primary key property in the class:

[DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
public int DepartmentId { get; set; }

IDENTITY_INSERT was already on in my DB.

steveareeno
  • 1,925
  • 5
  • 39
  • 59
1

Remove the Identity from Id or add 

SET IDENTITY_INSERT [dbo].[Project] ON

Normally the ID is inserted without user interference so you are getting the error because you are trying to enter data in it. Setting it on will allow you to enter the data you want OR, simple turn remove the code where you are entering data into the ID unless if that is something you need

Keii
  • 11
  • 2
0

Can you try 2 things and try each seperately?

  • Remove primary key fields for ID and Company_ID
  • Take ID column on the first order
kemals
  • 216
  • 1
  • 7
0

Ok, you have a solution already... but consider to leave ID as unique Primary Key and Company_Id as Foreign Key

Jonmac
  • 1
0

The Increment of your Foreign Key in your SQL table is Set automatic ... So when you want to insert any things, you have to delete this Foreign Key from the code like this exemple.

Sql code: 

CREATE TABLE [dbo].[annexe1](
[cle] [int] IDENTITY(1,1) NOT NULL,
[tarif] [nvarchar](50) NULL,
[libele] [nvarchar](max) NULL)

Asp.Net code:

InsertCommand = "INSERT INTO [annexe6] ([cle], [tarif], [libele]) VALUES (@cle, @tarif, @libele)"

Correction:

InsertCommand = "INSERT INTO [annexe6] ([tarif], [libele]) VALUES (@tarif, @libele)"
Massimiliano Kraus
  • 3,638
  • 5
  • 27
  • 47
Mensinovish
  • 1
  • 3
0

Your ID must be unique. Use some hash for that. (for example GUID)

[Key]
public string Id { get; set; }

public your_constructor()
{
    Id = Guid.NewGuid().ToString();
}
Marci
  • 899
  • 10
  • 13
-2

take a look if you have an autoincrement field in your table. Put autoincrement to NO and than execute your SQL. After re-put autoincrement in that field and syncronyze again.

Fernando
  • 1