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

10 Apr 2006 9:28 AM

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

10 Apr 2006 10:44 AM

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

Show quote

"Aziz" <aziz***@googlemail.com> schreef in bericht
news:1144661293.966259.184790@v46g2000cwv.googlegroups.com...
> 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
>

10 Apr 2006 12:28 PM

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.

10 Apr 2006 12:38 PM

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

Show quote

"Aziz" <aziz***@googlemail.com> schreef in bericht
news:1144672130.721375.243700@t31g2000cwb.googlegroups.com...
> 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.
>

10 Apr 2006 8:12 PM

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.

11 Apr 2006 8:20 AM

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)