Error: Microsoft JET Database Engine ...

I am working on a VB.NET WINFORM project with MS Access as database. The
database has 2 tables:

1) PROJID: has the following fields: projid & projnm, projid is the primary
key
2) PROJCOST: has the following fields: projid, currtyp, costtyp, yr, annamt.
Primary keys are: projid, currtyp, costtyp, yr.

In the MS Access Relationships design for this database, I setup a
relationship between PROJID & PROJCOST tables related by 'projid' field and
the 'Enforce Referential Integrity' box is checked.

On one window form, I created a dataview populated with data from table
PROJID. Several text box fields on this form are binded to this dataview,
user can change data in table PROJID from these text fields.

To save data to table, I use DataAdapter.Update command. This is where I
keep getting an error saying:

"Error: Microsoft JET Database Engine: The record cannot be deleted or
changed because table 'projcost' includes related records"

This happen even when the primary key field in PROJID is not changed. Please
let me know if you have way to solve this problem

Thanks

Mike
Tue Nov 09 11:22:21 CST 2004

David,

For the scenario you described will need to delete the child records, then
the parent record from code in your client application.

In other words, call UDATE on the child table DataAdapter then the parent
table DataAdapter.

You will likely need to do the opposite when you add a parent with children
e.g. add the parent record then the children.

If by chance you have the book Programming Microsoft.NET by Francesco Balena
see chapter 21 for a complete explanation of managing referential integrity
with ADO.NET.

Another thing that will help would be to create a component in your project
that will be the Data Access Layer. Here you can place the connection and
all adpaters, and generate one strongly typed DataSet you can used
throughout your program.


--
Mike

Mike McIntyre
Visual Basic MVP
www.getdotnetcode.com



"DavidT" <DavidT@discussions.microsoft.com> wrote in message
news:E0F5C3A6-FB94-46AE-BF6C-AAE4282B7EAB@microsoft.com...
>I am working on a VB.NET WINFORM project with MS Access as database. The
> database has 2 tables:
>
> 1) PROJID: has the following fields: projid & projnm, projid is the
> primary
> key
> 2) PROJCOST: has the following fields: projid, currtyp, costtyp, yr,
> annamt.
> Primary keys are: projid, currtyp, costtyp, yr.
>
> In the MS Access Relationships design for this database, I setup a
> relationship between PROJID & PROJCOST tables related by 'projid' field
> and
> the 'Enforce Referential Integrity' box is checked.
>
> On one window form, I created a dataview populated with data from table
> PROJID. Several text box fields on this form are binded to this dataview,
> user can change data in table PROJID from these text fields.
>
> To save data to table, I use DataAdapter.Update command. This is where I
> keep getting an error saying:
>
> "Error: Microsoft JET Database Engine: The record cannot be deleted or
> changed because table 'projcost' includes related records"
>
> This happen even when the primary key field in PROJID is not changed.
> Please
> let me know if you have way to solve this problem
>
> Thanks

DavidT
Tue Nov 09 14:25:06 CST 2004

Mike,

Thanks for your reply.

The form that I mention, only contain fields from the parent table (projid),
and only non-primary fields of this table were updated.

I thought may be the binding collection somehow change the data in primary
key, but when I use GetChanges() method of the dataset, to view the changes
in debug window, nothing in primary key was changed. This is a weird error, I
think I should post this message in the adonet forum too.

DavidT


"Mike McIntyre" wrote:

> David,
>
> For the scenario you described will need to delete the child records, then
> the parent record from code in your client application.
>
> In other words, call UDATE on the child table DataAdapter then the parent
> table DataAdapter.
>
> You will likely need to do the opposite when you add a parent with children
> e.g. add the parent record then the children.
>
> If by chance you have the book Programming Microsoft.NET by Francesco Balena
> see chapter 21 for a complete explanation of managing referential integrity
> with ADO.NET.
>
> Another thing that will help would be to create a component in your project
> that will be the Data Access Layer. Here you can place the connection and
> all adpaters, and generate one strongly typed DataSet you can used
> throughout your program.
>
>
> --
> Mike
>
> Mike McIntyre
> Visual Basic MVP
> www.getdotnetcode.com
>
>
>
> "DavidT" <DavidT@discussions.microsoft.com> wrote in message
> news:E0F5C3A6-FB94-46AE-BF6C-AAE4282B7EAB@microsoft.com...
> >I am working on a VB.NET WINFORM project with MS Access as database. The
> > database has 2 tables:
> >
> > 1) PROJID: has the following fields: projid & projnm, projid is the
> > primary
> > key
> > 2) PROJCOST: has the following fields: projid, currtyp, costtyp, yr,
> > annamt.
> > Primary keys are: projid, currtyp, costtyp, yr.
> >
> > In the MS Access Relationships design for this database, I setup a
> > relationship between PROJID & PROJCOST tables related by 'projid' field
> > and
> > the 'Enforce Referential Integrity' box is checked.
> >
> > On one window form, I created a dataview populated with data from table
> > PROJID. Several text box fields on this form are binded to this dataview,
> > user can change data in table PROJID from these text fields.
> >
> > To save data to table, I use DataAdapter.Update command. This is where I
> > keep getting an error saying:
> >
> > "Error: Microsoft JET Database Engine: The record cannot be deleted or
> > changed because table 'projcost' includes related records"
> >
> > This happen even when the primary key field in PROJID is not changed.
> > Please
> > let me know if you have way to solve this problem
> >
> > Thanks
>
>
>