updating child rows with master pk id AFTER initial insert

[joben]

I need help with the following. I'm a newbie.

I have a master-detail form for an inventory application. Its a simple
form to record delivered items. The underlying table schema is as
follows
(simplified):

DELIVERY
-DeliveryID (Integer, PK, Identity)

DELIVERYDETAILS
-DeliveryFK
-SerialNumber

The dataset has the corresponding tables with a declared data relation
for linking DeliveryID and DeliveryFK. In the dataset schema, I set
(-1) as a default value for both DeliveryID and DeliveryFK. I use a
data adapter to propagate changes to the DB. In my insert command, I've
set the .UpdatedRowSource = OutputParameters so that when I insert into
the Delivery table, I can retrieve the generated PK. The data relation
takes care of cascading the value to DeliveryFK. So far so good. I am
able to successfully insert to both DELIVERY and DELIVERYDETAILS.

My problem is that after I save the records, I want the user to be able
to update the same record, or insert new details. As long as no new
details are added, I can still update the underlying records. But when
I add new details, I get an error. "violation of foreign key
constraint". What happens is that when I add new details, the
DeliveryFK has a value of (-1) because that is the default value I
initially set. But the DeliveryID already has a different value because
during the INSERT, I was able to retrieve the generated PK.

How do I tell my dataset to use the new value of DeliveryFK instead of
the default?

Thanks for any help.

 

[Grzegorz Danowski]

I need help with the following. I'm a newbie.

I have a master-detail form for an inventory application. Its a simple
form to record delivered items. The underlying table schema is as
follows
(simplified):

DELIVERY
-DeliveryID (Integer, PK, Identity)

DELIVERYDETAILS
-DeliveryFK
-SerialNumber

The dataset has the corresponding tables with a declared data relation
for linking DeliveryID and DeliveryFK. In the dataset schema, I set
(-1) as a default value for both DeliveryID and DeliveryFK.

In so situations I prefer do use AutoIncrement with seed and step = -1
(recommendation from David Sceppa book).

Regards,
Grzegorz

 

[Alex Homer]

I've done this by handling the RowUpdating and RowUpdated events of the
DataSet during the update on the primary table.

... etc ...
'add event handlers for RowUpdating and RowUpdated events
'required to handle inserted rows by updating the OrderID
'which is an IDENTITY column in the Orders table
AddHandler objDAOrders.RowUpdating, _
New OleDbRowUpdatingEventHandler(AddressOf OnRowUpdating)
AddHandler objDAOrders.RowUpdated, _
New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)
... etc ...

'----------------------------------------------------------------
'----------------------------------------------------------------

Sub OnRowUpdating(ByVal objSender As Object, _
ByVal objArgs As OleDbRowUpdatingEventArgs)

'event handler for the RowUpdating event
'see if its an INSERT statement. If so we need to save the
'temporary order ID allocated on the client while disconnected
'so we can update the child rows with the "real" order ID
'after the row has been inserted into the data store
If objArgs.StatementType = StatementType.Insert Then

'save the temporary order ID in the class-level variable
m_InsertOrderID = objArgs.Row("OrderID", DataRowVersion.Current)

End If

End Sub

'----------------------------------------------------------------
'----------------------------------------------------------------

Sub OnRowUpdated(ByVal objSender As Object, _
ByVal objArgs As OleDbRowUpdatedEventArgs)

'event handler for the RowUpdated event
'see if its an INSERT statement
If objArgs.StatementType = StatementType.Insert Then

'see if the insert was successful
'expect 1 in success, zero or -1 on failure
If objArgs.RecordsAffected = 1 Then

'get new order ID from IDENTITY column in this row.
'the InsertOrders stored proc returns the newly inserted row
'and this automatically updates the current values for the
'columns in this row, including the auto-generated OrderID
'because of Relation, also cascades updates to child table rows
Dim intNewOrderID As Integer = objArgs.Row("OrderID",
DataRowVersion.Current)

'update matching Orders row in original DataSet otherwise the
'rows will not be matched when the Merge method is used later
'because of Relation, also cascades updates to child table rows
Dim objTable As DataTable = m_ModifiedDataSet.Tables("Orders")
Dim arrRows(), objRow As DataRow
arrRows = objTable.Select("OrderID = '" & m_InsertOrderID & "'")
For Each objRow In arrRows
objRow("OrderID") = intNewOrderID
Next

