Typed datasets using oleDB

[Jason L James]

Hi all,

I have created a multi-user app using an MS Access
database in VB.Net. I am using a typed dataset. Each
table has a PK and several have FK relationships to
maintain a one-to-many relationship between parent
and child.

If I add a parent record to the dataset and then a child
record to the dataset the FK of the child is written as
the PK of the parent through my VB code.

However, if another record has been written by another
user the PK of the parent will be changed when the
dataset is updated to the batabase. The FK of the child is not
automatically updated to reflect the change. The new
PK of the parent is not updated in the dataset after
the update command has written the data back to the
database. When this happens the child record is orphaned!!

Does anyone have any suggestions as to how I should
ensure that the referential integrity of the relationship
between the parent and the child is maintained when the
parent dataset is updated and then the child dataset is
updated using the dataAdapter.Update(???) command .

Some code follows below:

Dim newComment As dsProjects.tblCommentRow =
DsProjects1.tblComment.NewRow
newComment.cEvent = sEvent
newComment.cAuthor = sEngineer
newComment.cText = sComment
newComment._cDate = dDate
newComment.csID = StageID
DsProjects1.tblComment.Rows.Add(newComment)

At this point the dataset has a PK value of 4, for example

Dim newAttachment As dsProjects.tblAttachmentRow =
DsProjects1.tblAttachment.NewRow()
newAttachment.acID = newComment.cID
newAttachment.aName = sFileName
newAttachment.aPath = sAttachment
newAttachment.aExt = sExtension
DsProjects1.tblAttachment.Rows.Add(newAttachment)

The FK of the child is written as four in the dataset (the same as
the PK of the parent).

This command will write the data to the database where it might
change the PK to ensure unique rules are not violated.
daComment.Update(DsProjects1.tblComment)


When the child dataset is updated the new PK of the parent will not
be the FK of the child. The record is orphaned.
daAttachment.Update(DsProjects1.tblAttachment)


Any ideas welcomed.

Many thanks,

Jason.

 

[Paul Clement]

On Tue, 02 Nov 2004 10:21:15 GMT, (e-mail address removed)-master.org (Jason L James) wrote:

¤ Hi all,
¤
¤ I have created a multi-user app using an MS Access
¤ database in VB.Net. I am using a typed dataset. Each
¤ table has a PK and several have FK relationships to
¤ maintain a one-to-many relationship between parent
¤ and child.
¤
¤ If I add a parent record to the dataset and then a child
¤ record to the dataset the FK of the child is written as
¤ the PK of the parent through my VB code.
¤
¤ However, if another record has been written by another
¤ user the PK of the parent will be changed when the
¤ dataset is updated to the batabase. The FK of the child is not
¤ automatically updated to reflect the change. The new
¤ PK of the parent is not updated in the dataset after
¤ the update command has written the data back to the
¤ database. When this happens the child record is orphaned!!
¤

You don't typically change the Primary Key value of a row. That would be a data integrity problem.
Is this what you are attempting to do?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Jason L James]

Paul,

it isn't something that I am explicitly doing. It occurs when
the data adapter is updated by back to the database.

The dataset assumes is knows what the next PK should be
when the dataset is loaded. However, if someone else
writes to the database, then an assumed PK will have to
be updated by the database when the second new
record is written.

Does that make sense? I'm not sure! All I know is that
if two people are accessing the database, then there is
a high chance that the PK will be updated when the data
is written back through the data adapter.

Thanks,

Jason.

 

[Jason L James]

Paul,

I replied but then it got deleted by my news server. The
updating of the PK occurs if the PK chosen by the
dataset is used by another users before it can be
permanently written to the database.

Thanks,

Jason.

 

[Jason L James]

Paul,

is there some way of retrieving the PK value from
the record that has just been written in the
RowUpdated event of the data adapter so that
I could migrate this to the child rows in accordance
with the integrity rules?

Thanks,

Jason.

 

[Paul Clement]

On Tue, 02 Nov 2004 17:14:19 GMT, (e-mail address removed)-master.org (Jason L James) wrote:

¤ Paul,
¤
¤ is there some way of retrieving the PK value from
¤ the record that has just been written in the
¤ RowUpdated event of the data adapter so that
¤ I could migrate this to the child rows in accordance
¤ with the integrity rules?
¤

I don't think I understand how this is happening. Unless you modify the fields that comprise the
primary key it will not be updated. If these fields can be updated by the user then they should not
be part of the primary key.

The primary key should be created when you add a new row to the table. None of the fields which
comprise the primary key should be modified after that.

How are you creating the primary key and how many fields does it consist of?


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Jason L James]

Paul,

thanks for sticking with me on this. The PK is a single
integer field in my Access database. It is set to auto-increment
when the table was created in Access. The PK of my child
table is also an auto-increment field. The FK in the child
table is of type integer. In Access I then create a one-to-many
relationship that enforces referential integrity by cascading updates
and deletes.

I create a connection in my IDE and then drag the tables
onto my form, creating a connection object and two
data adapter objects. I generate a dataset that contains the two
tables. I then create the relationship in the dataset, ensuring that
the updates and deletes will be cascaded.

I can then drop a datagrid onto my form, populate the dataset by
using the fill method of the data adapters, bind the dataset to the
parent table of my dataset and see the relationship and related
table.

If I then add a new record to the dataset using the following

Dim newDrawing As dsDrawings.tblDrawingRow =
DsDrawings1.tblDrawing.NewRow
newDrawing.dName = Now.TimeOfDay.ToString
DsDrawings1.tblDrawing.Rows.Add(newDrawing)
daDrawing.Update(DsDrawings1.tblDrawing)

I see the new row appear in the grid with it PK filled in. Assuming
that no other user has written a record to the database since the
dataset was filled, writing this new record to the dataset and then
updating the database through the data adapter will have both
the dataset and database in-line.

However, if I write a record to the database (using Access) after
filling the dataset, but before writing a new record in my VB.Net app,
the PK that is assigned to the new row in the dataset is already in
use in the database and so, when written to the database, the PK is
incremented, but the PK in the dataset is not changed to reflect
the new value. Therefore any child records that may be written will
be orphaned as the PK of their parent was changed from the value
used in the dataset to the value used in the Access database.

Sorry about all the writing, but this thing is really confusing and
I can't see a way out of it at the moment.

Below is the code used to fill the dataset:

daDrawing.Fill(DsDrawings1.tblDrawing)
daIssue.Fill(DsDrawings1.tblIssue)
DataGrid1.DataSource = DsDrawings1.tblDrawing

I can also send you the XML for the dataset if that will help. Or
even the entire app.

Thanks,

Jason.

 

[Jason L James]

Paul,

I managed to find it on the Microsoft support web
site:

http://support.microsoft.com/?kbid=816112

Thanks,

Jason.