Adding constraints

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have an ACCESS database, with 2 tables. The realtionship between tables is
set 1:1.

I put those tables into dataset and set up the foreign key constraints:

DataColumn parent = ds.Tables["Empl].Columns["AdvertisementID"];
DataColumn child = ds.Tables["Pictures"].Columns["AdvertisementID"];
ForeignKeyConstraint fk = new ForeignKeyConstraint("FK_Empl_Pictures",
parent, child);
fk.UpdateRule = Rule.Cascade;
fk.DeleteRule = Rule.SetNull;
ds.Tables["Pictures"].Constraints.Add(fk);

When Pictures database is empty, everything is OK. If I add records to
Picture db (=using Access directly), I get error message while running
mentioned code.
"This constraint cannot be enabled as not all values have corresponding
parent values.
"
Thats is not true, as Access would not allowe to corrupt the relationsip by
adding values.

Any idea what is wrong here?

Thanks,

Lubomir
 
Lubomir - let me make sure I understand the problem. You have two tables,
Empl and Pictures which are related via AdvertisementID - in the Access db.
You also have a DataSet locally with the same relationships. You fill the
local tables from the db - then you attempt to add a record to Picture back
on the database and this causes the problem?

I think the timing may be the issue. Are you refilling the Dataset again
after changing the data on the back end? What is the sequence you are doing
things in (filling the datatables, adding the contraints, adding the
records). I think there's probably just a timing issue with what you're
updating and when.
 
Hi Lubomir,
"This constraint cannot be enabled as not all values have corresponding
parent values.
"
Thats is not true, as Access would not allowe to corrupt the relationsip by
adding values.
Click to expand...

You are correct, but the exception is not related to Access. Remember the
DataSet is disconnected and is independent from Access. This error can occur
when a parent-child relationship is created and then children are loaded into
the DataSet without the parent existing (whether or not the parent exists in
the database is irrelevant - it has to exist in the DataSet as well because
the DataSet is disconnected from the database).

The first thing to double check is that you are filling the "Empl" table
first and the "Pictures" table second. The second thing to check is that for
every row you are selecting in the second table (Pictures) you have also
selected a row in the first table (Empl) to be its parent.

Cheers,
Steve Goodyear
Vancouver, Canada
 
Hi All,

Thank you for interrest. here is step by step, what I have done:

1/ vreated test database in Access; 2 tables, with relations. Then i put
some records to parent table and while I was in a table view, i added records
to the child (pictures0 table.
2/ I closed Access app.
3/ In code behind for my asp page I did:
- dataset: added parent table
added Picture table from Access
4/set up constraint primary key for parent table
5/ set up primary key for Pictures tbl
6/ set up ForeignKeyConstraint -> here i get that mentioned exception.

If I go back to Access database, and delete all records from Pictures, my
code behind code works.

In Pictures tbl are not any records which are not in paretn table.

Any idea wha could help?
Thank you again.
Lubomir
 
Hi,

I guess, I've found it. In codebehind is set a wrong filter for the parent
table, so it could happen, not all records from child table were in the
parent table.

Thank you anyway, for your time.
Regards,
Lubomir
 
Back
Top