title: Possible to retrieve IDENTITY column value on insert using SqlCommandBuilder (without using Stored Proc)?
body: <p>FYI: I am running on dotnet 3.5 SP1</p>
  
  <p>I am trying to retrieve the value of an identity column into my dataset after performing an update (using a SqlDataAdapter and SqlCommandBuilder).
  After performing SqlDataAdapter.Update(myDataset), I want to be able to read the auto-assigned value of <code>myDataset.tables(0).Rows(0)("ID")</code>, but it is System.DBNull (despite the fact that the row was inserted).</p>
  
  <p>(Note: I do not want to explicitly write a new stored procedure to do this!)</p>
  
  <p>One method often posted <a href="http://forums.asp.net/t/951025.aspx" rel="noreferrer">http://forums.asp.net/t/951025.aspx</a> modifies the SqlDataAdapter.InsertCommand and UpdatedRowSource like so:</p>
  
  <pre><code>SqlDataAdapter.InsertCommand.CommandText += "; SELECT MyTableID = SCOPE_IDENTITY()"
  InsertCommand.UpdatedRowSource = UpdateRowSource.FirstReturnedRecord
  </code></pre>
  
  <p>Apparently, this seemed to work for many people in the past, but does not work for me.</p>
  
  <p>Another technique: <a href="http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&amp;SiteID=1" rel="noreferrer">http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=619031&amp;SiteID=1</a> doesn't work for me either, as after executing the SqlDataAdapter.Update, the SqlDataAdapter.InsertCommand.Parameters collection is reset to the original (losing the additional added parameter).</p>
  
  <p>Does anyone know the answer to this???</p>