D
Don MacKenzie
Hi,
I am getting these "Value is already present" errors occasionally on
my autoincrement columns. I am not doing anything special here, just
using the code that is generated by the Visual Studio designer. I
have tried autoincrement seeds on the table of both 1 and 100, but it
doesn't make any difference. Most of the time, it works, but every
once in a while, I get this problem... usually when the table is
"young" but not necessarily on the first inserts.
I am 99% certain that SQL Server is not the source of the fault.
The fault, I believe, is with the SQL generated by Visual Studio
and/or the manner in which ADO.NET handles the merging of the INSERT
results into the original table. If you look at the SQL statement
generated by Visual Studio, you will see that it SELECTS the results
of the insert. I believe that Visual Studio is immediately attempting
to merge the results into the source dataset, so if there are still
rows in the dataset waiting to be inserted and the IDENTITY result of
the first insert happens to coinicide with the "Fake" ADO.NET identity
in one of the pending insert rows, then ADO.NET throws an exception.
This would be a bug in ADO.NET, in my opinion.
I do not want to have to customize the INSERT statements for all of my
SQL commands. This would be a lot of work, and also essentially
renders the Visual Studio designer useless.
My question is: Is there a known workaround for this problem? I have
seen this suggested:
http://groups.google.ca/groups?hl=e...ff&[email protected]
....but you would have to remember to repeat this step every time you
regenerated the dataset, not good.
The only other idea is temporarily turning off constraints during the
insert... would it work?
See code examples below... names have been changed to protect the
identities of the innocent. FYI, the dataset is being generated by a
client app, the data updates are done by an EnterpriseServices
component (COM+). Otherwise, this is 100% plain-jane Visual Studio
generated code.
Me.SqlInsertCommand2.CommandText = "INSERT INTO X(Y_Id, Z_Id) VALUES
(@Y_Id, @Z_Id); SELECT X_Id, Y_Id, Z_Id, FROM X WHERE (X_Id =
@@IDENTITY)"
Column 'X_Id' is constrained to be unique. Value '115' is already
present.
....
<xs:element name="X_Id" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int" />
....
CREATE TABLE [X] (
[X_Id] [int] IDENTITY (100, 1) NOT NULL ,
[Z_Id] [int] NOT NULL ,
[Y_Id] [int] NOT NULL ,
....
CONSTRAINT [PK_X] PRIMARY KEY NONCLUSTERED
(
[X_Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_X_Z_Y] FOREIGN KEY
(
[Z_Id],
[Y_Id]
) REFERENCES [X_Y] (
[Z_Id],
[Y_Id]
) ON DELETE CASCADE
) ON [PRIMARY]
I am getting these "Value is already present" errors occasionally on
my autoincrement columns. I am not doing anything special here, just
using the code that is generated by the Visual Studio designer. I
have tried autoincrement seeds on the table of both 1 and 100, but it
doesn't make any difference. Most of the time, it works, but every
once in a while, I get this problem... usually when the table is
"young" but not necessarily on the first inserts.
I am 99% certain that SQL Server is not the source of the fault.
The fault, I believe, is with the SQL generated by Visual Studio
and/or the manner in which ADO.NET handles the merging of the INSERT
results into the original table. If you look at the SQL statement
generated by Visual Studio, you will see that it SELECTS the results
of the insert. I believe that Visual Studio is immediately attempting
to merge the results into the source dataset, so if there are still
rows in the dataset waiting to be inserted and the IDENTITY result of
the first insert happens to coinicide with the "Fake" ADO.NET identity
in one of the pending insert rows, then ADO.NET throws an exception.
This would be a bug in ADO.NET, in my opinion.
I do not want to have to customize the INSERT statements for all of my
SQL commands. This would be a lot of work, and also essentially
renders the Visual Studio designer useless.
My question is: Is there a known workaround for this problem? I have
seen this suggested:
http://groups.google.ca/groups?hl=e...ff&[email protected]
....but you would have to remember to repeat this step every time you
regenerated the dataset, not good.
The only other idea is temporarily turning off constraints during the
insert... would it work?
See code examples below... names have been changed to protect the
identities of the innocent. FYI, the dataset is being generated by a
client app, the data updates are done by an EnterpriseServices
component (COM+). Otherwise, this is 100% plain-jane Visual Studio
generated code.
Me.SqlInsertCommand2.CommandText = "INSERT INTO X(Y_Id, Z_Id) VALUES
(@Y_Id, @Z_Id); SELECT X_Id, Y_Id, Z_Id, FROM X WHERE (X_Id =
@@IDENTITY)"
Column 'X_Id' is constrained to be unique. Value '115' is already
present.
....
<xs:element name="X_Id" msdata:ReadOnly="true"
msdata:AutoIncrement="true" type="xs:int" />
....
CREATE TABLE [X] (
[X_Id] [int] IDENTITY (100, 1) NOT NULL ,
[Z_Id] [int] NOT NULL ,
[Y_Id] [int] NOT NULL ,
....
CONSTRAINT [PK_X] PRIMARY KEY NONCLUSTERED
(
[X_Id]
) ON [PRIMARY] ,
CONSTRAINT [FK_X_Z_Y] FOREIGN KEY
(
[Z_Id],
[Y_Id]
) REFERENCES [X_Y] (
[Z_Id],
[Y_Id]
) ON DELETE CASCADE
) ON [PRIMARY]