Parent Child Update problem

[penno90]

hi people

i realise that this is a subject that has been covered a lot here but can't quite seem to nail this problem i'm having....

i have a vb.net 2008 frontend with access 2008 database. Parent 'DGV Clients' and Child 'DGV Orders' on a form. They are linked successfully but my problem lies in making updates.

If i add a new client and then an order for the new client, it appears to save ok, but when i try and reload the app. i get the error message that 'ContraintException was unhandled' on my Orders tableapapter.fill.

i can see what the problem is if i open my access database directly. the new Client is being added to the database and being assigned PK of ClientID, but the Child record is not changing to the same ID and is staying with -1. Hence, when i try to reopen the app i get the problem.

I have set up Relation and Foreign key constraints on the relation to 'cascade'. Changed update code of table adapters to update as follows

ClientsTableAdapter.Update(Bd1DataSet.Clients)
OrdersTableAdapter.Update(Bd1DataSet.Orders)

as i understand that the database needs to obviously add parent records to the db first. i am sure i am missing something simple here????

any help greatly appreciated.

 

[cjard]

It sounds like, when the database calculates the AutoNumber for the parent record, the tableadapter/dataset code is not picking up the change and setting it in the parent (which the client side relation then causes to transmit into the child table)

Right click your parent tableadapter, click Advanced.. Tell me, is the tickbox "Refresh Changes in the Database Back Into The Dataset" (or words to that effect) box ticked?

 

[penno90]

Hiya cjard

when i right click on the parent table adapter i only have options for View Code, Edit Queries, Add Query, Preview Data and Properties. In the properties i don't see anything like what you're describing, which is a shame cos it sounds absolutely the kind of thing that i thought i would need! I am using VB.Net 2008.

Any ideas?

 

[cjard]

in vs2005 i would:

right click tableadater
choose configure
in the first one or two pages of the wizard (i forget which, and no vs in front of me i'm afraid) there is an advanced button
the advanced page contains 3 options:
* (something i forgot, maybe generate I/U/D statements)
* use optimistic concurrency
* refresh the dataset

 

[penno90]

Hi cjard

i found the options now! iwas right clicking the table adapter on my form and not in the dataset.xsd

the third box is there as you said for 'Refresh the data table'. Unfortunately this is greyed out, and i am unable to tick the box? any idea why that might be?

cheers

 

[cjard]

I was sure this worked on Access but more and more I'm seeing users who say this option is greyed out for Access databases. Have a read of the DW2 link in my signature, section on:

Displaying Related Data
Saving Related Data

 

[Stonkie]

Do both tables have defined primary keys and using autonumbers? I mean formally defined as such in Access? It would be impossible for the table adapter to know what filter to give the select if it can't tell which column is the primary key and what is the next value of the newly inserted autonumber.

I'm not sure if that helps, but I've had to work with poorly designed Access databases and I had a few options disabled like this because sometimes, the primary key was just a normal autonumber without a primary key status within Access and the designer couldn't tell it was the primary key. It may be something similar that is happening, like you forgot to set a property on a field somewhere...

Just an idea, if there are more than one autonumber on the same table (which is not allowed on SQL Server, but I think it is in Access), it may be the reason why the reason why the designer cannot tell which one of the two autonumber sequence to use for telling what is the newly added primary key.

 

[penno90]

Both tables have defined primary keys using autonumbers.

Followed the link in your signature cjard. i went through the code for 'Walkthrough: Saving Data to a Database (Multiple Tables)' making appropriate changes for my code. Still the same problem. Interestingly enough at the bottom of this walkthrough, someone has posted ...

Adding a customer and order at the same time is giving me an error. A message pops up that complains about a foreign key constraint. This occurs even though the code above has the customer changes taking place before adding orders.

Unfortunately still no solution though??

 

[Stonkie]

It is possible that that person's problem (and maybe yours) is that he didn't set the relation in the dataset designer to "ON UPDATE CASCADE". You must do this manually so when the data is refreshed from the database when you insert the master row, it updates the id of the master row and the detail's foreign key refering to the row is updated as well (instead of remaining to -1 which is not a real master row).

To change this setting in the dataset designer, double click on the lines that appear between your tables and you will have access to the relationship's properties.

Maybe this is what is happening to you? I always thought this should have been the default to begin with, but I think it takes the settings from your database. And since in real life, the id from your database will never change, you don't need to do this on your database, only on the dataset.

 

[cjard]

OK, well the option may be greyed out because:

It is not available, or
It always happens and you cannot turn it off

I'd have expected unticked-greyed in the former and ticked-greyed in the latter. However, we can answer this question more directly:

Put a breakpoint on the line BEFORE you call Update. Look at the row values using the dataset visualizer (highlight datatable variable, point to it, click magnifying glass in the tooltip). You see your normal autonumber from the DATASET, right?

Now step over the Update (point of note: youre Update() the ORIGINAL datatable, NOT the copy that GetChanges gives you, right? Do not use GetChanges()!)

Now look at the datatable visualizer again. Crucial question: Did the value change (to the autonumber from the DB) or not?


If your DB is blank or empty then PLEASE DONT START your dataset autonumber from the SAME seed as the DB! (For the reason that if the DS calc an ID of 1, and then the DB also calcs 1, it wont look like it changed. Use a seed of -1 and a step of -1 in the dataset if needs be)

 

[penno90]

Thanks Stonkie, but i already set the relationship to Cascade.

The option for refresh the dataset is greyed out and unticked.
I have inserted break points and checked the dataset visualizer and see that the autonumber from the dataset always remains as assigned by the dataset.
The seed is set to -1 with step of -1 (i think this is the vb default)

i can see here by opening my database after updating, that the new Client is being assigned an autonumber by the DB and that the new Order is being assigned a new autonumber too. the problem is that somewhere along the line the new order is not being associated with the new client in the DB and in the Orders table the Client ID stays as -1.

Am i to understand then, that using Access it is not possible to add a new parent and child at the same time or is there some way of doing this in code?

 

[cjard]

see that the autonumber from the dataset always remains as assigned by the dataset.

Oh dear. The simplest solution would actually be to use a better database. SQLServer comes in a variety of free, small editions, Oracle has a freeware version that leaves Access in the dust and if youre really masochistic, you can take a look at MySQL and PostGresQL

However, you should take a read of this article:
Retrieving Identity or Autonumber Values (ADO.NET)

It describes a way to achieve your goal on access.. Be warned; it's kinda ugly. Sorry I didnt provide this info before: I only just rana google for it because I genuienly thought "Refresh the DataSet" was available on access

 

[penno90]

Hi cjard

Thanks for the help/advice. I actually started building my database in SQL Server Express 2005 last night to try it out as a pre-emptive solution. As you said the 'Refresh Data Set' option is ticked by default and not greyed out and works perfectly! I had been a bit anxious about trying to learn a new database at the same time as trying to learn VB, but I am so happy to have this working that it was definitely worth it. Since i also eventually want my app to be multi-user this was probably the sensible way to go at an early stage.

Thanks for everyone's help as always

James