"Geoff" <nodamnspam@email.com> wrote in message
news:uP6RmfFJGHA.3408@TK2MSFTNGP12.phx.gbl...
> Hi
>
> I'm hoping somebody can help me with the following problem that has
occurred
> to me.
>
> Suppose I have two tables in an SQL Server database. Let's call these
tables
> A and B. Assume that A has two fields: a primary key and another holding a
> string. In table B there are three fields: a primary key, a foreign key
> (which links to the primary key in A) and other field holding a string.
>
> Suppose I load these tables into a DataSet. I populate table A with some
> rows. For each of these rows I create some rows in B which are linked by
the
> foreign key to A i.e. there is a one to many relationship between A and B.
> Once this is done, I update the DataSet via a DataAdaptor such that these
> tables are transferred to the SQL database.
>
> My question is this. As far as I can see, the values in the primary key of
A
> and the corresponding keys in B will not necessarily be the same in the
> DataSet and the SQL database! Do you see what I mean? I can't see how the
> relationship specified is still valid after the Update.
>
> Can anybody explain this to me?
>
> Thanks in advance
>
> Geoff
>
>
>

"Jim Rand" <jimr***@ix.netcom.com> wrote in message
news:usOSiROJGHA.3064@TK2MSFTNGP10.phx.gbl...
> Hi Geoff,
>
> You're right, the keys aren't the same. The question is, "how do you get
> the
> dataset keys to sync up with the real keys in the database and how do you
> maintain the parent/child relationship at the same time?
>
> Let's assume the database issues the primary keys as autoincrement values.
>
> On the dataset side:
>
> In the xsd designer, for the primary key in each table, set AutoIncrement
> to
> true, AutoIncrementSeed to -1 and AutoIncrementStep to -1.  Each row added
> will have a unique primary value. It won't be "right", but at least it
> won't
> conflict with the database or with any other user.
>
> Edit each relationship between each table; in this case the relationship
> between parent table "A" and child table "B".
> Set the update rule to "cascade".  As a result of this setting, a change
> in
> the value of parent table "A's" primary key will cascade down to child
> table
> "B's" foreign key referencing parent table "A".  Thus, if something
> changes
> the primary key in "A" from -3 to 35736328, the value of the foreign key
> in
> table "B" will also change from -3 to 35736328 preserving the parent/child
> relationship.
>
> Moving on to the data adapter:
>
> In the INSERT statement, have something that brings the SCOPE_IDENTITY()
> from the database with the real primary key.  For example, the INSERT
> statement for table "A" would be:
>
> INSERT INTO A ( SomeString )
> VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()
>
> Notice two important points. First, we don't insert the primary key (-3)
> that was generated by the dataset. Second, we bring back the real primary
> key (35736328) issued by the database.  Through Microsoft magic, the data
> adapter's Update() method will change the primary key value in table "A"
> and
> will cascade the new foreign key values to table "B".
>
> Now for the code:
>
> If you've used the data adapter's Update() method on the original dataset,
> you're done.  However, if you created a small dataset with only changes to
> send back to the database using the dataset's GetChanges() method and
> subsequent Merge() method, you will discover that Microsoft's magic left
> your original (-3) row intact and added a new row (35736328).
>
> To get around this behavior, add the following event handler to the data
> adapter:
>
>  private void daTblA_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
>    {
>        if ( e.StatementType == StatementType.Insert) e.Status =
> UpdateStatus.SkipCurrentRow;
>    }
>
> Note, with the dataset's WriteXml() method, examine the GetChanges
> dataset's
> diffgram after the update to the database but prior to the Merge()
> operation
> back to the original dataset.
>
> Jim
>
>
> "Geoff" <nodamnspam@email.com> wrote in message
> news:uP6RmfFJGHA.3408@TK2MSFTNGP12.phx.gbl...
>> Hi
>>
>> I'm hoping somebody can help me with the following problem that has
> occurred
>> to me.
>>
>> Suppose I have two tables in an SQL Server database. Let's call these
> tables
>> A and B. Assume that A has two fields: a primary key and another holding
>> a
>> string. In table B there are three fields: a primary key, a foreign key
>> (which links to the primary key in A) and other field holding a string.
>>
>> Suppose I load these tables into a DataSet. I populate table A with some
>> rows. For each of these rows I create some rows in B which are linked by
> the
>> foreign key to A i.e. there is a one to many relationship between A and
>> B.
>> Once this is done, I update the DataSet via a DataAdaptor such that these
>> tables are transferred to the SQL database.
>>
>> My question is this. As far as I can see, the values in the primary key
>> of
> A
>> and the corresponding keys in B will not necessarily be the same in the
>> DataSet and the SQL database! Do you see what I mean? I can't see how the
>> relationship specified is still valid after the Update.
>>
>> Can anybody explain this to me?
>>
>> Thanks in advance
>>
>> Geoff
>>
>>
>>
>
>

