DataTable. Foreign key does not works.

C

c4p

Hello to all.

I have simple form with DataGrid binded to DataTable.
DataTable has foreign key to itself.
When I'm deleting one row it's child rows stay in Table.
(It deletes only parent row, not child).

Here is code:

DataTable TestTable = new DataTable("Test");

TestTable.Columns.Add("ID", typeof(Int32));
TestTable.Columns.Add("ParentID", typeof(Int32));

TestTable.Constraints.Add("PARENT_TO_ID",
TestTable.Columns["ID"],
TestTable.Columns["ParentID"]);

DataRow TestRow = TestTable.NewRow();
TestRow["ID"] = 1;
TestRow["ParentID"] = DBNull.Value;
TestTable.Rows.Add(TestRow);

TestRow = TestTable.NewRow();
TestRow["ID"] = 2;
TestRow["ParentID"] = 1;
TestTable.Rows.Add(TestRow);

dataGrid1.DataSource = TestTable;

Is it DataTable bug?
Thanks in advance!
 
R

RobinS

First, I think your syntax needs some help.

TestTable.Constraints.Add(New ForeignKeyConstraint("FK_Parent_ID", _
TestTable.Columns["ID"],
TestTable.Columns["ParentID"])

Second, is [ID] the parent's primary key, and [ParentID]
the child's key that matches the parent? If not, then I think
you need to reverse those.

Third, why are you setting up a DT with a foreign key to itself?

Robin S.
 
R

RobinS

Oh, it needs another parentheses, darn it.

TestTable.Constraints.Add(New ForeignKeyConstraint("FK_Parent_ID", _
TestTable.Columns["ID"],
TestTable.Columns["ParentID"]))

Robin S.
-------------
RobinS said:
First, I think your syntax needs some help.

TestTable.Constraints.Add(New ForeignKeyConstraint("FK_Parent_ID", _
TestTable.Columns["ID"],
TestTable.Columns["ParentID"])

Second, is [ID] the parent's primary key, and [ParentID]
the child's key that matches the parent? If not, then I think
you need to reverse those.

Third, why are you setting up a DT with a foreign key to itself?

Robin S.
-------------------------------------
c4p said:
Hello to all.

I have simple form with DataGrid binded to DataTable.
DataTable has foreign key to itself.
When I'm deleting one row it's child rows stay in Table.
(It deletes only parent row, not child).

Here is code:

DataTable TestTable = new DataTable("Test");

TestTable.Columns.Add("ID", typeof(Int32));
TestTable.Columns.Add("ParentID", typeof(Int32));

TestTable.Constraints.Add("PARENT_TO_ID",
TestTable.Columns["ID"],
TestTable.Columns["ParentID"]);

DataRow TestRow = TestTable.NewRow();
TestRow["ID"] = 1;
TestRow["ParentID"] = DBNull.Value;
TestTable.Rows.Add(TestRow);

TestRow = TestTable.NewRow();
TestRow["ID"] = 2;
TestRow["ParentID"] = 1;
TestTable.Rows.Add(TestRow);

dataGrid1.DataSource = TestTable;

Is it DataTable bug?
Thanks in advance!
Click to expand...
Click to expand...
 
C

c4p

Did you mean something like this:

ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Parent_ID",
TestTable.Columns["ID"], TestTable.Columns["ParentID"]);
TestTable.Constraints.Add(fk);

It doesn't work too.

Thanks for reply! :)
 
R

RobinS

Is ID the primary key for the parent, and ParentID the
matching entry for the child?

Robin S.
 
C

c4p

Yea!
When I delete Row with ID=1, all rows with ParentID=1 must be deleted
to...
I just cant understand why it's not work...
 
C

c4p

Do you have the DataRelation defined to cascade changes?

Hello

No, I don't have DataRelation. I'm using only DataTable, without
DataSet. Unfortunately I can't use DataSet.
Can I do it wthout DataSet?
 
C

c4p

Do you have the DataRelation defined to cascade changes?

Hello

No, I don't have DataRelation. I'm using only DataTable, without
DataSet. Unfortunately I can't use DataSet.
Can I do it wthout DataSet?
 
R

RobinS

Why do you have parents and children in the same table?
What does the table definition look like?

Robin S.
--------------------------------------
 
C

c4p

Hello.
This table is hierarchy.
For example we have such table:

ID ParentID
1 null
2 1
3 1
4 2
5 null

The hierarchy will look like (the number is ID):
|
|_1
| |_2
| | |_4
| |_3
|_4

I've created special control DataTreeView which displays this hierarchy.
 
R

RobinS

When you define the foreign key, add values for DeleteRule
and UpdateRule so it will cascade the changes. They should
default to Cascade, but just in case, set them.

Also, try setting EnforceConstraints to true. I'm hoping
this is a property of a DataTable. In the example on MSDN,
they apply this to the DataSet containing the two tables.

ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Parent_ID",
TestTable.Columns["ID"], TestTable.Columns["ParentID"]);
//***Add these two lines.
fk.DeleteRule = Rule.Cascade;
fk.UpdateRule = Rule.Cascade;
TestTable.Constraints.Add(fk);
//Add this line and see if it works.
TestTable.EnforceConstraints = True;

Does that help at all? I don't even know if you can do what
you're trying to do, but maybe this will work.

Robin S.
 
C

c4p

Hello!
I've tryed it before. It doesn't works too.
EnforceConstraints is property of DataSet.
Thanks for advices!
 
R

RobinS

I'd say what you're trying to do isn't possible then.
Have you considered showing the data in two grids as
parents and children?

Robin S.
 
C

c4p

I have own UserControl called DataTreeView which displays hierarhical
tables.
It's a pitty that such activity can't be done only with DataTable.

Yesterday I've tryed to do such task with DataSet. It works! But I
can't use DataSet...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

DataSet Parent.Child.GrandChild Relationship 1
ComboBox in DataGrid - Stuck! Help with Debugging!! 1
Filter on Parent-child relation?? 1
How to update dataset with multiple tables in C#? 1
How to update dataset with multiple tables? 1
Refresh a datatable afer an update so that child foreignkeys will be changed if necessary 2
DataTable Binding with DataGridView -- Doco Example Needs Work 3
AcceptChanges does nothing ?? 1

Top