problem with dataset constraints

[shiv]

Hi all,

I have two master/child tables
master: DEPT - PK deptid
child: EMP - PK empid ,FK (DEPT.deptid)

when i fill a dataset with the records of these two tables
Dataset ds=new Dataset();
DataAdapter.Fill(ds,"DEPT");
DataAdapter.Fill(ds,"EMP");
and add constraints to it

DataColumn[] deptPk={ds.Tables["DEPT"].Columns["DEPTID"]};
ds.Tables["DEPT"].PrimaryKey = deptPk;

DataColumn[] EmpPk = {ds.Tables["EMP"].Columns["EMPID"]};
ds.Tables["EMP"].PrimaryKey = EmpPk;
ds.Relations.Add("FK_REL",ds.Tables["DEPT"].Columns
["DEPTID"],ds.Tables["EMP"].Columns["DEPTID"],true);

It works fine for the following Constraint violations
1)Primary key/Unique for both the tables.
2)When we try to add a row to child table with an invalid
deptid

But Its not able to handle when we try to delete a row
from the master table when its child records are found in
the EMP table.

what must be going wrong?

Thanks in advance
regards
shiva

 

[Paul K]

shiva,

I assume you mean that the child rows are being deleted as
well? If so, change the DeleteRule and/or UpdateRule on
the foreign key to something other than Cascade.

ds.Relations("FKREL").DeleteRule / .UpdateRule =

Rule.Cascade (default - cascades deletes and updates)
Rule.None (no action)
Rule.SetDefault (sets related values to default values)
Rule.Null (sets related values to null)

Paul K

 

[Guest]

Dear Mr Paul K,

Thanks a million for the tip. it worked like a charm. The
problem with my code was that i did not set any delete
rules as you mentioned it out. Now it works fine.
thanks a lot again
regards
shiva

-----Original Message-----
shiva,

I assume you mean that the child rows are being deleted as
well? If so, change the DeleteRule and/or UpdateRule on
the foreign key to something other than Cascade.

ds.Relations("FKREL").DeleteRule / .UpdateRule =

Rule.Cascade (default - cascades deletes and updates)
Rule.None (no action)
Rule.SetDefault (sets related values to default values)
Rule.Null (sets related values to null)

Paul K

-----Original Message-----
Hi all,

I have two master/child tables
master: DEPT - PK deptid
child: EMP - PK empid ,FK (DEPT.deptid)

when i fill a dataset with the records of these two tables
Dataset ds=new Dataset();
DataAdapter.Fill(ds,"DEPT");
DataAdapter.Fill(ds,"EMP");
and add constraints to it

DataColumn[] deptPk={ds.Tables["DEPT"].Columns ["DEPTID"]};
ds.Tables["DEPT"].PrimaryKey = deptPk;

DataColumn[] EmpPk = {ds.Tables["EMP"].Columns["EMPID"]};
ds.Tables["EMP"].PrimaryKey = EmpPk;
ds.Relations.Add("FK_REL",ds.Tables["DEPT"].Columns
["DEPTID"],ds.Tables["EMP"].Columns["DEPTID"],true);

It works fine for the following Constraint violations
1)Primary key/Unique for both the tables.
2)When we try to add a row to child table with an invalid
deptid

But Its not able to handle when we try to delete a row
from the master table when its child records are found in
the EMP table.

what must be going wrong?

Thanks in advance
regards
shiva



.

.