"Geoff" <nodamnspam@email.com> wrote in message
news:%23DYsMkOJGHA.1088@tk2msftngp13.phx.gbl...
> Many thanks Jim for your comprehensive reply.
>
>
> "Jim Rand" <jimr***@ix.netcom.com> wrote in message
> news:usOSiROJGHA.3064@TK2MSFTNGP10.phx.gbl...
> > Hi Geoff,
> >
> > You're right, the keys aren't the same. The question is, "how do you get
> > the
> > dataset keys to sync up with the real keys in the database and how do
you
> > maintain the parent/child relationship at the same time?
> >
> > Let's assume the database issues the primary keys as autoincrement
values.
> >
> > On the dataset side:
> >
> > In the xsd designer, for the primary key in each table, set
AutoIncrement
> > to
> > true, AutoIncrementSeed to -1 and AutoIncrementStep to -1.  Each row
added
> > will have a unique primary value. It won't be "right", but at least it
> > won't
> > conflict with the database or with any other user.
> >
> > Edit each relationship between each table; in this case the relationship
> > between parent table "A" and child table "B".
> > Set the update rule to "cascade".  As a result of this setting, a change
> > in
> > the value of parent table "A's" primary key will cascade down to child
> > table
> > "B's" foreign key referencing parent table "A".  Thus, if something
> > changes
> > the primary key in "A" from -3 to 35736328, the value of the foreign key
> > in
> > table "B" will also change from -3 to 35736328 preserving the
parent/child
> > relationship.
> >
> > Moving on to the data adapter:
> >
> > In the INSERT statement, have something that brings the SCOPE_IDENTITY()
> > from the database with the real primary key.  For example, the INSERT
> > statement for table "A" would be:
> >
> > INSERT INTO A ( SomeString )
> > VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()
> >
> > Notice two important points. First, we don't insert the primary key (-3)
> > that was generated by the dataset. Second, we bring back the real
primary
> > key (35736328) issued by the database.  Through Microsoft magic, the
data
> > adapter's Update() method will change the primary key value in table "A"
> > and
> > will cascade the new foreign key values to table "B".
> >
> > Now for the code:
> >
> > If you've used the data adapter's Update() method on the original
dataset,
> > you're done.  However, if you created a small dataset with only changes
to
> > send back to the database using the dataset's GetChanges() method and
> > subsequent Merge() method, you will discover that Microsoft's magic left
> > your original (-3) row intact and added a new row (35736328).
> >
> > To get around this behavior, add the following event handler to the data
> > adapter:
> >
> >  private void daTblA_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
> >    {
> >        if ( e.StatementType == StatementType.Insert) e.Status =
> > UpdateStatus.SkipCurrentRow;
> >    }
> >
> > Note, with the dataset's WriteXml() method, examine the GetChanges
> > dataset's
> > diffgram after the update to the database but prior to the Merge()
> > operation
> > back to the original dataset.
> >
> > Jim
> >
> >
> > "Geoff" <nodamnspam@email.com> wrote in message
> > news:uP6RmfFJGHA.3408@TK2MSFTNGP12.phx.gbl...
> >> Hi
> >>
> >> I'm hoping somebody can help me with the following problem that has
> > occurred
> >> to me.
> >>
> >> Suppose I have two tables in an SQL Server database. Let's call these
> > tables
> >> A and B. Assume that A has two fields: a primary key and another
holding
> >> a
> >> string. In table B there are three fields: a primary key, a foreign key
> >> (which links to the primary key in A) and other field holding a string.
> >>
> >> Suppose I load these tables into a DataSet. I populate table A with
some
> >> rows. For each of these rows I create some rows in B which are linked
by
> > the
> >> foreign key to A i.e. there is a one to many relationship between A and
> >> B.
> >> Once this is done, I update the DataSet via a DataAdaptor such that
these
> >> tables are transferred to the SQL database.
> >>
> >> My question is this. As far as I can see, the values in the primary key
> >> of
> > A
> >> and the corresponding keys in B will not necessarily be the same in the
> >> DataSet and the SQL database! Do you see what I mean? I can't see how
the
> >> relationship specified is still valid after the Update.
> >>
> >> Can anybody explain this to me?
> >>
> >> Thanks in advance
> >>
> >> Geoff
> >>
> >>
> >>
> >
> >
>
>

