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
 
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:
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
 
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
 
Talk about calling a spade a bucket.. ;)

What are the properties on the autoincrement fields in the dataset?
 
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
 
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.
 
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:
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?
 
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)
 
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
 
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?
 
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?
 
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:
Back
Top