0

I have an import from Access table. That table is Category table, such as: - length - area...etc

That table from access has also an ID field, that sometimes can be 1,2,3,4,5, or sometimes 1,3,4,5... any combination.

I need to insert those values to my own ID field in the Server Table. Thus I tried to use identity insert on. Using this code:

using (var transaction = new TransactionScope())
{
    dbContext.ExecuteCommand("TRUNCATE TABLE tbl_unitCat");

    dbContext.ExecuteCommand("SET IDENTITY_INSERT tbl_unitCat ON");

    foreach (DataRow row in table.Rows)//Rows coming from Access
    {
        tbl_unitCat currDBObj = new tbl_unitCat();
        dbContext.tbl_unitCat.InsertOnSubmit(currDBObj);
        //UnitCatID is the identity field
        currDBObj.UnitCatID = row.ItemArray[0].ToInteger();
        currDBObj.UnitCategory = row.ItemArray[1].ToString();
        currDBObj.rawmat = (bool)row.ItemArray[2];
        currDBObj.purchparts = (bool)row.ItemArray[3];
    }

    dbContext.SubmitChanges();
    dbContext.ExecuteCommand("SET IDENTITY_INSERT tbl_unitCat OFF");
    transaction.Complete();
}

It fails with this error: An explicit value must be specified for the identity column in the ' tbl_unitCat' table when IDENTITY_INSERT is set to ON or when a replication user into a NOT FOR REPLICATION identity column inserts.

The server I use is 2005. Looking at the SO similar posts, I found this MSDN Link

But that does not help me.

Any suggestions?

diiN__________
  • 7,393
  • 6
  • 42
  • 69
Amiga500
  • 5,874
  • 10
  • 64
  • 117
  • You have to make sure that the ID you're adding is not already present in the table otherwise the constraint is getting violated – Chaos Legion May 11 '16 at 10:02
  • Table is always truncated – Amiga500 May 11 '16 at 10:03
  • What about the IDs you're inserting from your "row"s? can you confirm there are no duplicates in that? – Chaos Legion May 11 '16 at 10:06
  • @Kunal when the OP were inserting duplicate IDs, they'd get a different error. – CodeCaster May 11 '16 at 10:08
  • 3
    Entity Framework is ignoring your PK values because it has recorded that the PK is database generated. In other words, it is ignoring the value you assign to `UnitCatID`. See also [How can I force entity framework to insert identity columns?](http://stackoverflow.com/questions/13086006/how-can-i-force-entity-framework-to-insert-identity-columns), [How do I temporarily turn off an IDENTITY column in EF Code First?](http://stackoverflow.com/questions/7151710/how-do-i-temporarily-turn-off-an-identity-column-in-ef-code-first). – CodeCaster May 11 '16 at 10:09

0 Answers0