Related records - is a datarelation necessary

I have a table named ProposedCosts and another table named AdditionalCosts.
AdditionalCosts has a foriegn key to the ProposedCostID in the ProposedCost
table.

When I retrieve records out of the database, I use a SQLDAtaAdapter and the
.Fill method to fill a 'ProposedCost' datatable and a 'AdditionalCost'
datatable, both tables are in the same dataset.

From the front end, if I wanted to add a new row to ProposedCost, that isn't
any problem except for the fact that it does not generate a ProposedCostID
(this is an identity key in the database). The problem is that if I want to
create an 'additional cost' record, I have no ProposedCostID.

What is the best way to handle a situation like this?

I thought about going back to the database with the dataset and somehow
adding a row to the datatable that way, but then when I finally do, do an
update, won't it think the row I added is a 'new' row and not an update?

Thanks.

STom

Miha
Wed Jan 21 03:26:06 CST 2004

Hi STom,

"STom" <stombiztalker@hotmail.com> wrote in message
news:%23sVPsT73DHA.1404@TK2MSFTNGP11.phx.gbl...
> I have a table named ProposedCosts and another table named
AdditionalCosts.
> AdditionalCosts has a foriegn key to the ProposedCostID in the
ProposedCost
> table.
>
> When I retrieve records out of the database, I use a SQLDAtaAdapter and
the
> .Fill method to fill a 'ProposedCost' datatable and a 'AdditionalCost'
> datatable, both tables are in the same dataset.
>
> From the front end, if I wanted to add a new row to ProposedCost, that
isn't
> any problem except for the fact that it does not generate a ProposedCostID
> (this is an identity key in the database). The problem is that if I want
to
> create an 'additional cost' record, I have no ProposedCostID.

You should set pk DataColumn.AutoIncrement=true, AutoIncrementSeed = -1 and
AutoIncrementStep = -1

> What is the best way to handle a situation like this?

In addition to pk (you should set for both tables, I suppose) you should set
a DataRelation between them to keep the data integrity.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

STom
Wed Jan 21 06:19:46 CST 2004

Miha,

Pardon my ignorance on the matter, but I'm not quite sure what you are
saying.

Are you saying that after I retrieve my dataset from the database and right
before I add a new record I need to set the column properties of the
ProposedCostID to:
DataColumn.AutoIncrement = True
.AutoIncrementSeed = -1
.AutoIncrementStep = -1
??

Will this then place an ID in my new table record or does it just give me an
idea that I can use for my other table.

I'm just a little confused about where to perform these settings.

Thanks.

STom
"Miha Markic" <miha at rthand com> wrote in message
news:uB5vVCA4DHA.2380@TK2MSFTNGP10.phx.gbl...
> Hi STom,
>
> "STom" <stombiztalker@hotmail.com> wrote in message
> news:%23sVPsT73DHA.1404@TK2MSFTNGP11.phx.gbl...
> > I have a table named ProposedCosts and another table named
> AdditionalCosts.
> > AdditionalCosts has a foriegn key to the ProposedCostID in the
> ProposedCost
> > table.
> >
> > When I retrieve records out of the database, I use a SQLDAtaAdapter and
> the
> > .Fill method to fill a 'ProposedCost' datatable and a 'AdditionalCost'
> > datatable, both tables are in the same dataset.
> >
> > From the front end, if I wanted to add a new row to ProposedCost, that
> isn't
> > any problem except for the fact that it does not generate a
ProposedCostID
> > (this is an identity key in the database). The problem is that if I want
> to
> > create an 'additional cost' record, I have no ProposedCostID.
>
> You should set pk DataColumn.AutoIncrement=true, AutoIncrementSeed = -1
and
> AutoIncrementStep = -1
>
> > What is the best way to handle a situation like this?
>
> In addition to pk (you should set for both tables, I suppose) you should
set
> a DataRelation between them to keep the data integrity.
>
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
>

Miha
Wed Jan 21 06:38:15 CST 2004

Hi Stom,

"STom" <stombiztalker@hotmail.com> wrote in message
news:%23Yh%23djB4DHA.632@TK2MSFTNGP12.phx.gbl...
> Miha,
>
> Pardon my ignorance on the matter, but I'm not quite sure what you are
> saying.

No problem - sometime neither do I ;-)

> Are you saying that after I retrieve my dataset from the database and
right
> before I add a new record I need to set the column properties of the
> ProposedCostID to:
> DataColumn.AutoIncrement = True
> .AutoIncrementSeed = -1
> .AutoIncrementStep = -1
> ??

Normally you would set them when you create dataset. However it is important
only when you add new rows by code.

> Will this then place an ID in my new table record or does it just give me
an
> idea that I can use for my other table.

This will place negative ids on your table for rows added by code.
When calling Update the values should be replaced by real values
(responsability of insertcommand).

> I'm just a little confused about where to perform these settings.

Just ask if you need more info.

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com

STom
Wed Jan 21 07:47:11 CST 2004

Miha,

Thanks for clearing this up.

If I use the negative IDs in the child tables, with the insert commands know
how to backtrack and up the right values in there also?

STom
"Miha Markic" <miha at rthand com> wrote in message
news:us7mttB4DHA.2184@TK2MSFTNGP10.phx.gbl...
> Hi Stom,
>
> "STom" <stombiztalker@hotmail.com> wrote in message
> news:%23Yh%23djB4DHA.632@TK2MSFTNGP12.phx.gbl...
> > Miha,
> >
> > Pardon my ignorance on the matter, but I'm not quite sure what you are
> > saying.
>
> No problem - sometime neither do I ;-)
>
> > Are you saying that after I retrieve my dataset from the database and
> right
> > before I add a new record I need to set the column properties of the
> > ProposedCostID to:
> > DataColumn.AutoIncrement = True
> > .AutoIncrementSeed = -1
> > .AutoIncrementStep = -1
> > ??
>
> Normally you would set them when you create dataset. However it is
important
> only when you add new rows by code.
>
> > Will this then place an ID in my new table record or does it just give
me
> an
> > idea that I can use for my other table.
>
> This will place negative ids on your table for rows added by code.
> When calling Update the values should be replaced by real values
> (responsability of insertcommand).
>
> > I'm just a little confused about where to perform these settings.
>
> Just ask if you need more info.
>
> --
> Miha Markic - RightHand .NET consulting & software development
> miha at rthand com
> www.rthand.com
>
>

Miha
Wed Jan 21 08:01:14 CST 2004


"STom" <stombiztalker@hotmail.com> wrote in message
news:%23thIUUC4DHA.360@TK2MSFTNGP12.phx.gbl...
> Miha,
>
> Thanks for clearing this up.
>
> If I use the negative IDs in the child tables, with the insert commands
know
> how to backtrack and up the right values in there also?

Yes, it all depends on how the InsertCommand is defined...

--
Miha Markic - RightHand .NET consulting & software development
miha at rthand com
www.rthand.com