Identity Problem ...Known solution doesnt work

[bijoy]

We are trying to sort out the issue regarding updating
the identity column value in DataTable after an insert to
DB. Although several examples including the one below
seem to profess that it will work , the below approach
seems to work only in theory

The below sniippet doesn't seem to update the row as
professed in all the technical articles both on msdn and
the web

Although an alternative approach suggests that we
subscribe to RowUpdated event of the DataAdapter

Every material which we posses seem to suggest this isnt
necessary for SQL 2000 (which we are using ) and is only
required for MS ACCESS

I have included the code which to test the same ( Pubs ,
Jobs Table) , U may test the below code by binding what
it returns to a datagrid

Any alternative suggestions , workarounds would be highly
regard

Kind regards
Bijoy

public static DataSet test()
{
DataSet ds = new DataSet();
SqlDataAdapter da = new
SqlDataAdapter(
"select * from jobs",

"server=localhost;uid=sa;database=pubs");

//populate the dataset;
da.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
da.Fill(ds);

//change the identity seed and
increment so that we are not mislead that code actually
is getting value //from db;

ds.Tables[0].Columns
["job_id"].AutoIncrementSeed =39;
ds.Tables[0].Columns
["job_id"].AutoIncrementStep =-1;

//create the row
ds.Tables[0].Rows[1][1] = "newer
description";
ds.Tables[0].Rows.Add(
new object[4] {
null, "new row", 40, 40 });

DataSet ds2 = new DataSet();
ds2 = ds.GetChanges();

SqlCommandBuilder bld = new
SqlCommandBuilder(da);
da.InsertCommand =
bld.GetInsertCommand();
da.UpdateCommand =
bld.GetUpdateCommand();
da.DeleteCommand =
bld.GetDeleteCommand();

// make sure we get the identity
column on insert
da.InsertCommand.CommandText +=
";select * from jobs
where job_id = @@identity";
da.InsertCommand.UpdatedRowSource
=

UpdateRowSource.FirstReturnedRecord;

//save to the database the
changes to the dataset
da.Update(ds2);

// dataset should show now
contains latest changes (The id generated by the DB
Engine) which it doesnt
ds.Merge(ds2);
return ds;
}

 

[Tim]

Sorry, I can't follow your code.

Two things:

1. I would suggest you question why you have an identity column if you
require to insert specific values. Under normal circumstances you only ever
read an identity column and let the DBMS manage it for you - that is what it
is for. Under rare circumstances EG a data load for a production database
you may need to set the identity to a specific value when loading say a
reference list - even then you should question your design.

2. If you want to insert specific identity values it is easy in SQL:

For SQL Server - ignore for other DBMS.

Syntax
SET IDENTITY_INSERT [ database. [ owner. ] ] { table } { ON | OFF }

EG.

SET IDENTITY_INSERT table-name ON
INSERT INTO table-name (idcolumn, ...column list)
VALUES(idvalue, rest of column values....)
....
more inserts
....
SET IDENTITY_INSERT table-name OFF

The above does work.

SQL such as this can be executed extremely easily using ADO.

Don't forget to turn off the identity insert when finished or you will
regret it.

Perhaps there is an easy way for you to make your own code work...

Note:

If you have any type of unique index or constraint on the identity column it
will fail on insert if you attempt to insert a duplicate - this is the
purpose of the index (may be a primary key) and is in addition to the
identity...

Last I new, you could not reseed or alter the increment value of the
identity. If you can through ADO then neat - I would like to hear back.

- Tim

We are trying to sort out the issue regarding updating
the identity column value in DataTable after an insert to
DB. Although several examples including the one below
seem to profess that it will work , the below approach
seems to work only in theory

The below sniippet doesn't seem to update the row as
professed in all the technical articles both on msdn and
the web

Although an alternative approach suggests that we
subscribe to RowUpdated event of the DataAdapter

Every material which we posses seem to suggest this isnt
necessary for SQL 2000 (which we are using ) and is only
required for MS ACCESS

I have included the code which to test the same ( Pubs ,
Jobs Table) , U may test the below code by binding what
it returns to a datagrid

Any alternative suggestions , workarounds would be highly
regard

Kind regards
Bijoy

public static DataSet test()
{
DataSet ds = new DataSet();
SqlDataAdapter da = new
SqlDataAdapter(
"select * from jobs",

"server=localhost;uid=sa;database=pubs");

//populate the dataset;
da.MissingSchemaAction =
MissingSchemaAction.AddWithKey;
da.Fill(ds);

//change the identity seed and
increment so that we are not mislead that code actually
is getting value //from db;

ds.Tables[0].Columns
["job_id"].AutoIncrementSeed =39;
ds.Tables[0].Columns
["job_id"].AutoIncrementStep =-1;

//create the row
ds.Tables[0].Rows[1][1] = "newer
description";
ds.Tables[0].Rows.Add(
new object[4] {
null, "new row", 40, 40 });

DataSet ds2 = new DataSet();
ds2 = ds.GetChanges();

SqlCommandBuilder bld = new
SqlCommandBuilder(da);
da.InsertCommand =
bld.GetInsertCommand();
da.UpdateCommand =
bld.GetUpdateCommand();
da.DeleteCommand =
bld.GetDeleteCommand();

// make sure we get the identity
column on insert
da.InsertCommand.CommandText +=
";select * from jobs
where job_id = @@identity";
da.InsertCommand.UpdatedRowSource
=

UpdateRowSource.FirstReturnedRecord;

//save to the database the
changes to the dataset
da.Update(ds2);

// dataset should show now
contains latest changes (The id generated by the DB
Engine) which it doesnt
ds.Merge(ds2);
return ds;
}