Update to child table fails

[PAUL]

Hello,
I have 2 tables with a relationship set up in the dataset with vb
..net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I do the update the changed
parentid in the child table fails to change. No error is given its just that
the change is not written to the Database.

When I step through the records for the child table the one I would expect
to be changed has a row state of Unchanged!

Basically in the child table I have at the start:

ParentID
1664
1665
1666 <------ParentID to change
---------

1666 parentid gets changed to -1 as thats the new ID of the parent table
record, the -1 being a temporary placeholder.

The parent table record goes in fine and is there when I look in the
database after the update has been done.


Update code
Try

daAffectedObjects.DeleteCommand.Transaction = trnMain

daAffectedObjects.InsertCommand.Transaction = trnMain

daAffectedObjects.UpdateCommand.Transaction = trnMain

daAffectedObjects.UpdateCommand.CommandText = _

"UPDATE tblAffectedObjects " & _

"SET Archived = ?, CheckedOutBy = ?, CheckedOutDatabase = ?, " & _

"CheckedOutDate = ?, Description = ?, IssueID = ?, ObjectID = ?, " & _

"CheckedInDate = ? " & _

"WHERE ID = ?"


daAffectedObjects.Update(dsIssue, "tblAffectedObjects")


Catch dbcx As DBConcurrencyException

createMessage(dbcx)

Catch err As Exception

MsgBox(Me.Name & _

System.Reflection.MethodBase.GetCurrentMethod().Name & vbCrLf & _

"Affected objects update failed - can not update issue" & vbCrLf & _

"Description : " & err.ToString & vbCrLf & _

"Line Number : " & Erl())

trnMain.Rollback()

btnUpdate.Enabled = True

btnCancel.Enabled = True

If OleDbConnection1.State = ConnectionState.Open Then

OleDbConnection1.Close()

End If

Exit Sub

Finally

End Try

 

[Bart Mermuys]

Hi,

Hello,
I have 2 tables with a relationship set up in the dataset with vb
.net. I add a new record to the parent table then edit an existing child
record to have the new parent ID. However when I do the update the changed
parentid in the child table fails to change. No error is given its just
that the change is not written to the Database.

When I step through the records for the child table the one I would expect
to be changed has a row state of Unchanged!

I assume that right after you assign the id of the new parent to an existing
child row that that row will have a Modified RowState (you could also use
SetParentRow). So at what point does the RowState change back to Unchanged
?

How did you add the relation ? Make sure you don't set Accept/Reject rule to
Cascade.

Also never call AcceptChanges between fill and update because that will also
change the RowState to Unchanged.

Anyways, RowSate of the modified child row should be Modified before the
Update, it's something to look into.

If you can't find the problem post all relevant code.

HTH,
Greetings

 

[PAUL]

Thanks Bart, I'll look at that ASAP.
Paul

----- Original Message -----
From: "Bart Mermuys" <[email protected]>
Newsgroups:
comp.lang.basic.visual.database,microsoft.public.dotnet.languages.vb
Sent: Tuesday, October 25, 2005 10:01 PM
Subject: Re: Update to child table fails

 

[PAUL]

Hello,
tried the things below with no luck. The parent & child tables are
held within a single dataset, the tables (dataset) are exact mirrors of
their base tables in the MS Access database I am using. In the Db I have a 1
to many relationship (ref int switched on) between parent & child. The
relationship is also defined in the dataset as well.

The problem appears to be this:
(1) I add a new record in the parent (dataset), say this gives me an ID=1667
(2) I find the child record (dataset) that had the old parentID (eg 1666)
and update to the value of the new parentID value (1667). This I can do
under the rules of referential integrity.
(3) So now I have a record in the parent table (dataset) with an ID = 1667
and a related record in the child table (ParentID=1667). As I havn't done a
dataAdaptor.Update call yet MS Access is out of the picture.
(4) I then call the dataAdaptor.Update call for the parent table, this works
OK.
(5) I then call the dataAdaptor.Update call for the CHILD table, this is
when I get the error about must having a related record in the parent table.
(6) If I remove the relationship between Parent & Child in both MS Access
and the dataset schema the code goes through without an error. However when
I look at the Parent & Child tables in MS Access a new record has been added
to the table but it does not have the ID it was allocated by vb .net.
(8) When I remove the relationship in MS Access only the code again goes
through without an error and the same result as in point (6).
(9) When I look in the Child table the record has had the value of the
ParentID changed to 1667 but there is no record with an ID=1667 in the
Parent table because MS Access allocated a different ID when inserting the
new record into the Parent table.
(10) Now I understand the vb .net and MS Access will allocate autonumber
fields differently I thought the values would be synchronised? So when the
parent table gets a new record ID from MS Access either it or ado .net
wwould change the child ParentID field to the new value.

Any help would be much appreciated....

Thanks
Paul

 

[Bart Mermuys]

Hi,

Hello,
tried the things below with no luck. The parent & child tables are
held within a single dataset, the tables (dataset) are exact mirrors of
their base tables in the MS Access database I am using. In the Db I have a
1 to many relationship (ref int switched on) between parent & child. The
relationship is also defined in the dataset as well.

The problem appears to be this:
(1) I add a new record in the parent (dataset), say this gives me an
ID=1667
(2) I find the child record (dataset) that had the old parentID (eg 1666)
and update to the value of the new parentID value (1667). This I can do
under the rules of referential integrity.
(3) So now I have a record in the parent table (dataset) with an ID = 1667
and a related record in the child table (ParentID=1667). As I havn't done
a dataAdaptor.Update call yet MS Access is out of the picture.
(4) I then call the dataAdaptor.Update call for the parent table, this
works OK.
(5) I then call the dataAdaptor.Update call for the CHILD table, this is
when I get the error about must having a related record in the parent
table.
(6) If I remove the relationship between Parent & Child in both MS Access
and the dataset schema the code goes through without an error. However
when I look at the Parent & Child tables in MS Access a new record has
been added to the table but it does not have the ID it was allocated by vb
.net.
(8) When I remove the relationship in MS Access only the code again goes
through without an error and the same result as in point (6).
(9) When I look in the Child table the record has had the value of the
ParentID changed to 1667 but there is no record with an ID=1667 in the
Parent table because MS Access allocated a different ID when inserting the
new record into the Parent table.

(10) Now I understand the vb .net and MS Access will allocate autonumber
fields differently I thought the values would be synchronised? So when the
parent table gets a new record ID from MS Access either it or ado .net
wwould change the child ParentID field to the new value.

Yes, the autonumber can be different inside the DataTable and db, when you
do an update you can retrieve the db generated pk, but this doesn't happen
automatically and it is handled differently for sqlsever and access(jet).

For access you need to add an eventhandler to the
OleDbDataAdapter.RowUpdated event and use an OleDbCommand to do a query like
"SELECT @@IDENTITY" to get the new pk.

See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp
Scroll to Microsoft Access/JET Issues

Once the parent DataRow gets updated with the new pk it should automatically
update all related child DataRow's when there is a relation (in DataSet)
between them and UpdateRule is Cascade (which is the default).


HTH,
Greetings

 

[PAUL M.]

Hello,
I tried using the Dataset.OnUpdate event but (probably I am using
transaction and havnt committed yet) selecting the max value for the ID
field in the parent table just returns the value that was the highest value
before I started to do anything. I assume by commiting first I would then be
able to get the new ID value but I dont want to do that as I want the update
to go through or if problems be able to roll things back ie I dont want to
leave the DB in an inconsistent state.

Cheers
Paul M