How can I get the value of an autoincremented column when adding new rows to a DataTable?

  • Thread starter Thread starter 0to60
  • Start date Start date
0

0to60

In the following situation:

System.Data.DataRow row = dataTable.NewRow();

row["someField"] = someValue;
// Set some more fields

dataTable.Rows.Add(row);

dataAdapter.Update(dataTable);



If the primary key of my table is an autoincrementing number, can I get to
it from here?
 
See my whitepaper "Handling an Identity Crisis" on my website.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
 
Assuming you are using SQL Server, getting the autoincrement key back is
really trivial.

Dataset
--------

set the primary key field's AutoIncrementStep and AutoIncrementSeed both
to -1

The adapter's insert statement
------------------------------
Add code to the end of the insert statement to get back the autoincrement
key

INSERT INTO [dbo].[AccessFlagLookup] ([AccessFlag], [Description],
[LastUpdatedBy]) VALUES (@AccessFlag, @Description, @LastUpdatedBy);SELECT
AccessFlagLookupID FROM dbo.AccessFlagLookup WHERE (AccessFlagLookupID =
SCOPE_IDENTITY())

Trap RowUpdated event for inserts
------------------------------------

/* Skip current row for statement type insert. Necessary to prevent adding a
new row for
* each insert instead of changing the autoincrement primary key */
foreach (System.Data.SqlClient.SqlDataAdapter da in adapterList)
{
da.RowUpdated += new
System.Data.SqlClient.SqlRowUpdatedEventHandler(da_RowUpdated);
}

static void da_RowUpdated(object sender,
System.Data.SqlClient.SqlRowUpdatedEventArgs e)
{
if (e.StatementType == System.Data.StatementType.Insert) e.Status =
System.Data.UpdateStatus.SkipCurrentRow;
}

This methodology works great with SQL Server. For other database, such as
Sybase's SQL Anywhere, it's a little more complicated but very doable.
 
I can't find it anywhere on your site. Can you send me a link?


William (Bill) Vaughn said:
See my whitepaper "Handling an Identity Crisis" on my website.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
-----------------------------------------------------------------------------------------------------------------------

0to60 said:
In the following situation:

System.Data.DataRow row = dataTable.NewRow();

row["someField"] = someValue;
// Set some more fields

dataTable.Rows.Add(row);

dataAdapter.Update(dataTable);



If the primary key of my table is an autoincrementing number, can I get
to it from here?
Click to expand...
Click to expand...
 
http://www.betav.com/Files/Content/whitepapers.htm "Handling an Identity
Crisis"

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
-----------------------------------------------------------------------------------------------------------------------

0to60 said:
I can't find it anywhere on your site. Can you send me a link?


William (Bill) Vaughn said:
See my whitepaper "Handling an Identity Crisis" on my website.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
-----------------------------------------------------------------------------------------------------------------------

0to60 said:
In the following situation:

System.Data.DataRow row = dataTable.NewRow();

row["someField"] = someValue;
// Set some more fields

dataTable.Rows.Add(row);

dataAdapter.Update(dataTable);



If the primary key of my table is an autoincrementing number, can I get
to it from here?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top