"Jim Rand" <jimr***@ix.netcom.com> wrote in message
news:OlZiFTPJGHA.1728@TK2MSFTNGP09.phx.gbl...
> You're welcome.
>
> Upon further examination, the INSERT statement should read:
> INSERT INTO A ( SomeString )
> VALUES ( @SomeString ); SELECT A_ID FROM A WHERE A_ID =SCOPE_IDENTITY()
>
> Jim
>
>
> "Geoff" <nodamnspam@email.com> wrote in message
> news:%23DYsMkOJGHA.1088@tk2msftngp13.phx.gbl...
>> Many thanks Jim for your comprehensive reply.
>>
>>
>> "Jim Rand" <jimr***@ix.netcom.com> wrote in message
>> news:usOSiROJGHA.3064@TK2MSFTNGP10.phx.gbl...
>> > Hi Geoff,
>> >
>> > You're right, the keys aren't the same. The question is, "how do you
>> > get
>> > the
>> > dataset keys to sync up with the real keys in the database and how do
> you
>> > maintain the parent/child relationship at the same time?
>> >
>> > Let's assume the database issues the primary keys as autoincrement
> values.
>> >
>> > On the dataset side:
>> >
>> > In the xsd designer, for the primary key in each table, set
> AutoIncrement
>> > to
>> > true, AutoIncrementSeed to -1 and AutoIncrementStep to -1.  Each row
> added
>> > will have a unique primary value. It won't be "right", but at least it
>> > won't
>> > conflict with the database or with any other user.
>> >
>> > Edit each relationship between each table; in this case the
>> > relationship
>> > between parent table "A" and child table "B".
>> > Set the update rule to "cascade".  As a result of this setting, a
>> > change
>> > in
>> > the value of parent table "A's" primary key will cascade down to child
>> > table
>> > "B's" foreign key referencing parent table "A".  Thus, if something
>> > changes
>> > the primary key in "A" from -3 to 35736328, the value of the foreign
>> > key
>> > in
>> > table "B" will also change from -3 to 35736328 preserving the
> parent/child
>> > relationship.
>> >
>> > Moving on to the data adapter:
>> >
>> > In the INSERT statement, have something that brings the
>> > SCOPE_IDENTITY()
>> > from the database with the real primary key.  For example, the INSERT
>> > statement for table "A" would be:
>> >
>> > INSERT INTO A ( SomeString )
>> > VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()
>> >
>> > Notice two important points. First, we don't insert the primary key
>> > (-3)
>> > that was generated by the dataset. Second, we bring back the real
> primary
>> > key (35736328) issued by the database.  Through Microsoft magic, the
> data
>> > adapter's Update() method will change the primary key value in table
>> > "A"
>> > and
>> > will cascade the new foreign key values to table "B".
>> >
>> > Now for the code:
>> >
>> > If you've used the data adapter's Update() method on the original
> dataset,
>> > you're done.  However, if you created a small dataset with only changes
> to
>> > send back to the database using the dataset's GetChanges() method and
>> > subsequent Merge() method, you will discover that Microsoft's magic
>> > left
>> > your original (-3) row intact and added a new row (35736328).
>> >
>> > To get around this behavior, add the following event handler to the
>> > data
>> > adapter:
>> >
>> >  private void daTblA_RowUpdated(object sender, SqlRowUpdatedEventArgs
>> > e)
>> >    {
>> >        if ( e.StatementType == StatementType.Insert) e.Status =
>> > UpdateStatus.SkipCurrentRow;
>> >    }
>> >
>> > Note, with the dataset's WriteXml() method, examine the GetChanges
>> > dataset's
>> > diffgram after the update to the database but prior to the Merge()
>> > operation
>> > back to the original dataset.
>> >
>> > Jim
>> >
>> >
>> > "Geoff" <nodamnspam@email.com> wrote in message
>> > news:uP6RmfFJGHA.3408@TK2MSFTNGP12.phx.gbl...
>> >> Hi
>> >>
>> >> I'm hoping somebody can help me with the following problem that has
>> > occurred
>> >> to me.
>> >>
>> >> Suppose I have two tables in an SQL Server database. Let's call these
>> > tables
>> >> A and B. Assume that A has two fields: a primary key and another
> holding
>> >> a
>> >> string. In table B there are three fields: a primary key, a foreign
>> >> key
>> >> (which links to the primary key in A) and other field holding a
>> >> string.
>> >>
>> >> Suppose I load these tables into a DataSet. I populate table A with
> some
>> >> rows. For each of these rows I create some rows in B which are linked
> by
>> > the
>> >> foreign key to A i.e. there is a one to many relationship between A
>> >> and
>> >> B.
>> >> Once this is done, I update the DataSet via a DataAdaptor such that
> these
>> >> tables are transferred to the SQL database.
>> >>
>> >> My question is this. As far as I can see, the values in the primary
>> >> key
>> >> of
>> > A
>> >> and the corresponding keys in B will not necessarily be the same in
>> >> the
>> >> DataSet and the SQL database! Do you see what I mean? I can't see how
> the
>> >> relationship specified is still valid after the Update.
>> >>
>> >> Can anybody explain this to me?
>> >>
>> >> Thanks in advance
>> >>
>> >> Geoff
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

