how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.

My question is how to get IDENTITY_INSERT Incriment Primary Key ID roll back when the application fails.

Using TransactionScope with single connection in DataObject. I am trying to insert row in two dataTable using its own tableAdapter (two tableAdapter).

I have Product table with ProductID primary key with incriment identity. and that ProductID is used to insert row in ProductHistory Table. Lets say Product table has the last ProductID=8 (8 rows) and the next ProductID will be 9.

When I insert row in both table and if the second table insert fails both gets roll back (which is good). but when I insert again another time the Product ID=10 not 9. Is there any way to roll back the ProductID in Product table so when i insert next time it has incriment number instead of gap.

That is the nature of IDENTITY column. The number once assigned to a row is gone whether the transaction succeeds or not. Even if the transaction succeeds, if you delete the row, the number is not re-assigned. Hopefully your application is based only on the PK-FK relationship and not on the serial order of the data.

|||

Thats what I thought but thought there might be a way I guess not, My table relation is based on PK-FK . It looks odd when some one is viewing Product table and has a gap in Product ID in between product (further someone might have question or confuse). I guess I have to manually create Product ID Primary Key data.

Thank for your answer

|||

Just out of curiosity, why is the ProductId important? ProductId should just be a PK to identify the product, it should not matter if there are gaps.

|||

For Developer standpoint it shouldnt matter at all, For other users, they would like to see incriment by 1. The product page displays Product ID, Product Name, Product Insert Date. So if they will see gap in Product ID inbetween they will have question and trust me they dont want that way, I have dealt in similar situation before.

|||

So why is productId even shown? Do users care?

|||

Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID

|||

rumax96:

Oh Yes , that is how they identify the product. Product ID is more important then Product Name. Product ID is the key when communicating with anyone and used all over the page. Product come and go. It is very hard to keep up with their name. They find easy with Product ID

Then a gap won't matter - unless your users remember that the product they want appeared 4 products further down a page than product 26, and therefore assume they want product 30. I know users can be extremely odd, but that seems to me to be pushing it a bit.

|||

Thanks Mike, I understand , From a developing perspective it doenst make sense wether there is a gap or not in Primary key field. The maing thing is user dont like gap in their ProductID and If that is the way the users wants then We have no choice. Like I said I have deal before in similar situation.