Constraint error when dataset.GetChanges() invoked

[Frnak McKenney]

I'm running into a puzzling exception when I attempt to update a
modified row in a table with a foreign key constraint.

System.Data.ConstraintException: Failed to enable
constraints. One or more rows contain values violating
non-null, unique, or foreign-key constraints

My application has a single dataset with five tables that gets
loaded from an Access database. The error occurs along the way to
propagating a (trivial) change back to the .mdb file, and _seems_ to
be related to my attempt to update a single row of a single table
(call it TableB) which has a foriegn key constraint pointing back to
another table (call it TableA). These look like the following:


TableA: pk_a (autonumber primary key)
[other columns, none required]

TableB: pk_b (autonumber primary key)
pk_a (foreign key to TableA)
[other columns, none required]

These tables are loaded into a common DataSet which has been set up
witht he following constraints for TableB:

fkc_fd_pr = new ForeignKeyConstraint(
"TableATableB",
dataset.Tables["TableA"].Columns["pk_a"],
dataset.Tables["TableB"].Columns["pk_a"]
);
fkc_fd_pr.DeleteRule = Rule.Cascade;
fkc_fd_pr.UpdateRule = Rule.Cascade;
fkc_fd_pr.AcceptRejectRule = AcceptRejectRule.Cascade;
dataset.Tables["TabelB"].Constraints.Add(fkc_fd_pr);

The dataset then is filled:

dataadapter.Fill(dataset,"TableA");
dataadapter.Fill(dataset,"TableB");
....etc.

The user edits the contents of TableB through a form with lots of
TextBoxen. When he/she/it clicks on a [Save] button, the code goes
through the following steps:

- Prompt for confirmation (Are you ReallyReallySure(tm)?)
- bindingmanager.EndCurrentEdit()
- If dataset.HasErrors, exit the update procedure.
- If dataset.HasChanges(), then
- temp_dataset = dataset.GetChanges()

The exception is raised in the middle to the GetChanges method, with
a stack trace that looks like the following:
(Exception message)
at System.Data.DataSet.FailedEnableConstraints()
at System.Data.DataSet.EnableConstraints()
at System.Data.DataSet.set_EnforceConstraints(Boolean value)
at System.Data.DataSet.GetChanges(DataRowState rowStates)
at System.Data.DataSet.GetChanges()

After spending some time checking my Access table definitions, and
much more time getting Google-eyed trying to find someone else who
had seen a similar problem, I patched my code to try to figure out
exactly what was being objected to.

I added code similar to the following right after the check for
..HasErrors and before invoking .GetChanges(), and then issued a
simple [Edit] [Save] (no actual changes) on TableB.

DataTable dt1 = dsPgtDb.Tables["TableA"].GetChanges();
DataTable dt2 = dsPgtDb.Tables["TableB"].GetChanges();
DataTable dt3 = dsPgtDb.Tables["TableC"].GetChanges();
DataTable dt4 = dsPgtDb.Tables["TableD"].GetChanges();
DataTable dt5 = dsPgtDb.Tables["TableE"].GetChanges();

Those all executed flawlessly (although all but the "TableB" result
were null).

DataSet ds1 = dsPgtDb.GetChanges(DataRowState.Added);
DataSet ds2 = dsPgtDb.GetChanges(DataRowState.Deleted);
DataSet ds3 = dsPgtDb.GetChanges(DataRowState.Detached);
DataSet ds4 = dsPgtDb.GetChanges(DataRowState.Modified);
DataSet ds5 = dsPgtDb.GetChanges(DataRowState.Unchanged);

The first three of these worked just fine (null results, but no
ConstraintException). The fourth line, the one checking for Modified
datarows, tripped the ConstraintException.

Still no clue as to _why_ ADO.NET is unhappy, but at least it
confirms that it _is_ the one row I "modified" (opened up for
editing but made no changes to) which is being objected to.
This row was happily loaded from the Access .mdb file into a
DataSet with constraints already in place. So why, when it gets
flagged as Modified, does GetChanges have a hernia trying to locate
it in one DataSet and stuff a copy into a fresh, brand-spanking-new
Dataset?

Does anyone see any really obvious steps I'm leaving out that might
contribute to the problem?

Alternatively, can anyone suggest an approach for nailing down
exactly _which_ constraint I'm rudely violating?

Clues, hints, and suggestions will all be appreciated. I really
don't have all that much hair left to pull out... <grin?>


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[Guest]

Frnak,

I'm not familiar with OLEDB client but as I understand SQL client of ADO.NET
is the same.

I have had some problems with the mothod youare using:
- If dataset.HasChanges(), then
- temp_dataset = dataset.GetChanges()
so I'm using the DataAdapter's Updtae method directly on the DataSet it self
instead of transfering the changed data into a different DataSet.
you can check if there are changes using the dataset.HasChanges but try to
update the dataset tables with the fill instead of creating a new dataset.
the update in such a case will not affect unchanged rows and should update
only the changed rows.