'write message to Trace object
If m_CanTrace Then
m_Context.Trace.Write("UpdateDataSet", "Inserted Order row with
OrderID " _
& intNewOrderID.ToString())
End If

End If

End If

End Sub

This is the sample page that uses this approach:
http://www.daveandal.net/books/4923/updating-data/update-orders-demo.aspx
Other examples at:
http://www.daveandal.net/books/4923/
or email for more details...

 

[Jason L James]

Alex,

can this be done in an MS Access DB using an OleDB connection?

I am having tremendous difficulty in cascading my updates when the PK
is changed during the da.update command.

Many thanks,

Jason.

 

[Paul Nelson]

It is hard to know exactly how your system is set up, but I suspect
your difficulty could be that your DELIVERY_DETAILS table is using the
DELIVERFK as a primary Key. Primary Keys cannot be duplicated, so you
could only ever have one record with that value in there - it will not
allow you to enter any more. What you need it as follows:

DELIVERY
-DeliveryPK (Integer, PK, Identity)

DELIVERY_DETAILS
- DD_PK (Integer, PK, Identity)
-DeliveryFK (Integer)
-Serial number


I hope I haven't got completely the wrong end of the stick. Best of luck
- I was a newbie too once.

Paul.

 

[Jason L James]

If DeliveryFK is assocaited with a DELIVERY record,
that you should be able to retrieve the PK from that row. This
value can then be inserted into the newly created row
as the FK.

The secret is to get hold of the current parent (DELIVERY) record
and obtain the PK from it. I use a dataview and a datarowview
to extract the parent record I need and then I can obtain the
item from the datarow view and use that as the FK for all
newly created child rows.

Goodluck,

Jason.

 

[Duray AKAR]

Hi,

I don't know if you already solved this or not.
But it is easy.

I am assuming you use SQL server or MSDE as a database, and VS.NET as
the development environment. Here is how to do it step by step:

1) Create a "DataSet" in your project (dsDelivery).
2) Drag and drop the Delivery table from the Servers pane.
3) Set the AutoIncrementSeed and Step values for the "DeliveryID" column
to '-1'
4) Drag and drop the DeliveryDetails table to your DataSet.
5) Create a relation in your dataset by dragging the DeliveryID column
of your Delivery table onto DeliveryKey column of the DeliveryDetails
table.
6) DO NOT MODIFY ANY DEFAULT RELATION SETTINGS UNLESS YOU HAVE TO. If
you modify, make sure that you select "Cascade" for Update...
7) Add a Component to your project(DALDelivery).
8) Drag and Drop a SQLDataAdapter from the DATA tab on the Toolbox Pane.
9) Select to create new stored procedures, select your deliveries table,
the default settings already has "Refresh DataSet after Updating" option
enabled, so either keep it, or make sure it is checked.
10) Name the new procedures appropriately (e.g. DALDeliveriesSel,
DALDeliveriesUpd, ..Ins, ..Del )
11) Finish the wizard
12) Rename the SQLDataAdapter daDeliveries.
13) Do the same thing for DeliveryDetails Table.

At that point you should already hate the SQLHelper If you don't
think twice...

14) Go to the source code of your DALDeliveries component.
15) Add a "public int Load(dsDeliveries ds){}" method.
16) Type "int i = 0; i+= daDeliveries.Fill(ds); i+=
daDeliveryDetails.Fill(ds);return i;" in the method.
17) Add a "public int Save(dsDeliveries ds){}" method.
18) Type "int i = 0; i+= daDeliveries.Update(ds); i+=
daDeliveryDetails.Update(ds); return i;"
19) THE ORDER OF THE FILL AND UPDATE IS NOT RANDOM... UPDATE IN THE SAME
ORDER.

20) Use your DAL (Data Access Layer) Component in your application to
Load and save data...

You have clicked the mouse about 20 times, and typed less then 10 lines
of code...

I wish that was all about it , but it is not, UNLESS YOU REMOVE THE
FOREIGN KEY FROM YOUR DB. If you are not satisfied with just an index on
the DeliveryKey column in the DeliveryDetails table, then you will
suffer some trouble IF YOU HAVE DELETED ROWS. For deleted records, you
should call the update in REVERSE ORDER... So you will need to type
another 10 lines of code to get the deleted rows only, update and merge
them...


Hope it helps.