Adding New Row With Relationship

I am using VB .NET 2003

Assume I have the following tables:

==ORDERS== ==ORDER_PRODUCTS==
OrderID (PK) OrderProductsID (PK)
Field1 OrderID (FK)
Field2 ProductCode (FK from anothertable)
Field3 Field1
etc etc

ORDERS, 1 ---------------- M, ORDER_PRODUCTS
(Linked via OrderID)

ORDERS, 1 -------- M, ORDER_PRODUCTS
(Linked via OrderID)

I have created the relationship for this in the XML Schema (the XSD
file), let's call it ORDERS_ORDERPRODUCTS

Now what I want to do is create a new order. So I add a new row to
ORDERS and then use my ORDERS DataAdapter to save the changes. But
since the OrderID is a PK autonumber it gets generated automatically.
So how do I know what the OrderID foriegn key is?? I need it to link my
Order and the Product. I suppose I could use:

dsDataset.Orders.Rows(dsTurbobraze.Orders.Rows.Count -
1).Item("OrderID").tostring

to get the auto-generated OrderID of the last added row, but what if
some Orders have been deleted from the DataBase in the past? The
OrderID's woudn't go up nicely in sequence, or would ADO keep a hidden
record of every OrderID, and never use it again?

How do I solve this seemingly easy problem?

Aziz,

You can solve this problem by not using autokeys in this kind of situations.
The GUID (unique identifier) excelent to overcome the most problems you have
said now.

Just my thought,

Cor


"Aziz" <aziz001@googlemail.com> schreef in bericht
news:1144338125.792033.75690@j33g2000cwa.googlegroups.com...
>I am using VB .NET 2003
>
> Assume I have the following tables:
>
> ==ORDERS== ==ORDER_PRODUCTS==
> OrderID (PK) OrderProductsID (PK)
> Field1 OrderID (FK)
> Field2 ProductCode (FK from anothertable)
> Field3 Field1
> etc etc
>
> ORDERS, 1 ---------------- M, ORDER_PRODUCTS
> (Linked via OrderID)
>
> ORDERS, 1 -------- M, ORDER_PRODUCTS
> (Linked via OrderID)
>
> I have created the relationship for this in the XML Schema (the XSD
> file), let's call it ORDERS_ORDERPRODUCTS
>
> Now what I want to do is create a new order. So I add a new row to
> ORDERS and then use my ORDERS DataAdapter to save the changes. But
> since the OrderID is a PK autonumber it gets generated automatically.
> So how do I know what the OrderID foriegn key is?? I need it to link my
> Order and the Product. I suppose I could use:
>
> dsDataset.Orders.Rows(dsTurbobraze.Orders.Rows.Count -
> 1).Item("OrderID").tostring
>
> to get the auto-generated OrderID of the last added row, but what if
> some Orders have been deleted from the DataBase in the past? The
> OrderID's woudn't go up nicely in sequence, or would ADO keep a hidden
> record of every OrderID, and never use it again?
>
> How do I solve this seemingly easy problem?
>

Where's the GUID? If I go into the XML Designer (the one where you can
drag and drop to create relationsips between tables) I cannot find it
in the list of types, the closest I can find is ID, IDREF, IDREFS.

Also, if I do use this GUID, can I still keep my Database type a
autonumber (it's an access file) or will it have to become a number?

Aziz,

Have a look at these links

Net
http://msdn.microsoft.com/library/d...dclasstopic.asp

SQL server
http://msdn.microsoft.com/library/d..._ua-uz_6dyq.asp

I hope this helps,

Cor


"Aziz" <aziz001@googlemail.com> schreef in bericht
news:1144397899.177746.299620@t31g2000cwb.googlegroups.com...
> Where's the GUID? If I go into the XML Designer (the one where you can
> drag and drop to create relationsips between tables) I cannot find it
> in the list of types, the closest I can find is ID, IDREF, IDREFS.
>
> Also, if I do use this GUID, can I still keep my Database type a
> autonumber (it's an access file) or will it have to become a number?
>

Thanks Cor, but I managed to fix it using David Sceppa's code:


daOrders.Update(dsTurbobraze, "Orders")
AddHandler daOrders.RowUpdated, AddressOf HandleRowUpdated


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()
Debug.WriteLine(e.ToString)
End If
End Sub

Very useful.