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]

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.

 

[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 ?

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

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

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

command.CommandType = System.Data.CommandType.Text;

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

command.Parameters.Add ( new System.Data.SqlClient.SqlParameter (
"@parmName", System.Data.SqlDbType.Int, 0,
System.Data.ParameterDirection.Input, 0, 0, "ColumnName",
System.Data.DataRowVersion.Current, false, null, "", "", "" ) );
.....

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

connection.Open ();

int ID = (int) command.ExecuteScalar ();
}