Maybe this will solve your problems.

I'm running into a puzzling exception when I attempt to update a
modified row in a table with a foreign key constraint.

System.Data.ConstraintException: Failed to enable
constraints. One or more rows contain values violating
non-null, unique, or foreign-key constraints

My application has a single dataset with five tables that gets
loaded from an Access database. The error occurs along the way to
propagating a (trivial) change back to the .mdb file, and _seems_ to
be related to my attempt to update a single row of a single table
(call it TableB) which has a foriegn key constraint pointing back to
another table (call it TableA). These look like the following:


TableA: pk_a (autonumber primary key)
[other columns, none required]

TableB: pk_b (autonumber primary key)
pk_a (foreign key to TableA)
[other columns, none required]

These tables are loaded into a common DataSet which has been set up
witht he following constraints for TableB:

fkc_fd_pr = new ForeignKeyConstraint(
"TableATableB",
dataset.Tables["TableA"].Columns["pk_a"],
dataset.Tables["TableB"].Columns["pk_a"]
);
fkc_fd_pr.DeleteRule = Rule.Cascade;
fkc_fd_pr.UpdateRule = Rule.Cascade;
fkc_fd_pr.AcceptRejectRule = AcceptRejectRule.Cascade;
dataset.Tables["TabelB"].Constraints.Add(fkc_fd_pr);

The dataset then is filled:

dataadapter.Fill(dataset,"TableA");
dataadapter.Fill(dataset,"TableB");
....etc.

The user edits the contents of TableB through a form with lots of
TextBoxen. When he/she/it clicks on a [Save] button, the code goes
through the following steps:

- Prompt for confirmation (Are you ReallyReallySure(tm)?)
- bindingmanager.EndCurrentEdit()
- If dataset.HasErrors, exit the update procedure.
- If dataset.HasChanges(), then
- temp_dataset = dataset.GetChanges()

The exception is raised in the middle to the GetChanges method, with
a stack trace that looks like the following:
(Exception message)
at System.Data.DataSet.FailedEnableConstraints()
at System.Data.DataSet.EnableConstraints()
at System.Data.DataSet.set_EnforceConstraints(Boolean value)
at System.Data.DataSet.GetChanges(DataRowState rowStates)
at System.Data.DataSet.GetChanges()

After spending some time checking my Access table definitions, and
much more time getting Google-eyed trying to find someone else who
had seen a similar problem, I patched my code to try to figure out
exactly what was being objected to.

I added code similar to the following right after the check for
..HasErrors and before invoking .GetChanges(), and then issued a
simple [Edit] [Save] (no actual changes) on TableB.

DataTable dt1 = dsPgtDb.Tables["TableA"].GetChanges();
DataTable dt2 = dsPgtDb.Tables["TableB"].GetChanges();
DataTable dt3 = dsPgtDb.Tables["TableC"].GetChanges();
DataTable dt4 = dsPgtDb.Tables["TableD"].GetChanges();
DataTable dt5 = dsPgtDb.Tables["TableE"].GetChanges();

Those all executed flawlessly (although all but the "TableB" result
were null).

DataSet ds1 = dsPgtDb.GetChanges(DataRowState.Added);
DataSet ds2 = dsPgtDb.GetChanges(DataRowState.Deleted);
DataSet ds3 = dsPgtDb.GetChanges(DataRowState.Detached);
DataSet ds4 = dsPgtDb.GetChanges(DataRowState.Modified);
DataSet ds5 = dsPgtDb.GetChanges(DataRowState.Unchanged);

The first three of these worked just fine (null results, but no
ConstraintException). The fourth line, the one checking for Modified
datarows, tripped the ConstraintException.

Still no clue as to _why_ ADO.NET is unhappy, but at least it
confirms that it _is_ the one row I "modified" (opened up for
editing but made no changes to) which is being objected to.
This row was happily loaded from the Access .mdb file into a
DataSet with constraints already in place. So why, when it gets
flagged as Modified, does GetChanges have a hernia trying to locate
it in one DataSet and stuff a copy into a fresh, brand-spanking-new
Dataset?

Does anyone see any really obvious steps I'm leaving out that might
contribute to the problem?

Alternatively, can anyone suggest an approach for nailing down
exactly _which_ constraint I'm rudely violating?

Clues, hints, and suggestions will all be appreciated. I really
don't have all that much hair left to pull out... <grin?>


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[Frnak McKenney]

Shai,

Thank you for responding so quickly.

I have had some problems with the mothod youare using:
- If dataset.HasChanges(), then
- temp_dataset = dataset.GetChanges()
so I'm using the DataAdapter's Updtae method directly on the DataSet it self
instead of transfering the changed data into a different DataSet.
you can check if there are changes using the dataset.HasChanges but try to
update the dataset tables with the fill instead of creating a new dataset.
the update in such a case will not affect unchanged rows and should update
only the changed rows.

