Parent-Child Relationship Headaches (add new records) (VB 2003)

[Aziz]

Right...I have an access Database with around 10 tables. I have
modelled only a few of these in the XML Schema (enough to get my
program working) and assigned the relevant DataRelations.

Anyway I'm having problems with a three table
Order-OrderProducst-Product relationship which stores the temporary
order information in a Shopping Basket datatable.


ORDER
OrderID (PK) (Autonum)


ORDERPRODUCTS
OrderProductsID (PK) (Autonum)
OrderID (FK)
ProductID (FK)

PRODUCT
ProductID (PK)


PRODUCT is read only and just used to store the list of products.


When I try to create a new record in ORDERPRODUCTS I get:
An unhandled exception of type 'System.Data.InvalidConstraintException'
occurred in system.data.dll

Additional information: ForeignKeyConstraint OrdersOrderProducts
requires the child key values (0) to exist in the parent table.

First I create a new ORDER (works fine), then I use some code to return
the value of the OrderID just created. I use this to create a new
ORDERPRODUCTS with the ProductID (which is already known and exists the
parent table). But I get the above error. Why? I'm sure I'm doing it in
the right order: ORDER>ORDERPRODUCTS. All the parents get created/exist
first before the child record is created. The child key values DO exist
in the parent tables.
Please help, this is driving me mad.


I use the following code:
Dim drOrders As DataRow = dsDataset.Orders.NewRow
Dim drOrderProducts As DataRow = dsDataset.OrderProducts.NewRow

drOrders.Item("ShippingName") = txtDeliveryName1.Text & " " &
txtDeliveryName3.Text
...
drOrders.Item("TotalPrice") = 100
dsDataset.Orders.Rows.Add(drOrders)
daOrders.Update(dsDataset, "Orders")
AddHandler daOrders.RowUpdated, AddressOf HandleRowUpdated
daOrders.Dispose()
conADOConnection.Close()

conADOConnection.Open()


Dim counter As Integer = 0
' For counter = 0 To dtShoppingBasket.Rows.Count - 1
drOrderProducts.Item("OrderID") = intTempOrderID
drOrderProducts.Item("ProductCode") =
dtShoppingBasket.Rows(counter).Item("ProductCode")
drOrderProducts.Item("AmountRequired") =
dtShoppingBasket.Rows(counter).Item("ProductAmount")
drOrderProducts.Item("PackingOptions") =
dtShoppingBasket.Rows(counter).Item("PackingOptions")
dsDataset.OrderProducts.Rows.Add(drOrderProducts)


Debug.WriteLine(drOrderProducts.Item("OrderID"))
Debug.WriteLine(intTempOrderID)

'Debug.WriteLine(dtShoppingBasket.Rows(counter).Item("ProductCode"))

Debug.WriteLine(dtShoppingBasket.Rows(counter).Item("ProductAmount"))

Debug.WriteLine(dtShoppingBasket.Rows(counter).Item("PackingOptions"))

daOrderProducts.Update(dsDataset, "OrderProducts")
End Sub

Private Sub HandleRowUpdated(ByVal sender As Object, ByVal e As
OleDb.OleDbRowUpdatedEventArgs)
If e.Status = UpdateStatus.Continue AndAlso e.StatementType =
StatementType.Insert Then
e.Row("OrderID") =
Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString())
e.Row.AcceptChanges()
intTempOrderID = CInt(e.ToString) 'global variable
End If
End Sub

 

[Cor Ligthert [MVP]]

Azis,

Was you the one who told that you had your complete solution done, while I
was giving you the advice to look at using the Guid (etc with some links)
instead of the autonumber?

I did look at David solution now. It is now not so liked anymore. By
instance when you use the @@Identity to get the real identifier back in a
seperated process as you show, you get back the last created identifier by
whatever/whoever.

It is in SQLServer even easier to do instead of OleDB what you use. However,
still I don't use an autonumber as well there.

I hope this helps,

Cor

 

[Aziz]

Yep that was me. At the time David's WAS working, but now it doesn't
work at all, I have no idea why. I keep getting a "ExecuteScalar:
Connection property has not been initialized" if I try to add the
handler before the Update command is called.

I read about the SQLServer solution, but unfortunately I'm using
Access.

I'll give the GUID approach a try but it seems unnecessarily
complicated and I can't make much sense of the article on MSDN.
*Sigh*...Oh well.

 

[Cor Ligthert [MVP]]

Aziz,

Maybe does this bring you on the route, I will not call that complex.

http://www.vb-tips.com/default.aspx?ID=b57d0ce2-3f5f-47d4-9663-fee3733fcd6f

I hope this helps,

Cor

 

[s.bussing]

Hi Aziz,

what you're doing is very dangerous. In your case there is a possiblity
that some else also inserted a record in the orders table and that you
will retrieve the wrong value for your OrderId. (your construction
cmdGetIdentity.ExecuteScalar().toString()). I use a more complex
construction with 7 nested tables in a typed dataset. They all depend
on the new id of there parent. The best thing to do is first fill the
whole typed dataset and then update the dataadapters.

I wrote my program in C# and in multiple layers, so it's hard to add my
code here, But if you intrested I can sent my program. I have to warn
you because my native language is dutch so there are a few dutch words
in it, just send me a mail.

 

[Aziz]

Hi,

My program is only a single user program (hence the reason for not
using something other than Access) so the problem of concurrency isn't
really an issue.

Thanks for the offer of the program but I'm only a beginner programmer
and I doubt I'd understand it (did some C and Java years ago, but have
forgotten it all now)