DataAdapter Update Behaviour

  • Thread starter Thread starter Theo
  • Start date Start date
T

Theo

Dear friends,

a summary of my problem: Update method adds new row in the
database (as expected) but then raises an exception as if
it failed to update.

I have a form with binded controls. Most of them bind to a
main table and some of them bind to a secondary table.
These tables are related to each other (One to One
relation in the back end database) but this relation is
not implemented in any way on the dataset. I just use a
query to get the second table based on the key value of
the main table.

The possible scenarios when filling the dataset are:

Main Table | Second Table
Key Value | Related Key Value
======================================
Exists | Exists
Exists | Does not exist
Does not exist | Does not exist


When rows exist in both tables, updates works fine.

When row exists only in main table, i create a new row in
the second table and updates work fine

When none of the tables return a row i create a row in the
first table, update it (works fine) and then i create a
row in the second table and try to update it. An exception
is raised that key value cannot be null and that a key
value must exist in the related table (the fist table). I
check the database and row has been added in the table.

Why is the exception raised then???
There is no relation between the two tables within the
dataset so i assume that the exception about the relation
is generated by the database. I also manually set the key
value in the second table. Each table contains a single
row. I checked the values in both tables and there isn't a
null value anywhere.
 
There isn't really enough information here to try to
reproduce the problem, could you email me an example? Are
you using any autonumber fields in the database and/or the
dataset? What are the command texts used for the Select,
Insert and Update for each of the data adaptors? Is the
dataset a typed or untyped dataset?

I suspect that the problem may be related to autonumber
fields, since these are allocated by the data table only
on a temporary basis, and when the Update is carried out,
the database will allocate its own value for the field
which may differ from that in the data table, causing a
constraint error when you write new rows to related tables.

The Visual Studio Data Adapator configuration wizard
accommodates this by creating a Select statement following
every insert to ensure that autonumber fields, defaults
etc are updated in the dataset following any save. If you
have constraints defined in your dataset, these values can
also be cascaded automatically to child rows to ensure
consistency.

If you are using your own hand-spun queries for the data
adaptor then the value for the foreign key in the second
table may not match the value assigned by the database for
the primary key, and you would get an error like the one
you receive.

Hope this helps,

Neil.
 
Neil thanks for your reply,

I am using an automumber field but this is in the first table that updates
without errors. It is a simple master-child table scenario where both tables
are displayed in the same form.
The key value in the master table is also the keyvalue in the child table. I
want to say again that I am not using any relationships in the dataset
between the two tables. I am just using the key value of the first table to
load a row from the second table and if that row does not exist I create it.
The select commands are simple like SELECT * FROM TableA WHERE ID=1 and for
the rest of the commands I use the command builder to generate them. I have
checked all the values before the update and they are fine. As I said before
although an exception is raised the update takes place successfully in the
database. The code is very simple as well:
create a datarow
set its values
add it to the table
update the table

Everything is created at run time including the dataset.

Thanks
 
Back
Top