Basically I have a table that contains hierarchical data.
So I have an id column, and a rollup column which points to the id column for each child's parent.
This table is loaded from the database using GetData() on the TableAdapter then the table's primary key autoincrementseed is set using:
SELECT IDENT_CURRENT('TableName') + 1
New children are created in the hierarchy, then I call Update() on the same TableAdapter.
Most of the time this works perfectly, but occasionally I get the ContraintException Column 'id' is constrained to be unique problem, and it only seems to occur when new rows are added.
Normally, i would just set the autoincrementseed and autoincrementstep to -1 and after updating the id column would get the correct values from the database, but because the rollup column depends on the primary key values this won't work.
I can't apply each new row live either because all the changes as a whole must be reviewed by the user before they are applied to the database.
Any ideas?
EDIT: I should mention that no other changes are coming in to this table from any other source, so there are no concurrency issues here.
ANOTHER EDIT: Today I checked the values in the table before the update method was called and noticed that the problem is related to the new rows not being properly sorted on the primary key.
So for example the rows went, 960,961,963,962 and when 962 was inserted I got the error that 962 already exists. Had the update inserted the rows in order this would not have happened.
Why would the table index the rows not in order by primary key?