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)
--
It's not what we don't know that gives us trouble, it's what
we know that ain't so. -- Will Rogers
--

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.



"Frnak McKenney" wrote:

>
>
> 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)
> --
> It's not what we don't know that gives us trouble, it's what
> we know that ain't so. -- Will Rogers
> --
>

Shai,

Thank you for responding so quickly.

On Tue, 28 Jun 2005 02:55:01 -0700, Shai Goldberg <(E-Mail Removed)> wrote:
> 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)
--
"A ship in harbor is safe, but that is not what ships are built for."
-- John A. Shedd
--

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/de...ngestopic1.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

"Frnak McKenney" wrote:

>
> Shai,
>
> Thank you for responding so quickly.
>
> On Tue, 28 Jun 2005 02:55:01 -0700, Shai Goldberg <(E-Mail Removed)> wrote:
> > 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)
> --
> "A ship in harbor is safe, but that is not what ships are built for."
> -- John A. Shedd
> --
>

On Wed, 29 Jun 2005 01:03:09 -0700, Shai Goldberg <(E-Mail Removed)> wrote:
> 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)