Problems with using typed dataset and Access 2000

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
Hi

I'm trying to update an Access 2003 database with the classic Orders, OrderDetails set up.

Orders
-------
OrderId (autonumber) (PK)

OrderDetails
-------
OrderDetailsId (autonumber) (PK)
OrderId (FK)

Heres my code

Main calling function
VB.NET:
OrderId = FillOrdersDataRow(trans)

' Fill order details record
If OrderId > 0 Then FillOrderDetailsDataRow(trans, OrderId)

FillOrderDataRow
VB.NET:
    Private Function FillOrdersDataRow(ByVal trans As TransactionType) As Integer

Dim drOrders As dsDataset.OrdersRow = dsDataset.Orders.NewOrdersRow

' Add the values
drOrders.transactionId = trans.TransactionID
drOrders.orderQuantity = trans.QuantityPurchased
....
....

' Add the new row to the table
Me.dsDataset.Orders.AddOrdersRow(drOrders)

' Update the datatables to the database
Me.OrdersTableAdapter.Update(Me.dsDataset.Orders)

Return drOrders.orderId

FillOrderDetailsDataRow
VB.NET:
    Private Sub FillOrderDetailsDataRow(ByVal trans As TransactionType, ByVal OrderId As Integer)

Dim drOrderDetails As dsDataset.OrderDetailsRow = dsDataset.OrderDetails.NewOrderDetailsRow

' Fill the values
drOrderDetails.orderId = OrderId
drOrderDetails.ItemId = trans.Item.ItemID

' Add the new row to the table
Me.dsDataset.OrderDetails.AddOrderDetailsRow(drOrderDetails)

' Validate and update the datatables to the database
Me.Validate()
Me.OrderDetailsTableAdapter.Update(Me.dsDataset.OrderDetails)

Trouble is this sometimes works but most of the time it doesn't. The OrderId returned from FillOrdersDataRow is not the autonumber from the database, its just a increment of how many rows we have inserted and starts from 0.

Any ideas?

Thanks

Darren
 
The logic for me is the easy bit. Getting bloody ado.net to return the orderid to me is the confusing part.

If that worked 100% I can do some proper testing as the code is written to do what i want.
 
OK will Ive found this Microsoft article that seems to points me in the right direction

http://msdn2.microsoft.com/en-us/library/ks9f57t0(VS.71).aspx

VB.NET:
    ' Connection etc etc

    ' Fill the DataSet.
    Dim catDS As DataSet = New DataSet
    catDA.Fill(catDS, "Categories")

    ' Add a new row.
    Dim newRow As DataRow = catDS.Tables("Categories").NewRow()
    newRow("CategoryName") = "New Category"
    catDS.Tables("Categories").Rows.Add(newRow)

    ' Include an event to fill in the Autonumber value.
    AddHandler catDA.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)

    ' Update the DataSet.
    catDA.Update(catDS, "Categories")

    nwindConn.Close()
  End Sub

  Private Shared Sub OnRowUpdated(sender As Object, args As OleDbRowUpdatedEventArgs)
    ' Include a variable and a command to retrieve the identity value from the Access database.
    Dim newID As Integer = 0
    Dim idCMD As OleDbCommand = New OleDbCommand("SELECT @@IDENTITY", nwindConn)

    If args.StatementType = StatementType.Insert
      ' Retrieve the identity value and store it in the CategoryID column.
      newID = CInt(idCMD.ExecuteScalar())
      args.Row("CategoryID") = newID
    End If
  End Sub

catDA is a dataAdapter. How do I get the dataAdapter as Im using TableAdapters as thats what VS gives me from using Typed Dataasets?
 
Last edited:
Look at this link:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=189482&SiteID=1&PageID=1

Here's what I did to make my code work:

Add this into the dataset.vb class:
VB.NET:
Namespace dsDatasetTableAdapters
    Partial Public Class OrdersTableAdapter
        Public ReadOnly Property MyAdapter() As System.Data.OleDb.OleDbDataAdapter
            Get
                If (Me._adapter Is Nothing) Then
                    Me.InitAdapter()
                End If
                Return Me._adapter
            End Get
        End Property
    End Class
End Namespace

Then before you update the datatable add this line:

VB.NET:
' Include an event to fill in the Autonumber value.
AddHandler Me.OrdersTableAdapter.MyAdapter.RowUpdated, New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)

and add a new routine:
VB.NET:
Private Sub OnRowUpdated(ByVal sender As Object, ByVal e As OleDbRowUpdatedEventArgs)
        If e.StatementType = StatementType.Insert Then
            Dim getKey As New OleDbCommand("SELECT @@IDENTITY", EOS.DataConnection.getConnection)
            e.Row("OrderID") = getKey.ExecuteScalar
            getKey.Dispose()
        End If
End Sub
 
Last edited:
Back
Top