Self Referencing parent child relation brick wall on dataadapter.update

[Jonathan Stone]

Anyone have experience with a similar scenario as the following? I have to
believe someone has done this.

I am trying to use a dataadapter.update method to create new records in one
datatable with records from another.

Quick background: Table1 has template records that need to be created in
Table2. I say template records only in context of the application this is
for, each record represents a project task for a particular customer that
will always be a needed task for projects dealing with that customer, thus,
template tasks.

Anyways, each record from the template Table1 has an identity column["mid"]
and a parent column["parentID"] which creates the hiearchical relationship
of these tasks (to understand which are subtasks of others through this
parent child relationship. The top level tasks' parentID is set to '0'. When
I fill my dataset with the appropriate records, I then loop thru them and
make a few slight adjustments to some of the other datacolumns to identify
them more to the current new project at hand. Now I want to do
dataadapter.update to put these records into the actual task Table2. Now
this works great, the new tasks are inserted and the identity columns are
obviously udpated to the Table2's latest autonumbers. The problem i have run
into is the parentID field. In the template Table1, they reference the
correct parent record, but once in the new Table2, the parentID column data
is still the static data that it was in the template Table1. I am trying to
find a way to keep this relation from Table1 into Table2 so that the new
records in Table2's parentIDs reference the new autonumber fields that keep
them tied to the appropriate parent tasks.

I've read much about creating DataRelations, Constraints but I just can't
get the formula correct, or maybe its not even the right way to do this.

I'm looking for any ideas or experiences that would lead me to the answer
here, the solution to this problem that has tied me up for a good 8 hours
now.

Thanks for any help
jon

 

[W.G. Ryan eMVP]

Johnathan:

If I understand the problem correctly, you have the parentID column and MID
and those are getting out of sync w/ the Child table? If you use a
DataRelation, on whichever column you want to stay in sync, and you chage
the parent, the changes will be cascaded to the child records, so calling
Update again will change those values back in the db. In the back end, the
child table doesn't have any autoincrement values that the parent cares
about right?

 

[Jonathan Stone]

You are pretty much onto it. I think i should maybe describe it a little
more though.
Table1 contains template records of which there is an identity field <mid>
and there is a column called <parentID>.
If its a top level record, the <parentID> is 0. So there would then be
records in there where the <parentID> is the <MID> of another record, and so
on and so forth.
There's never more than 3 levels too, so its almost doable in a different
way, but i'd like to make it so that there could be an 'infinite' depth to
the parent child relationship.

Now, my first operation is to pull in the appropriate template records, then
loop through them all in the dataset and populate a new dataset that is the
set that I do the update on to
Insert them into a different table. While looping through the first dataset
(templates) I add some variable data to complete the second (new) dataset.
Once that is complete, I do the dataadapter.update,
where it inserts the new dataset table into the live tasks sql table. So all
the new records get a new Identity number when inserted into the live tasks
table, but the <parentID> column doesn't reflect that new Identity id...
This is exactly the issue. I've stepped away from it for a few days hoping
that now I will be able to think more clearly about what the heck I'm really
trying to do. But if you have any ideas, I am open for suggestion still...

Thanks!

Johnathan:

If I understand the problem correctly, you have the parentID column and
MID
and those are getting out of sync w/ the Child table? If you use a
DataRelation, on whichever column you want to stay in sync, and you chage
the parent, the changes will be cascaded to the child records, so calling
Update again will change those values back in the db. In the back end, the
child table doesn't have any autoincrement values that the parent cares
about right?

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

Anyone have experience with a similar scenario as the following? I have
to
believe someone has done this.

I am trying to use a dataadapter.update method to create new records in one
datatable with records from another.

Quick background: Table1 has template records that need to be created in
Table2. I say template records only in context of the application this is
for, each record represents a project task for a particular customer that
will always be a needed task for projects dealing with that customer, thus,
template tasks.

Anyways, each record from the template Table1 has an identity column["mid"]
and a parent column["parentID"] which creates the hiearchical
relationship
of these tasks (to understand which are subtasks of others through this
parent child relationship. The top level tasks' parentID is set to '0'. When
I fill my dataset with the appropriate records, I then loop thru them and
make a few slight adjustments to some of the other datacolumns to
identify
them more to the current new project at hand. Now I want to do
dataadapter.update to put these records into the actual task Table2. Now
this works great, the new tasks are inserted and the identity columns are
obviously udpated to the Table2's latest autonumbers. The problem i have run
into is the parentID field. In the template Table1, they reference the
correct parent record, but once in the new Table2, the parentID column data
is still the static data that it was in the template Table1. I am trying to
find a way to keep this relation from Table1 into Table2 so that the new
records in Table2's parentIDs reference the new autonumber fields that keep
them tied to the appropriate parent tasks.

I've read much about creating DataRelations, Constraints but I just can't
get the formula correct, or maybe its not even the right way to do this.

I'm looking for any ideas or experiences that would lead me to the answer
here, the solution to this problem that has tied me up for a good 8 hours
now.

Thanks for any help
jon