Related records - is a datarelation necessary

  • Thread starter Thread starter STom
  • Start date Start date
S

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
 
Hi STom,

STom said:
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.
Click to expand...

You should set pk DataColumn.AutoIncrement=true, AutoIncrementSeed = -1 and
AutoIncrementStep = -1
What is the best way to handle a situation like this?
Click to expand...

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,

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
 
Hi Stom,

STom said:
Miha,

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

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
??
Click to expand...

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.
Click to expand...

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.
Click to expand...

Just ask if you need more info.
 
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
 
STom said:
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?
Click to expand...

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