Related records - is a datarelation necessary

[STom]

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 Markic]

Hi STom,

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.

 

[STom]

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]

Hi Stom,

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.

 

[STom]

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,

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...