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

7 Apr 2006 8:33 AM

Aziz

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:

==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)

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?

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.

7 Apr 2006 11:10 AM

Aziz

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
        dsDataset.Orders.Rows.Add(drOrders)

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

    Private Sub HandleRowUpdated(ByVal sender As Object, ByVal e As
OleDb.OleDbRowUpdatedEventArgs)
          Debug.WriteLine("1: " & e.Row("OrderID").ToString)
        If e.Status = UpdateStatus.Continue AndAlso e.StatementType =
StatementType.Insert Then
            e.Row("OrderID") =
Int32.Parse(cmdGetIdentity.ExecuteScalar().ToString())
                 Debug.WriteLine("2: " & e.Row("OrderID").ToString)
            e.Row.AcceptChanges()
            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 system.data.dll
Additional information: ExecuteScalar: Connection property has not been
initialized.

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

Any ideas?

7 Apr 2006 11:14 AM

Aytaç ÖZAY

Hi,

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

Show quote

"Aziz" <aziz***@googlemail.com> wrote in message
news:1144398831.884159.60580@j33g2000cwa.googlegroups.com...
> 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:
>
> ==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)
>
> 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?
>
> 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.
>

7 Apr 2006 11:21 AM

Aziz

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").

7 Apr 2006 1:50 PM

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

Show quote

"Aziz" <aziz***@googlemail.com> wrote in message
news:1144408898.287957.68170@i40g2000cwc.googlegroups.com...
> 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").
>