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.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 ();
}