Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)

  • Thread starter Thread starter Nader Soliman
  • Start date Start date
N

Nader Soliman

I just thought I should share this with you guys:

This an investigation that I conducted the problem of

System.Data.ConstraintException: Column 'XYZ' is constrained to be
unique. Value '1' is already present.

When trying to use SqlDataAdapter to insert a bunch of rows from a
dataset.

The problem was on one database my insert statement worked just fine on
another database it was not working fine and always gave the above
exception.

I usually build my adapters manually and here is what I use

_DBAdapter.InsertCommand = new SqlCommand();
_DBAdapter.InsertCommand.Connection = Connection;
_DBAdapter.InsertCommand.CommandText = @"INSERT INTO tblABC
([XYZ]) VALUES (@xyz,);
SELECT * FROM tblABC WHERE ([ID] = SCOPE_IDENTITY());"

// Rest of code is just the parameters.

The cause of the problem was that my customer usually used "TRUNCATE
TABLE tblABC" which somehow missed with the Identity column (possible
reset the seed or something which has broken an assumption in ADO.NET
dataset ... please MS Guys confirm this).

Now I thought yes ... that might be the UpdateRowSource problem so I
added this line
_DBAdapter.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;

With no avail ... take a look at this URL for a confirmation of this
problem
(http://www.dotnetfun.com/articles/winforms/DataGridIdentityConflictSQLServer.aspx?aid=8)

So I decided to set AutoIncrementSeed and AutoIncrementStep as
indicated by
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp)


I set them in the DataSet schema file as msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" and voila everything is working fine.

Finally, MS Guys out there could explain that bug?

Read this link for some other reports of the problem
http://groups-beta.google.com/group...449b6/24235e15b88392a4?hl=en#24235e15b88392a4


Nader O. Soliman
Freelance Software Engineer
Web & Desktop Development
 
I'm not sure I see this is a bug, assuming I understand your scenario
correctly :)

If the dataset auto-increment column is set to a range that overlaps with
the server range, then the SELECT statement after the INSERT may return a
value for the identity column that might be already present for another row
(may be one that hasn't been sent yet), which was generated by the dataset.
So when the adapter tries to copy the results from the SELECT back into the
DataTable column it violates the constraint.

That's why setting the initial value to -1 in the dataset identity, and
increment to -1 as well, solves the problem. In that case the ranges of
identities for client- and server-generated keys are non-overlapping.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.


Nader Soliman said:
I just thought I should share this with you guys:

This an investigation that I conducted the problem of

System.Data.ConstraintException: Column 'XYZ' is constrained to be
unique. Value '1' is already present.

When trying to use SqlDataAdapter to insert a bunch of rows from a
dataset.

The problem was on one database my insert statement worked just fine on
another database it was not working fine and always gave the above
exception.

I usually build my adapters manually and here is what I use

_DBAdapter.InsertCommand = new SqlCommand();
_DBAdapter.InsertCommand.Connection = Connection;
_DBAdapter.InsertCommand.CommandText = @"INSERT INTO tblABC
([XYZ]) VALUES (@XYZ,);
SELECT * FROM tblABC WHERE ([ID] = SCOPE_IDENTITY());"

// Rest of code is just the parameters.

The cause of the problem was that my customer usually used "TRUNCATE
TABLE tblABC" which somehow missed with the Identity column (possible
reset the seed or something which has broken an assumption in ADO.NET
dataset ... please MS Guys confirm this).

Now I thought yes ... that might be the UpdateRowSource problem so I
added this line
_DBAdapter.InsertCommand.UpdatedRowSource =
UpdateRowSource.FirstReturnedRecord;

With no avail ... take a look at this URL for a confirmation of this
problem
(http://www.dotnetfun.com/articles/winforms/DataGridIdentityConflictSQLServer.aspx?aid=8)

So I decided to set AutoIncrementSeed and AutoIncrementStep as
indicated by
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp)


I set them in the DataSet schema file as msdata:AutoIncrementSeed="-1"
msdata:AutoIncrementStep="-1" and voila everything is working fine.

Finally, MS Guys out there could explain that bug?

Read this link for some other reports of the problem
http://groups-beta.google.com/group...449b6/24235e15b88392a4?hl=en#24235e15b88392a4


Nader O. Soliman
Freelance Software Engineer
Web & Desktop Development
Click to expand...
 
Yes, sure your description is absolutely right, but the question now
turns to.
Why this problem only occur when using TRUNCATE TABLE and didn't happen
before?

I have been using ADO.NET for 3 years now, and I have only seen this
problem 3 times, the first 2 times I couldn't work them out. The third
time I found that this truncate table is the main cause of the problem.

So do you think I should turn this question to SQL Server NG? or you
can help discover the problem, I have searched BOL but coulnd't find a
real difference in truncate that may cause this problem.
 
Back
Top