I've a DataSet with 2 tables inside. Table A is the Master and Table B is the
Detail. I've already set the relation between A and B.

DataSet1.Relations.Add("A2B", result.Tables("A").Columns("ID"),
result.Tables("B").Columns("ID"))

I work with SQL Server 2K and the column A.ID is de IDENTITY. In other
words, it's filled just after INSERT command and I do that through Stored
Procedure which INSERT the row into the table and returns its values back to
the DataSet and to make Merge() method possible.

Everything works fine until I try to INSERT table B itens into the DataBase:

1. change Table A by adding 1 row;
2. change Table B by adding 2 rows;
3. invoke GetChanges() method by creating a second DataSet called
DataSet1_Changes;
3. call the Update(DataSet1_Changes, "A") method to update Table A;
4. receive back the row's information from Stored Procedure result (mainly
ID IDENTITY column).
5. invoke DataSet1.Merge(DataSet1_Changes) to merge DataSet1_Changes A.ID
column into DataSet1 A.ID. Until here everything's OK: The Merge() method has
suscessfully updated DataSet1.

--the problem starts
6. at this step Table B.ID would have the same value as well A.ID, because
I've set a relaton between both, isn't it?

Then thats the problem. When I've A.ID changed it isn't propagating to B.ID.

If you understood my explation, could you guve me same tips to handle this
issue?

Thnaks

Andrei,

    GetChanges and Merge are helpful features, but they're primarily
intended for WebService scenarios where you plan on sending a subset of
your DataSet between different tiers in your application.  GetChanges
returns a new DataSet that contains the changes you requested and omits
(most of) the un-modified rows.  Merge combines DataSets based on the
PrimaryKey for your DataTables.  The main drawback to this overall approach
for auto-increment columns is that the auto-increment column is usually the
PrimaryKey column.  So, if you retrieve the new server-generated
auto-increment values, the values for the PrimaryKey column in the
"updated" DataSet are different from the values in the DataSet that
contains pending changes.  As a result, Merge does not match those rows
together.  There are a few possible solutions.  If you need to use the
GetChanges/Merge approach, please say so in a response on the thread with a
little more information about the scenario and I'll see what I can do to
help.

     If your application communicates directly with your SQL Server
database, don't use GetChanges/Merge.  Many developers use GetChanges/Merge
so they can submit a set of changes at a time, for example they may want to
submit just the inserts for the parent table in a master/detail scenario. 
If that's the type of scenario you're working with, use the overloaded
DataAdapter.Update method that takes an array of DataRows in conjunction
with the DataTable.Select method.  Your code would look something like this:

  DataAdapter.Update(DataTable.Select("", "", DataViewRowState.Added))

If you're able to use this approach rather than GetChanges/Merge, you're
working with one DataSet and you can avoid having to try to merge changes
back into a "main" DataSet.

     I've included a pre-packaged response that talks about the basic
approach with hierarchical DataSets and DataRelations based on
auto-increment columns.  It may answer some questions about how ADO.NET
cascades changes through DataRelations.

     I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights.  You assume all risk for your use.
© 2005 Microsoft Corporation.  All rights reserved.


     This is a fairly common scenario that ADO.NET handles much
better than any of its predecessors.  It may seem complex at
first, but once you've handled the scenario once, it will
hopefully feel more intuitive.


1.)  How do I keep pending parent and children in synch?

     Set the ADO.NET DataColumn's AutoIncrement property to True
and ADO.NET will generate placeholder values for new rows.  The
new values depend on the AutoIncrementStep, AutoIncrementSeed,
and the last value used in the DataTable.  I recommend setting
AutoIncrementSeed and AutoIncrementStep to -1.  These settings
will generate placeholder values of -1, -2, -3, …  There are two
benefits to this approach.  The values won't conflict with any
that actually exist in the database.  The user will not
misinterpret the placeholder value as an actual value from the
database.

     As you add the parent rows and ADO.NET generates placeholder
values, use those placeholder values for your pending child rows.
The DataRelation object will make it easy to go from parent to
child and back, either in code or in bound controls.


2.)  How do I fetch the new key values for the parent rows as I
submit them?

     If you're using SQL Server, this process is actually very
