sqldatasource insert return identity value

Question

Im using SqlDataSource sds = new SqlDataSource(); in code behind and inserting using sds.Insert(); Please tell me how to get inserted record primary key value? Please note im not using stored procedure.

Answer 1

Last_Insert_ID();

Gives you the last primary key id, you can simply append this on the end of your current insert and the key value will be returned from your insert.

here is a C# example:

tring sqlIns = "INSERT INTO table (name, information, other) VALUES (@name, @information, @other)";
db.Open();
try
{
 SqlCommand cmdIns = new SqlCommand(sqlIns, db.Connection);
 cmdIns.Parameters.Add("@name", info);
 cmdIns.Parameters.Add("@information", info1);
 cmdIns.Parameters.Add("@other", info2);
 cmdIns.ExecuteNonQuery();

 cmdIns.Parameters.Clear();
 cmdIns.CommandText = "SELECT @@IDENTITY";


 // Get the last inserted id.

 int insertID = Convert.ToInt32( cmdIns.ExecuteScalar() );


 cmdIns.Dispose();
 cmdIns = null;
}
catch(Exception ex)
{
throw new Exception(ex.ToString(), ex);
}
finally
{
db.Close();
}

I found this at MSDN:

http://social.msdn.microsoft.com/forums/en-US/csharpgeneral/thread/91bd10ce-c83b-4766-920b-2137ddc29908

Answer 2

I know this is a rather old post but in case someone lands here contemporaneously ...

Regarding Michael Eakins answer (see above), it is safer to use SCOPE_IDENTITY() rather than @@IDENTITY.

Per MSDN SCOPE_IDENTITY

"... SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope."