How to automatically update multiple datatables using datasets ?

[Bauhaus]

Hello,

I made a database (http://users.skynet.be/fc042048/Pizza.jpg) in Access
which I want to use via typed datasets on my website. Problem is, if I i.e.
want to edit the datatable "Pizza" in a gridview, I get the error "Can't
delete or update the record because there are related records in the
datatable "PizzaOnderdelen" (means Pizza Ingredients).
What do I have to do to automatically update the related tables ? Do I need to adjust my dataset file (contains only the table 'Pizza" a.t.m.) or do I have to make changes in my VB code ?

(Here's the source code: Solution
(http://users.skynet.be/fc042048/Pizza (solution).rar) and Aspx files
(http://users.skynet.be/fc042048/Pizza (aspx files).rar))

Short description:

1) Project Pizza
- Beheerdersmodule.aspx: This page is used by the administrator to update, insert or delete datatables.
2) Classlibrary DbManager: Dll file which contains the dataset,classes and methods for the Pizza table:
- PizzaDbManager: Used to make the connection with the database.
- PizzaDTO: Class describing a pizza object.
- PizzaSet.xsd: the dataset
- PizzasManager: contains the methods to update, insert or delete the Pizza datatable.

Bauhaus.

 

[Bauhaus]

Basically, the problem is this: the field PizzaNr is a primairy key in 3 tables (Pizza, PizzaOnderdelen and PizzaPrijs). If PizzaNr was a foreign key in the other 2 tables, then it wouldnt be a problem.
So I have to adjust my VB code so that the 3 tables are updated together, but how do I do that ? Could I use an SQL statement ?

 

[cjard]

it sounds like the structure isnt quite right.. i think you'd have an Ingredients table, a Pizza table, and a PizzaIngredients table

They are related like:

Ingredients 1---M PizzaIngredients N----1 Pizzas


There should be only one record for Mushroom, or Beef. There should be only one record for the House Special or the Super Hot. To record that a House Special has both mushroom and beef, you write two records into PizzaIngredients

 

[Bauhaus]

The structure seems to be right: In Access I could ad a new PizzaNr in Pizza without any problem. And I could use this new PizzaNr in the other tables as well... But adding a new PizzaNr in PizzaIngredients & PizzaPrice without first adding it in Pizza gave an error, as expected.
So it doesnt seem to be a referential integrity problem :s

Pff, I'm clueless what to do

 

[Bauhaus]

Found the problem: in my update method, the sql statement also tried to update PizzaNr:

"update Pizza set PizzaNr=@PizzaNr, Pizzanaam=@Pizzanaam, Beschrijving=@Beschrijving where PizzaNr=@PizzaNr"

So I had to ommit 'PizzaNr=@PizzaNr' after the set command.

But now I got a new error: "Violation on concurrency: 0 of 1 records are changed by the UpdateCommand." :s

Any suggestions what could be wrong ?

 

[cjard]

You downloaded 1 record
You edited 1 record
You told the dataadapter to save 1 record
The UPDATE query affected 0 rows
DataAdapter presumes this is because another user nipped in and changed the row you were editing (The record ID PizzaNr does not exist)

Why this has occurred in your case, I dont know. Only you can answer that

 

[MattP]

it sounds like the structure isnt quite right.. i think you'd have an Ingredients table, a Pizza table, and a PizzaIngredients table

They are related like:

Ingredients 1---M PizzaIngredients N----1 Pizzas


There should be only one record for Mushroom, or Beef. There should be only one record for the House Special or the Super Hot. To record that a House Special has both mushroom and beef, you write two records into PizzaIngredients

Prudent planning can save you lots of headaches in the future.

Cjard is trying to help you out by suggesting normalizing your database to 3NF or BCNF.

 

[Bauhaus]

Thanks for the replies.

I finally figured it out:

With the Access database, the field PizzaNr is not an autonumber. When I tried the same database, but in sqlexpress, where I did give PizzaNr an autonumber, I also got an error, but a different one: "Cant convert DBNull to Integer".

So it had something to do with PizzaNr, since that was the only field of type integer.
Then it suddenly hit me when I looked again at my gridview: in the gridview, PizzaNr starts from 0...
So what happens is this: when the field is an autonumber, the gridview/objectdatasource knows the field starts from 1. When the field isnt an autonumber however, the field should start from 0 ...but in Access I gave my first non-autonumber PizzaNr the value 1. Result: the code looks for a PizzaNr with value 0 but doesnt find it so it throws an exception.

The reason I got the "Concurrency violation" was also because of this, 'cos the code I wrote to detect multi-user conflicts also looks for a PizzaNr with value 0...

So when I ommitted the code to detect multi-user conflicts AND changed PizzaNr in my Access database to an autonumber, it worked fine.

 

[cjard]

So when I ommitted the code to detect multi-user conflicts ... it worked fine.

Yeah, like when I pulled the fuse out of the computer that controls the safety systems like the airbag and stuff in my car.. i found out that that annoying "bing bing bing" noise that told me I hadnt put my seat belt on.. it stopped! Woohoo! Now I dont have to put my seatbelt on to stop that noise!

 

[Bauhaus]

Damn, I was so happy about finding out about the autonumber thingy, when suddenly someone blew up my sarcasm detector.

Let me rephrase that last sentence: I will have to adjust the multi-user detection code.

 

[cjard]

___________a