Updating Child Table's Key (almost have it)
-
I'm trying to use a SqlDataAdapter to update a dataset containing multiple tables linked by a common parent table. I've set up my relations and ALMOST everything is working except for when my RowUpdated event handler tries to retrieve the identity field. Here's my code (without the try/catch/finally stuff and sanitized field names)
private void ParentRowUpdated(object sender, SqlRowUpdatedEventArgs e) { SqlCommand dbCommand = new SqlCommand("SELECT @@Identity"); SqlConnection cn = new SqlConnection(GetConnString(m_strConnStringName)); using (SqlConnection dbConn = new SqlConnection(GetConnString(m_strConnStringName))) { using (SqlDataAdapter daAdapter = new SqlDataAdapter()) { dbCommand.Connection = e.Command.Connection; daAdapter.SelectCommand = dbCommand; e.Row["p_key"] = Int32.Parse(daAdapter.SelectCommand.ExecuteScalar().ToString()); e.Row.AcceptChanges(); } } }Using @@Identity in my select query does retrieve the correct Identity value. However, when I try using SCOPE_IDENTITY() I get a blank value returned. I don't like using @@Identity because this will be a multi user system so I'm worried about getting the wrong value. Does anyone have any idea of a better way to do this? Thanks!