DataGrid Identity Increment Conflicts with MS SQL Server

Problem: Datenbank mit Tabelle wo Primary Key AutoIncrement ist. Also auf Client Seite im DataSet AutoIncrement und auf DB-Server Seite AutoIncrement. Diese 2 Seiten zu synchronisieren! Lösung Updated Link! DataGrid Identity Increment Conflicts with MS SQL Server Lösung weil SQL Server Insert Statement: After the INSERT statement occurs a proceeding SELECT is used to retrieve a 'refreshed' version of the record from the MS SQL Server database As you can see by the image above, after hitting the Save button, the SqlDataAdapter sends the INSERT statement to MS SQL Server and then retrieves an updated version of the record from the database via a SELECT statement. The correct identity value is retrieved from the database (26), but as you can see, it doesn't appear in the DataGrid - the original value is still there! Even though the correctly retrieved identity value is set to the Current version of the DataRow, it is not so in the DataGrid. The Current version contains the correct value. The Original version does not. The DataGrid cell objects bind to the Original version of the DataRow object. So, the key is to update the DataRow's TestID column, in this example, to the correct value so we can see it in the DataGrid control. To do that we need to create an SqlDataAdapter RowUpdated event handler and update the Original version value to that of the Current value like this:

private void sqlDataAdapter1_RowUpdated(object sender, 
 System.Data.SqlClient.SqlRowUpdatedEventArgs e)
 if (e.Row.HasVersion(DataRowVersion.Original) && 
  e.Row.HasVersion(DataRowVersion.Current) &&
  e.Row.RowState != DataRowState.Deleted) 
   int.Parse(e.Row[0, DataRowVersion.Original].ToString())).TestID = 
   int.Parse(e.Row[0, DataRowVersion.Current].ToString());

Latest Posts

Popular Posts