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


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.

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?

__________________

DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

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?

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

__________________

DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

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

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

__________________

DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ

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.

__________________
The human mind's capability to comprehend abstract concepts is limited to the vocabulary it can use to describe it. The more precise the building blocks, the more complex the thoughts that can emerge... Sounds like the evolution of programming languages doesn't it?

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


Unfortunately still no solution though??

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.

__________________
The human mind's capability to comprehend abstract concepts is limited to the vocabulary it can use to describe it. The more precise the building blocks, the more complex the thoughts that can emerge... Sounds like the evolution of programming languages doesn't it?

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)

__________________

DW1: Data Walkthroughs 1.1...DW2: Data Walkthroughs 2.0...DDS: The DataSet Designer Surface...ANO: ADO.NET2 Orientation...DAN: Deeper ADO.NET...DNU...PQ