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
 

dazlerd

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

I removed the tables from the dataset.xsd, and also the tableadapters from the form designer. I then added them back in.

It worked for perfectly for 3 test runs over a days time range each.

I then removed all records from the database and ran the test again using 5 days worth of data

Its gone back to giving me a returned OrderId of 1 (incremental) and of course I get an error that it cant insert into OrderDetails as no corresponding record exist in Orders.

Weird.
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,053
Programming Experience
10+
I'm confused. I understand "Fill" to be a download from a DB, but here your FIll methods appear to upload to a db.. ?

arg81 and jmcilhinney will have more input on this, because they use autonumber in this way.. I use oracle sprocs only, and they always work properly.. I've never tried the AutoIncrement of a DT in conjuctions with Access/standard IUD SQLs
 

dazlerd

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

"Fill" would not be anything like the fill of a datatable/dataset.

it could be replaced with "Load", or "get" or "Complete" or even as you say "Upload".

D
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
i am "filling" the row with the values I need before updating the db.

Nowt wrong with that !! :)

Anyway....

Order.OrderId and OrderDetails.OrderDetailsId properties
---------------------------
AllowDbNull: false
AutoINcrement: True
AutoIncrementSeed: 0
AutoIncrementStep : 1
MaxLength: - 1
ReadOnly: False
Source: OrderId
Unique: True

OrderDetails.OrderId
---------------------
AllowDbNull: True
AutoIncrement: False
AutoIncrementSeed: 0
AutoIncrementStep : 1
MaxLength: - 1
ReadOnly: False
Source: OrderId
Unique: False
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
Odd, it seems to only error when the database is empty.

If it errors and I restart the app it works fine.

If I empty the db and restart it errors.
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
and why cant i do this? It errors saying the updated affected 0 records.

VB.NET:
' Search for the OrderDetails records with OrderId
Dim dr() As dsDataset.OrderDetailsRow = Me.dsDataset.OrderDetails.Select("OrderId = " & orderId)

    ' Is it found
    If dr.Length > 0 Then

    ' Update it
    dr(0).orderId = LastOrderId

' Update this change
Me.OrderDetailsTableAdapter.Update(Me.dsDataset.OrderDetails)

End If

The record in the datatable is modified

? dsDataset.OrderDetails.Rows(3).RowState
Modified {16}
 
Last edited:

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,053
Programming Experience
10+
It errors saying the updated affected 0 records.

The rowstate is modified, but when the tableadapter executed the UPDATE sql, the update affected no records.

Erm. I cant really explain how thats a problem; to me it's obvious. You download a row, you change it, you try to send it back but the UPDATE didnt update anything, it means either your PK is crap or someone else changed the record so it no longer had the same PK as the row you hav elocally.. The local row has no mathcing remote row. Geddit?
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
I hear what you are saying but I cant figure out why. The Orderdetails table has a primary key, OrderDetailsId (autonumber) column which I am not changing.

Im changing the value of the OrderId which is to say im changing which Order row it relates to in the db.

I can open access and change the OrderId manually so its not a referential integrity issue. (as far as OrderId goes anyway)
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
Looks like its connected to the reason this post was started in the first place.

The Order database table and the datatable 'Order' have matching OrderId's.

The Orderdetails database table uses an autonumber and is up in the thousands now, the OrderDetails datatable starts from 0.

Bastard thing. So it is trying to update a record in the db which er, cough, er doesnt exist, cough.

Thanks
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
When the db is empty the order datatable doesnt know what value to start the OrderId primary key as so it starts from 0. The access db however knows the next record is going to be 3021. Then I call the update on the orderdetails table using orderid = 0 and of course it fails.

If the db has anything in it, the orders datatable is filled with the contents of the db and then the update knows it to give the next OrderId so the value then is 3021. Therefore OrderDetails can update as it has a valid OrderId.

So why is this? Why does the datatable not get returned the orderid after the record is inserted into the database?
 

cjard

Well-known member
Joined
Apr 25, 2006
Messages
7,053
Programming Experience
10+
When the db is empty the order datatable doesnt know what value to start the OrderId primary key as so it starts from 0. The access db however knows the next record is going to be 3021. Then I call the update on the orderdetails table using orderid = 0 and of course it fails.

If the db has anything in it, the orders datatable is filled with the contents of the db and then the update knows it to give the next OrderId so the value then is 3021. Therefore OrderDetails can update as it has a valid OrderId.

So why is this? Why does the datatable not get returned the orderid after the record is inserted into the database?

Normally we would have on the client side:

2 datatagbles
one datarelation between PK and FK that is cascading updates
pk is autoinc, FK is not
insert the PK record
db calcs new value
value is retreived auto by ADO.NET
change in PK value causes FK value to change (datarelation does donkeywork)
because FK is now updated to PK value, when inserting or updating (it should insert, no?) should be fine.. no auto needed

this is how you have it set up, right? haveyou read DW2 on displaying and saving related data?
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
I need:

2 datatables
one datarelation between PK and FK
pk is autoinc, FK is not
insert the PK record
db calcs new value
value is retreived auto by ADO.NET (only works if db contains data as then fill will load)
insert orderdetailsrecord.

Then i need to
Select all records(with specific criteria) from orders tbl
change the PK value in the corresponding FK record.
Delete the PK record no longer used.

I didnt have the cascading updates but there was a 'relation only' relation.

I dont update the PK (how can i its an autonumber?), I update the FK to look at a different PK record. Then I delete the PK that is no longer used.

I changed the relation to be cascading updates but its still the same.


I have
VB.NET:
Orders                                OrderDetails
------------                        -------------
OrderId  Quantity                  OrderDetailsId (PK)       OrderId
1000      1                            1500                         1000
1001      2                            1501                         1001
1002      1                            1502                         1002

This gets changed to 
Orders                                OrderDetails
------------                        -------------
OrderId  Quantity                  OrderDetailsId (PK)       OrderId
1002      4                            1500                         1002
                                          1501                         1002
                                          1502                         1002

as they are all part of the same despatched order.

I have read it, but that was when you helped me out last year, so Ill read again.

Thanks
 
Last edited:

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
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.
 

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
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:

dazlerd

Well-known member
Joined
Sep 29, 2006
Messages
52
Programming Experience
5-10
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:
Top Bottom