This sounded like it might be handy, but I didn't see how it applied
to what I was doing, so I snapped a copy for my archive and went
back to my code.

I was up late last night, so it took almost half an hour of staring
at my "update" routine before what you had been saying started to
sink in. It finaly dawned on me that what I was doing:

temp_ds = master_ds.GetChanges();
foreach (table in temp_ds) {
table.GetChanges()
update database copy of table
}

was only slightly different from:

foreach (table in master_ds) {
table.GetChanges()
update database copy of table
}

"Oh. ... THAT'S what he was talking about!"

My earlier "silly stuff" tests:

DataTable dt1 = dsPgtDb.Tables["TableA"].GetChanges();
DataTable dt2 = dsPgtDb.Tables["TableB"].GetChanges();

....etc.

had already shown me that _this_ route (that is, ignoring the
usually recommended approach and _not_ creating a changes-only
dataset) wouldn't blow up. So I made a backup copy of the class
source code file, simplified my code slightly, and...

IT WORKS!!!

I'll leave it to someone more knowledgeable about these things (and
who's not under a project deadline) to explain why I can invoke
GetChanges on every DataTable in a DataSet with no problem, but
issuing a GetChanges() for that whole DataSet triggers a
ConstraintException. I'm not thrilled about it -- I really like for
things that seem similar to behave similarly -- but at least I'm off
and running again.

When the dust settles on my current project, I may go back and see
if I can come up with some sort of routine that will do DataSet
consistency checking. If this turns out to be an ADO bug it won't
help me much, but I've been known to make... er, "a few" errors of
my own. <grin>


Thanks again, Shai.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[Guest]

Frnak,

First, I'm glad that I was able to help you, please be kind to mark my post
as helpfull by pressing Yes at the bottom of the mesage

Second, you where using the GetChanges method that is described here:
http://msdn.microsoft.com/library/d...lrfsystemdatadatasetclassgetchangestopic1.asp

If you'll loook there you'll see that "Relationship constraints may cause
Unchanged parent rows to be included. " (from the Remark section) so I assume
that for some reason the relations where not passed to the DataSet which
caused the problem because the DataSet expected some other rows which where
not passed.

As I wrote to you, I think that it is better to directly update the DataSet
and not to create a new DataSet and then Merge it back...

--
Shai

Shai,

Thank you for responding so quickly.

I have had some problems with the mothod youare using:
- If dataset.HasChanges(), then
- temp_dataset = dataset.GetChanges()
so I'm using the DataAdapter's Updtae method directly on the DataSet it self
instead of transfering the changed data into a different DataSet.
you can check if there are changes using the dataset.HasChanges but try to
update the dataset tables with the fill instead of creating a new dataset.
the update in such a case will not affect unchanged rows and should update
only the changed rows.

This sounded like it might be handy, but I didn't see how it applied
to what I was doing, so I snapped a copy for my archive and went
back to my code.

I was up late last night, so it took almost half an hour of staring
at my "update" routine before what you had been saying started to
sink in. It finaly dawned on me that what I was doing:

temp_ds = master_ds.GetChanges();
foreach (table in temp_ds) {
table.GetChanges()
update database copy of table
}

was only slightly different from:

foreach (table in master_ds) {
table.GetChanges()
update database copy of table
}

"Oh. ... THAT'S what he was talking about!"

My earlier "silly stuff" tests:

DataTable dt1 = dsPgtDb.Tables["TableA"].GetChanges();
DataTable dt2 = dsPgtDb.Tables["TableB"].GetChanges();

....etc.

had already shown me that _this_ route (that is, ignoring the
usually recommended approach and _not_ creating a changes-only
dataset) wouldn't blow up. So I made a backup copy of the class
source code file, simplified my code slightly, and...

IT WORKS!!!

I'll leave it to someone more knowledgeable about these things (and
who's not under a project deadline) to explain why I can invoke
GetChanges on every DataTable in a DataSet with no problem, but
issuing a GetChanges() for that whole DataSet triggers a
ConstraintException. I'm not thrilled about it -- I really like for
things that seem similar to behave similarly -- but at least I'm off
and running again.

When the dust settles on my current project, I may go back and see
if I can come up with some sort of routine that will do DataSet
consistency checking. If this turns out to be an ADO bug it won't
help me much, but I've been known to make... er, "a few" errors of
my own. <grin>


Thanks again, Shai.


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)

 

[Frnak McKenney]

First, I'm glad that I was able to help you, please be kind to mark my post
as helpfull by pressing Yes at the bottom of the mesage

Shai,

Um... I'd love to, but the bootom of your message there's only... the bottom of
your message and the SLRN command hints. After that, you fall out of the
command window. I get the feeling you might not be running SLRN. <grin>

Is there a 'web site I can access this through which _will_ supply the [Yes]
button?


Frank McKenney, McKenney Associates
Richmond, Virginia / (804) 320-4887
Munged E-mail: frank uscore mckenney ayut minds pring dawt cahm (y'all)