simple.  If you were writing your own queries, you would execute
an "INSERT INTO…" query to insert the new row and then execute a
"SELECT SCOPE_IDENTITY()" query to retrieve the last identity
value generated on that connection.

     The DataAdapter submits changes via its InsertCommand
property.  You can append ";SELECT @@IDENTITY AS MyIDColumn" to
the end of the "INSERT INTO..." query.  (SQL 2000 users should
use "SELECT SCOPE_IDENTITY()..." instead of "SELECT @@IDENTITY". 
See SQL Server Books OnLine for more information on why.)  If
you're building your DataAdapters via Visual Studio .NET's
DataAdapter Configuration Wizard, the wizard will do this for you
automatically.

     If you're writing your code by hand, make sure the
InsertCommand's UpdatedRowSource property is set to Both (the
default) or FirstReturnedRecord.  This property controls whether
the DataAdapter will fetch the row returned by the query and
apply that data to the DataRow object.

     This functionality is possible because SQL Server allows you
to execute a batch of queries that returns rows.  However, not
all databases support this feature.

     If you're working with an Access database, you'll need to go
a slightly different route.  Trap for the DataAdapter's
RowUpdated event and use code to check for a successful insert. 
Execute the "SELECT @@IDENTITY" query using a Command object and
assign the value returned by the query to the appropriate column
and call the DataRow object's AcceptChanges method.  Your code
will look something like this:

Visual Basic .NET:
  Dim da As New OleDbDataAdapter(strSQL, strConn)
  Dim cn As OleDbConnection = da.SelectCommand.Connection
  Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", cn)
  AddHandler da.RowUpdated, AddressOf HandleRowUpdated
  Dim tbl As DataTable = CreateMyDataTable()
  da.Fill(tbl)
  ...
  da.Update(tbl)

  Private Sub HandleRowUpdated(ByVal sender As Object, _
                               ByVal e As
OleDbRowUpdatedEventArgs)
      If e.Status = UpdateStatus.Continue AndAlso _
         e.StatementType = StatementType.Insert Then
          e.Row("OrderID") =
Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString())
          e.Row.AcceptChanges()
      End If
  End Sub

Visual C# .NET:
  OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn);
  OleDbConnection cn = da.SelectCommand.Connection;
  OleDbCommand cmdGetIdentity = new OleDbCommand("SELECT
@@IDENTITY", cn);
  da.RowUpdated += new
OleDbRowUpdatedEventHandler(HandleRowUpdated);
  DataTable tbl = CreateMyDataTable();
  da.Fill(tbl);
  ...
  da.Update(tbl);

  private void HandleRowUpdated(object sender,
OleDbRowUpdatedEventArgs e)
  {
      if ((e.Status == UpdateStatus.Continue) &&
         ((e.StatementType == StatementType.Insert))
      {
          e.Row["OrderID"] =
Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString());
          e.Row.AcceptChanges();
      }
  }

     You can use similar techniques to retrieve server-generated
values from other databases as well.  MySQL developers can use
the "LAST_INSERT_ID()" instead of "@@IDENTITY" to retrieve the
last auto-increment value generated.  Oracle developers can use
"SELECT SequenceName.CURRVAL FROM DUAL" to retrieve the last
value generated for a sequence on the connection.


3.)  How do I cascade the new key values to the child rows before
I submit them?

     This is the simplest part of the process.  When you create a
DataRelation object, ADO.NET will add a ForeignKeyConstraint
object to make sure that child rows match up to a parent row. 
The ForeignKeyConstraint object exposes a UpdateRule property. 
If this property is set to Cascade (the default), ADO.NET will
automatically cascade changes made to the parent down to the
associated child rows.

     So, if you have a DataRelation set up between the DataTables
based on the auto-increment column, and you've set the parent
DataAdapter's InsertCommand to fetch the new auto-increment
values from the database, ADO.NET will cascade the new values
down to the associated child rows automatically.


     I hope this information proves helpful.  For more
information, see Chapter 11 of Microsoft ADO.NET, available
through Microsoft Press.

DataTable.Select() - is it possible to pass rows back to datatable
Stored Procedure with Output parameter
How to create an Empty XML file
Datagrid Current Position
Unable to read image file correctly that was written using vb6.0