Multiple Inserts and Identity problem

[Jeronimo Bertran]

Hi,

I have created a data table using SQL Server 2000 which includes an
identity field



[CollectID] [bigint] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[Filename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL


I am adding new rows to a typed dataset and calling the data adapter
Update in order to Insert the new rows to the table.

My insertCommand calls a stored procedure that looks like this.

ALTER PROCEDURE spInsertCollect
(
@Filename varchar(50),
@Identity int OUTPUT
)
AS
INSERT INTO Collect(Filename) VALUES (@Filename);
SET @Identity = @@IDENTITY

RETURN


My InserCommand looks like the following:



// Create the parameters
IDataParameter[] parameters =
{
CreateDataParameter("@Filename", DbType.String, 50, "Filename"),
CreateDataParameter("@Identity", DbType.Int64, 8, "CollectID")
};

parameters[1].Direction = ParameterDirection.Output;

insertCommand = BuildStoredProcedureCommand("spInsertCollect",
parameters);



Now, before updating I set the adapter insertCommand member to the one
described above. Now, if the dataset that I pass to Update has only one
new row, then the above works fine, the new row is added to de table and
the new CollectID value is copied to the CollecID field in the dataset
for that row. However, if the dataset has multiple rows (CollectID are
numbered consecutively starting with 0 on the dataset before updating),
and I call the update method, the first row is added and the CollectID
is copied to the dataset, but the second row is not added to the
database and I get the following exception:

An unhandled exception of type 'System.Data.ConstraintException'
occurred in mydata.dll

Additional information: Column 'CollectID' is constrained to be unique.
Value '8' is already present.


Value 8 is the value that was assigned for the first row in the dataset.

Whan am I doing wrong?

Thanks

Jeronimo Bertran

 

[Kevin Yu [MSFT]]

Hi Jeronimo,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're receiving a ConstraintException
when trying to insert multiple rows to the database using a DataSet. If
there is any misunderstanding, please feel free to let me know.

You're getting this exception because of the following

1. You're adding 9 rows to the DataSet, the Identity in the DataSet is 0 to
8. There are already 7 rows in the database table identity from 1 to 7.
2. Now the first row in the DataSet is being addded to the database, it
obtains a new identity 8 after insertion. The DataAdapter is trying to
assign this value to the DataSet identity. However, a row with identity 8
already exists in the DataSet. Thus, the exception is thrown.

To avoid this, we can try to set both AutoIncrementSeed and
AutoIncrementStep of the identity column in DataSet to -1, so that all the
initial identity in the DataSet are negative values. While all the identity
in the database table are positive. So no conflict is made.

HTH.

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

 

[Cor Ligthert [MVP]]

Jeronimo,

In addition to Kevin however with a slight difference.

Make the identity a "guid" and add it yourself to your datatables.
The autoidentity can give all kind of problems to consider by instance with
relations.

However just as addition.

Cor

 

[Miha Markic [MVP C#]]

Hi Jeronimo,

You should set both AutoIncrementSeed and AutoIncrementStep (of the Id field
in DataTable) to -1.
So the newly generated ids will be negative until saved and won't interfer
with actual ones as it is happending now.

 

[Miha Markic [MVP C#]]

Just like Kevin suggested

 

[Jeronimo Bertran]

Thanks... It worked fine.

 

[Kevin Yu [MSFT]]

You're welcome, Jeronimo.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

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