TableAdapter Insert and SCOPE_IDENTITY

[Ian Semmel]

The generated INSERT statement in a typed dataset has an INSERT statement
followed by a SELECT statement which gets (I think) the value of the
autoincremented ID.

Question. How do I get hold of this value ?

[Greg Young]

I believe it populates the table with the ID after the insert :)

Cheers,

Greg
"Ian Semmel" <is***********@ NOKUNKrocketcom p.com.au> wrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

The generated INSERT statement in a typed dataset has an INSERT statement
followed by a SELECT statement which gets (I think) the value of the
autoincremented ID.

Question. How do I get hold of this value ?

[Ian Semmel]

Problem is how to know which record in the table is the new one, as the only key
is the ID.

I looked around and found that there were some sql procedures that did it but
with my knowledge of sql somewhat limited I didn't fully understand them.

What I did was put a value in a field prior to the INSERT and then read the
table back in and found the record I needed.

Greg Young wrote:

I believe it populates the table with the ID after the insert :)

Cheers,

Greg
"Ian Semmel" <is***********@ NOKUNKrocketcom p.com.au> wrote in message
news:%2******** ********@TK2MSF TNGP03.phx.gbl. ..

The generated INSERT statement in a typed dataset has an INSERT statement
followed by a SELECT statement which gets (I think) the value of the
autoincrement ed ID.

Question. How do I get hold of this value ?

[Ian Semmel]

Ian Semmel wrote:

The generated INSERT statement in a typed dataset has an INSERT
statement followed by a SELECT statement which gets (I think) the value
of the autoincremented ID.

Question. How do I get hold of this value ?

In case anyone is interested, I finally got this to work

// Copy this code from Dataset.Designe r
string sql = "INSERT INTO ...... "
// BUT replace the SELECT statement in Designer with
+ "SELECT CAST(scope_iden tity() AS int)";

using ( SqlConnection connection = new SqlConnection ( connectionStrin g ) )
{
SqlCommand command = new SqlCommand ( sql, connection );

command.Command Type = System.Data.Com mandType.Text;

// This stuff is in Dataset.Designe r (sort of)
// Copy it changing the command identifier

command.Paramet ers.Add ( new System.Data.Sql Client.SqlParam eter (
"@parmName" , System.Data.Sql DbType.Int, 0,
System.Data.Par ameterDirection .Input, 0, 0, "ColumnName ",
System.Data.Dat aRowVersion.Cur rent, false, null, "", "", "" ) );
.....

command.Paramet ers [ 0 ].Value = ( (int) ( parmValue ) );
.....

connection.Open ();

int ID = (int) command.Execute Scalar ();
}