Re: Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- From: "Pablo Castro [MS]" <pablocas@xxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 11 May 2005 19:59:37 -0700
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" <nadersoliman@xxxxxxxxx> wrote in message
news:1115739156.582691.122460@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>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/microsoft.public.dotnet.framework.adonet/browse_thread/thread/35490dc7910449b6/24235e15b88392a4?hl=en#24235e15b88392a4
>
>
> Nader O. Soliman
> Freelance Software Engineer
> Web & Desktop Development
>
.
- Follow-Ups:
- Re: Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- From: Nader Soliman
- Re: Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- References:
- Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- From: Nader Soliman
- Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- Prev by Date: Re: using Integrated Security SSPI with User ID
- Next by Date: Re: Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- Previous by thread: Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- Next by thread: Re: Mysterious Identity problem between SQL & ADO.NET (A BUG for MS)
- Index(es):
Relevant Pages
|