"Geoff" <nodamnspam@email.com> wrote in message
news:OBaSaIeJGHA.3936@TK2MSFTNGP12.phx.gbl...
> Hi Jim
>
> As a matter of interest, do you know if the CommandBuilder automatically
> generates this code? Is there an option for it to do so?
>
> Geoff
>
> "Jim Rand" <jimr***@ix.netcom.com> wrote in message
> news:OlZiFTPJGHA.1728@TK2MSFTNGP09.phx.gbl...
> > You're welcome.
> >
> > Upon further examination, the INSERT statement should read:
> > INSERT INTO A ( SomeString )
> > VALUES ( @SomeString ); SELECT A_ID FROM A WHERE A_ID =SCOPE_IDENTITY()
> >
> > Jim
> >
> >
> > "Geoff" <nodamnspam@email.com> wrote in message
> > news:%23DYsMkOJGHA.1088@tk2msftngp13.phx.gbl...
> >> Many thanks Jim for your comprehensive reply.
> >>
> >>
> >> "Jim Rand" <jimr***@ix.netcom.com> wrote in message
> >> news:usOSiROJGHA.3064@TK2MSFTNGP10.phx.gbl...
> >> > Hi Geoff,
> >> >
> >> > You're right, the keys aren't the same. The question is, "how do you
> >> > get
> >> > the
> >> > dataset keys to sync up with the real keys in the database and how do
> > you
> >> > maintain the parent/child relationship at the same time?
> >> >
> >> > Let's assume the database issues the primary keys as autoincrement
> > values.
> >> >
> >> > On the dataset side:
> >> >
> >> > In the xsd designer, for the primary key in each table, set
> > AutoIncrement
> >> > to
> >> > true, AutoIncrementSeed to -1 and AutoIncrementStep to -1.  Each row
> > added
> >> > will have a unique primary value. It won't be "right", but at least
it
> >> > won't
> >> > conflict with the database or with any other user.
> >> >
> >> > Edit each relationship between each table; in this case the
> >> > relationship
> >> > between parent table "A" and child table "B".
> >> > Set the update rule to "cascade".  As a result of this setting, a
> >> > change
> >> > in
> >> > the value of parent table "A's" primary key will cascade down to
child
> >> > table
> >> > "B's" foreign key referencing parent table "A".  Thus, if something
> >> > changes
> >> > the primary key in "A" from -3 to 35736328, the value of the foreign
> >> > key
> >> > in
> >> > table "B" will also change from -3 to 35736328 preserving the
> > parent/child
> >> > relationship.
> >> >
> >> > Moving on to the data adapter:
> >> >
> >> > In the INSERT statement, have something that brings the
> >> > SCOPE_IDENTITY()
> >> > from the database with the real primary key.  For example, the INSERT
> >> > statement for table "A" would be:
> >> >
> >> > INSERT INTO A ( SomeString )
> >> > VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()
> >> >
> >> > Notice two important points. First, we don't insert the primary key
> >> > (-3)
> >> > that was generated by the dataset. Second, we bring back the real
> > primary
> >> > key (35736328) issued by the database.  Through Microsoft magic, the
> > data
> >> > adapter's Update() method will change the primary key value in table
> >> > "A"
> >> > and
> >> > will cascade the new foreign key values to table "B".
> >> >
> >> > Now for the code:
> >> >
> >> > If you've used the data adapter's Update() method on the original
> > dataset,
> >> > you're done.  However, if you created a small dataset with only
changes
> > to
> >> > send back to the database using the dataset's GetChanges() method and
> >> > subsequent Merge() method, you will discover that Microsoft's magic
> >> > left
> >> > your original (-3) row intact and added a new row (35736328).
> >> >
> >> > To get around this behavior, add the following event handler to the
> >> > data
> >> > adapter:
> >> >
> >> >  private void daTblA_RowUpdated(object sender, SqlRowUpdatedEventArgs
> >> > e)
> >> >    {
> >> >        if ( e.StatementType == StatementType.Insert) e.Status =
> >> > UpdateStatus.SkipCurrentRow;
> >> >    }
> >> >
> >> > Note, with the dataset's WriteXml() method, examine the GetChanges
> >> > dataset's
> >> > diffgram after the update to the database but prior to the Merge()
> >> > operation
> >> > back to the original dataset.
> >> >
> >> > Jim
> >> >
> >> >
> >> > "Geoff" <nodamnspam@email.com> wrote in message
> >> > news:uP6RmfFJGHA.3408@TK2MSFTNGP12.phx.gbl...
> >> >> Hi
> >> >>
> >> >> I'm hoping somebody can help me with the following problem that has
> >> > occurred
> >> >> to me.
> >> >>
> >> >> Suppose I have two tables in an SQL Server database. Let's call
these
> >> > tables
> >> >> A and B. Assume that A has two fields: a primary key and another
> > holding
> >> >> a
> >> >> string. In table B there are three fields: a primary key, a foreign
> >> >> key
> >> >> (which links to the primary key in A) and other field holding a
> >> >> string.
> >> >>
> >> >> Suppose I load these tables into a DataSet. I populate table A with
> > some
> >> >> rows. For each of these rows I create some rows in B which are
linked
> > by
> >> > the
> >> >> foreign key to A i.e. there is a one to many relationship between A
> >> >> and
> >> >> B.
> >> >> Once this is done, I update the DataSet via a DataAdaptor such that
> > these
> >> >> tables are transferred to the SQL database.
> >> >>
> >> >> My question is this. As far as I can see, the values in the primary
> >> >> key
> >> >> of
> >> > A
> >> >> and the corresponding keys in B will not necessarily be the same in
> >> >> the
> >> >> DataSet and the SQL database! Do you see what I mean? I can't see
how
> > the
> >> >> relationship specified is still valid after the Update.
> >> >>
> >> >> Can anybody explain this to me?
> >> >>
> >> >> Thanks in advance
> >> >>
> >> >> Geoff
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

