CODEWITHSUNDEEP
.netinsertreturnsqldatasource
hotcoder
hotcoder

Reputation: 3246

sqldatasource insert return identity value

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.

Upvotes: 0

Views: 2982

Answers (2)

InTech97
InTech97

Reputation: 31

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."

Upvotes: 1

Michael Eakins
Michael Eakins

Reputation: 4179

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

Upvotes: 1

Related Questions