How i get new ID after inserting data in table through command.ExecuteNonQuery

How i get new ID after inserting data in table through command.ExecuteNonQuery

• Max Andre Bundchen

  There is no magic. If you need to get this identity, then you need to query @@IDENTITY or SCOPE_IDENTITY using SELECT statement and ExeuteNonQuery will not help you. You could use additional call with this SELECT using ExceuteScalar method right after ExecuteNonQuery
  
  

• Subi

  @@identity is limited to the current connection, so you will get the last id of the record that was inserted within the same 'connection' as the connection that you use to do the select @@identity.
  
  You can also use the scope_identity() method if you're using Sql Server. There's a subtle difference between @@identity and scope_identity(), so I'll refer to the Sql Server books online if you want to know what the difference is.
  
  
  

• redplanet

  thanks David...

  
  
  

• AleXXus

  thanks Frederik,

  Actuly my issue is how can i get those value through execuetnonquery with the insert statement. For @@Identy i have to fire another query to DB which i don't want.

  
  
  

• Davettttttttttttttttt

  You can look at this other message in this forum for the answer:

  http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=236419&SiteID=1

  Here is an example from the other message that uses SCOPE_IDENTITY() to get the recently added identity value :

  str = "Insert INTO TblAccount(Name) VALUES (@Name) Set @AccountNumber = SCOPE_IDENTITY()";

  SqlParameter[] sqlParams = new SqlParameters[2];

  sqlParams[0] = new SqlParameter("@Name", SqlDbType.Char);

  sqlParams[0].Value = "whatever";

  sqlParams[1] = new SqlParams("@AccountNumber",...);

  sqlParams[1].Direction = ParameterDirection.Output;

  ...

  cm.ExecuteNonQuery();

  int accountNumber = (int)sqlParams[1].Value; // assuming integer

  I wrote a post in my blog about this because the question gets asked so often:

  Retrieve Identity Column Value After Inserting Record in Database Table

  Hope this helps,

  Dave

  
  
  
• How i get new ID after inserting data in table through command.ExecuteNonQuery