"Jim Rand" <jimr***@ix.netcom.com> wrote in message
news:eZf7aTeJGHA.208@tk2msftngp13.phx.gbl...
> Hi Geoff,
>
> The SQL generated by the command builder only provides a starting point.
> It
> doesn't add the SELECT statement after either the INSERT or UPDATE
> statements.  Moreover, the WHERE clause includes every field - not too
> smart
> if you are using timestamps to handle concurrency conflicts.
>
> Jim
>
>
> "Geoff" <nodamnspam@email.com> wrote in message
> news:OBaSaIeJGHA.3936@TK2MSFTNGP12.phx.gbl...
>> Hi Jim
>>
>> As a matter of interest, do you know if the CommandBuilder automatically
>> generates this code? Is there an option for it to do so?
>>
>> Geoff
>>
>> "Jim Rand" <jimr***@ix.netcom.com> wrote in message
>> news:OlZiFTPJGHA.1728@TK2MSFTNGP09.phx.gbl...
>> > You're welcome.
>> >
>> > Upon further examination, the INSERT statement should read:
>> > INSERT INTO A ( SomeString )
>> > VALUES ( @SomeString ); SELECT A_ID FROM A WHERE A_ID =SCOPE_IDENTITY()
>> >
>> > Jim
>> >
>> >
>> > "Geoff" <nodamnspam@email.com> wrote in message
>> > news:%23DYsMkOJGHA.1088@tk2msftngp13.phx.gbl...
>> >> Many thanks Jim for your comprehensive reply.
>> >>
>> >>
>> >> "Jim Rand" <jimr***@ix.netcom.com> wrote in message
>> >> news:usOSiROJGHA.3064@TK2MSFTNGP10.phx.gbl...
>> >> > Hi Geoff,
>> >> >
>> >> > You're right, the keys aren't the same. The question is, "how do you
>> >> > get
>> >> > the
>> >> > dataset keys to sync up with the real keys in the database and how
>> >> > do
>> > you
>> >> > maintain the parent/child relationship at the same time?
>> >> >
>> >> > Let's assume the database issues the primary keys as autoincrement
>> > values.
>> >> >
>> >> > On the dataset side:
>> >> >
>> >> > In the xsd designer, for the primary key in each table, set
>> > AutoIncrement
>> >> > to
>> >> > true, AutoIncrementSeed to -1 and AutoIncrementStep to -1.  Each row
>> > added
>> >> > will have a unique primary value. It won't be "right", but at least
> it
>> >> > won't
>> >> > conflict with the database or with any other user.
>> >> >
>> >> > Edit each relationship between each table; in this case the
>> >> > relationship
>> >> > between parent table "A" and child table "B".
>> >> > Set the update rule to "cascade".  As a result of this setting, a
>> >> > change
>> >> > in
>> >> > the value of parent table "A's" primary key will cascade down to
> child
>> >> > table
>> >> > "B's" foreign key referencing parent table "A".  Thus, if something
>> >> > changes
>> >> > the primary key in "A" from -3 to 35736328, the value of the foreign
>> >> > key
>> >> > in
>> >> > table "B" will also change from -3 to 35736328 preserving the
>> > parent/child
>> >> > relationship.
>> >> >
>> >> > Moving on to the data adapter:
>> >> >
>> >> > In the INSERT statement, have something that brings the
>> >> > SCOPE_IDENTITY()
>> >> > from the database with the real primary key.  For example, the
>> >> > INSERT
>> >> > statement for table "A" would be:
>> >> >
>> >> > INSERT INTO A ( SomeString )
>> >> > VALUES ( @SomeString ); SELECT A_ID WHERE A_ID =SCOPE_IDENTITY()
>> >> >
>> >> > Notice two important points. First, we don't insert the primary key
>> >> > (-3)
>> >> > that was generated by the dataset. Second, we bring back the real
>> > primary
>> >> > key (35736328) issued by the database.  Through Microsoft magic, the
>> > data
>> >> > adapter's Update() method will change the primary key value in table
>> >> > "A"
>> >> > and
>> >> > will cascade the new foreign key values to table "B".
>> >> >
>> >> > Now for the code:
>> >> >
>> >> > If you've used the data adapter's Update() method on the original
>> > dataset,
>> >> > you're done.  However, if you created a small dataset with only
> changes
>> > to
>> >> > send back to the database using the dataset's GetChanges() method
>> >> > and
>> >> > subsequent Merge() method, you will discover that Microsoft's magic
>> >> > left
>> >> > your original (-3) row intact and added a new row (35736328).
>> >> >
>> >> > To get around this behavior, add the following event handler to the
>> >> > data
>> >> > adapter:
>> >> >
>> >> >  private void daTblA_RowUpdated(object sender,
>> >> > SqlRowUpdatedEventArgs
>> >> > e)
>> >> >    {
>> >> >        if ( e.StatementType == StatementType.Insert) e.Status =
>> >> > UpdateStatus.SkipCurrentRow;
>> >> >    }
>> >> >
>> >> > Note, with the dataset's WriteXml() method, examine the GetChanges
>> >> > dataset's
>> >> > diffgram after the update to the database but prior to the Merge()
>> >> > operation
>> >> > back to the original dataset.
>> >> >
>> >> > Jim
>> >> >
>> >> >
>> >> > "Geoff" <nodamnspam@email.com> wrote in message
>> >> > news:uP6RmfFJGHA.3408@TK2MSFTNGP12.phx.gbl...
>> >> >> Hi
>> >> >>
>> >> >> I'm hoping somebody can help me with the following problem that has
>> >> > occurred
>> >> >> to me.
>> >> >>
>> >> >> Suppose I have two tables in an SQL Server database. Let's call
> these
>> >> > tables
>> >> >> A and B. Assume that A has two fields: a primary key and another
>> > holding
>> >> >> a
>> >> >> string. In table B there are three fields: a primary key, a foreign
>> >> >> key
>> >> >> (which links to the primary key in A) and other field holding a
>> >> >> string.
>> >> >>
>> >> >> Suppose I load these tables into a DataSet. I populate table A with
>> > some
>> >> >> rows. For each of these rows I create some rows in B which are
> linked
>> > by
>> >> > the
>> >> >> foreign key to A i.e. there is a one to many relationship between A
>> >> >> and
>> >> >> B.
>> >> >> Once this is done, I update the DataSet via a DataAdaptor such that
>> > these
>> >> >> tables are transferred to the SQL database.
>> >> >>
>> >> >> My question is this. As far as I can see, the values in the primary
>> >> >> key
>> >> >> of
>> >> > A
>> >> >> and the corresponding keys in B will not necessarily be the same in
>> >> >> the
>> >> >> DataSet and the SQL database! Do you see what I mean? I can't see
> how
>> > the
>> >> >> relationship specified is still valid after the Update.
>> >> >>
>> >> >> Can anybody explain this to me?
>> >> >>
>> >> >> Thanks in advance
>> >> >>
>> >> >> Geoff
>> >> >>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

