Adding New Row (Getting Back Autonumber/Adding Parent-Child record)



Newbie queston here. I am using VB .NET 2003, with an Access 2003
Database and Ole connection in VB.

Assume I have the following tables:

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

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 -

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?

Basically I just need to return the value of the OrderID autonumber as
it's created.

I posted this in the VB newsgroup and someone recommended using a
GUID(Unique Identifier), but I coudn't find much relevant information
on this. Is there a more straighforward way.


I've found a post here by David Sceppa that is about what I want to do.
But it doesn't work.

Here's what I have:

Private Sub btnPlaceOrder_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles btnPlaceOrder.Click
Dim drOrders As DataRow = dsDataset.Orders.NewRow

drOrders.Item("ShippingName") = "Blah"
drOrders.Item("TotalPrice") = 100

daOrders.Update(dsDataset, "Orders")
AddHandler daOrders.RowUpdated, AddressOf HandleRowUpdated
End Sub

Private Sub HandleRowUpdated(ByVal sender As Object, ByVal e As
Debug.WriteLine("1: " & e.Row("OrderID").ToString)
If e.Status = UpdateStatus.Continue AndAlso e.StatementType =
StatementType.Insert Then
e.Row("OrderID") =
Debug.WriteLine("2: " & e.Row("OrderID").ToString)
Debug.WriteLine("3: " & e.Row("OrderID").ToString)
End If
End Sub

First time I click the button this it doesn't return anything (but it
does create a new order record as normal), second time I click it
(creates another record as normal) the first Debug.Writeline returns
the correct value but it then immediately crashes with this message:

An unhandled exception of type 'System.InvalidOperationException'
occurred in
Additional information: ExecuteScalar: Connection property has not been

This message points to the daOrders.Update(dsDataset, "Orders")

Any ideas?

Aytaç ÖZAY


If you can't use stored procedures for adding new rows, you can't get
identity column's value. But when you update the database then you can get
the dataset's latest version so you have the records just added in the
dataset's datatable, you can use dataview for selecting the OrderID's max
value, then you can use it anywhere you want.

Briefly, there is no way for getting the OrderID in one shot except using
stored procedures.

Have a nice work,
Aytaç ÖZAY


Thanks Aytac, not sure I understand you (hvn't come across stored
procedures yet) but I managed to get David Sceppa's code working 2
minutes after I posted the above reply (typical !). It was just a case
of using e.ToString to return the value rather than e.Row("OrderID").

Aytaç ÖZAY

You're wellcome:) In my opinion Sceppa's book is a very good helper book,
good choice.

Have a nice day,

Aytaç ÖZAY

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question