Refresh a datatable afer an update so that child foreignkeys will be changed if necessary

  • Thread starter Thread starter unknown;
  • Start date Start date
U

unknown;

hello,

my question is about relations and about refreshing datatable after a
update.

i've got 2 tables in a MSSQL database. tblOrder (parent) and
tblOrderProduct (child)
tblOder has a column ID <bigint, primary key, auto increase> and
tblOrderProduct also has a column ID<bigint, primary key, auto
increase> and a column OrderID which is the foreignkey to tblOrder.ID.

with a sqlAdapter and 2 sql-select-statements i fill 2 local
datatables from 1 dataset. i also make a relation on tblOrder.ID and
tblOrderProduct.OrderID. (whitch automaticly makes 2 constraints
( foreignkey constraint and unique constraint))

When i add an order (1 row into the ordertable and 3 rows into the
orderProdcut table) all rows have automaticly an ID assigned by the
dataset/datatable. This isn't the problem.

The problem occures when i want to update both tables into the
database and just before i do that an other user has added an order.
When i update my local tblOrder my order will get an other ID in the
database table but in my local table this doesn't change. When my
local tblOrder.ID doesn't change the foreignkeys in the child table
will not change.

When i update my local tblOrderProducts the foreign key isn't set to
the right order.

I hope my problem is clear.

How to fix this??


It's about refreshing my local datatable after an update so that a all
foreignkeys are also updated just before i update that table into the
database.

Thanks in advance!!

I can add an example if necessary
 
hello,

my question is about relations and about refreshingdatatableafter a
update.

i've got 2 tables in a MSSQL database. tblOrder (parent) and
tblOrderProduct (child)
tblOder has a column ID <bigint, primary key, auto increase> and
tblOrderProduct also has a column ID<bigint, primary key, auto
increase> and a column OrderID which is the foreignkey to tblOrder.ID.

with a sqlAdapter and 2 sql-select-statements i fill 2 local
datatables from 1 dataset. i also make a relation on tblOrder.ID and
tblOrderProduct.OrderID. (whitch automaticly makes 2 constraints
( foreignkey constraint and unique constraint))

When i add an order (1 row into the ordertable and 3 rows into the
orderProdcut table) all rows have automaticly an ID assigned by the
dataset/datatable. This isn't the problem.

The problem occures when i want to update both tables into the
database and just before i do that an other user has added an order.
When i update my local tblOrder my order will get an other ID in the
database table but in my local table this doesn't change. When my
local tblOrder.ID doesn't change the foreignkeys in the child table
will not change.

When i update my local tblOrderProducts the foreign key isn't set to
the right order.

I hope my problem is clear.

How to fix this??

It's about refreshing my localdatatableafter an update so that a all
foreignkeys are also updated just before i update that table into the
database.

Thanks in advance!!

I can add an example if necessary
Click to expand...

I also know that i can expand my insert command with the line ";
SELECT tblOrder.ID = @@IDENTITY" But when i do that there is an other
probem:


i give an example:

database.tblOrder
ID - OrderName
-------------------------------
1 - Order 1
3 - Order 3

database.tblOrderProduct
ID - OrderID - ProductName
---------------------------------------
1 - 1 - Prod.A
2 - 1 - Prod.B
3 - 3 - Prod.A
4 - 3 - Prod.C


Which SELECT * FROM tblOrder i fill my local.tblOrder and i add 2
orders so that my local.tblOrder look like this:

local.tblOrder
ID - OrderName
-------------------------------
1 - Order 1
3 - Order 3
4 - ADDED 4
5 - ADDED 5



Just before i want to update my local.tblOrder an other user just
added an order into the database so that my db table looks like this:
database.tblOrder
ID - OrderName
-------------------------------
1 - Order 1
3 - Order 3
4 - ORDER XX ADDED BY OTHER USER


When i use the UPDATE..... ; ; SELECT tblOrder.ID = @@IDENTITY command
my local datatable this is what happends
local.tblOrder
ID - OrderName
-------------------------------
1 - Order 1 (not changed, no update)
3 - Order 3 (not chaged, no updaet)
(4 - ORDER XX ADDED BY OTHER USER) ( this entry is in the
database.tblOrder, but not in my local.tblOrder)
5 - ADDED 4 ( added, so insert command. Original this entry had ID=4
but ID 4 already exists. Database will create this entry with ID=5. by
my SELECT ID = @@IDENTITY i'll try to change ID from 4 to 5.
ERROR -> there already is an entry in my local.tblOrder with ID=5.
5 - ADDED 5 (added in local.tblOrder but not into database.tblOrder)



How to fix this???
is it smart to update my datatable afer adding a row into my
local.tblOrder??
 
Back
Top