On Sat, 28 Jan 2006 22:12:25 -0000, "Geoff" <nodamnspam@email.com>
wrote:

>Hi
>
>I'm hoping somebody can help me with the following problem that has occurred
>to me.
>
>Suppose I have two tables in an SQL Server database. Let's call these tables
>A and B. Assume that A has two fields: a primary key and another holding a
>string. In table B there are three fields: a primary key, a foreign key
>(which links to the primary key in A) and other field holding a string.
>
>Suppose I load these tables into a DataSet. I populate table A with some
>rows. For each of these rows I create some rows in B which are linked by the
>foreign key to A i.e. there is a one to many relationship between A and B.
>Once this is done, I update the DataSet via a DataAdaptor such that these
>tables are transferred to the SQL database.
>
>My question is this. As far as I can see, the values in the primary key of A
>and the corresponding keys in B will not necessarily be the same in the
>DataSet and the SQL database! Do you see what I mean? I can't see how the
>relationship specified is still valid after the Update.
>
>Can anybody explain this to me?
>
>Thanks in advance
>
>Geoff
>
>

"Otis Mukinfus" <ph***@emailaddress.com> wrote in message
news:n66qt19cvea7jbvrc0micbuvsr8guoa63v@4ax.com...
> On Sat, 28 Jan 2006 22:12:25 -0000, "Geoff" <nodamnspam@email.com>
> wrote:
>
>>Hi
>>
>>I'm hoping somebody can help me with the following problem that has
>>occurred
>>to me.
>>
>>Suppose I have two tables in an SQL Server database. Let's call these
>>tables
>>A and B. Assume that A has two fields: a primary key and another holding a
>>string. In table B there are three fields: a primary key, a foreign key
>>(which links to the primary key in A) and other field holding a string.
>>
>>Suppose I load these tables into a DataSet. I populate table A with some
>>rows. For each of these rows I create some rows in B which are linked by
>>the
>>foreign key to A i.e. there is a one to many relationship between A and B.
>>Once this is done, I update the DataSet via a DataAdaptor such that these
>>tables are transferred to the SQL database.
>>
>>My question is this. As far as I can see, the values in the primary key of
>>A
>>and the corresponding keys in B will not necessarily be the same in the
>>DataSet and the SQL database! Do you see what I mean? I can't see how the
>>relationship specified is still valid after the Update.
>>
>>Can anybody explain this to me?
>>
>>Thanks in advance
>>
>>Geoff
>>
>>
> Your assumption is correct if you are assigning the key value to the
> new rows.
>
> If you use identity columns the new identities will become part of the
> relationship when inserted into the DB if you have set the DataSet up
> properly.
>
> There is a good explanation of this in Sceppa's book "ADO.NET" (MS
> Press).
>
> HTH
>
> Otis Mukinfus
> http://www.otismukinfus.com
> http://www.tomchilders.com