Thursday, March 27, 2008

Identity Column Primary Key Violation in Typed DataSet

Title: Primary Key Violation Exception on Identity Column while Updating via Table Adapter
Issue: Exception occurs when Table Adapter's Update method is called
Level: Advanced
Knowledge Required:
To understand the following solution you must have the knowledge of:
  • Typed DataSets
  • Table Adapters
  • Tables in Database
  • Identity Columns
Identity Column in a Typed DataSet may throw Primary Key Voilation Exception while Updating via Table Adapter. This happens due to the incorrect use of AutoIncreamentSeed and AutoIncreamentStep Properties.

For example:
We have a Typed DataSet called StudentDataSet having a table called StudentDataTable which has an Identity Column i.e. Student_ID

By default DataSet Designer sets following properties for the Identity Column as,
AutoIncreamentSeed = 0
AutoIncreamentStep = 1

So when we add 2 rows in the said table the first row will have Student_ID = 0 and the second Row will have Student_ID = 1

Now if we give this Data Table to Table Adapter for update with the following code as:


The table adapter will insert the first row in Database using Insert Stored Procedure then stored procedure will return the Student_ID which is actually returned by the Database (since this is also an identity column in database) so suppose database returned New Student_ID = 1 and now the table adapter will refresh this row and try to replace the Student_ID = 0 with Student_ID = 1 but at the same time we have a second row in the DataTable having Student_ID = 1 which will cause an exception of violating of Primary Key or Constraint.

So to overcome this issue the simple solution is to set the:
AutoIncreamentStep = -1
this will create the next value to -1 and will never be equal to the value returned by database (if database has an AutoIncreamentStep = 1)

No comments: