ASP/ADO/VB.NET: How to update multiple datatables with typed datasets ?

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 update the table ? Do I need to adjust my dataset
file (contains only the table 'Pizza") or do I have to change my VB code (I
made a Pizza object and update/delete/insert methods for the Pizza table) ?

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

Bauhaus.

Scott
Sat May 10 16:55:54 CDT 2008

The message you are getting is coming from the database and it means that
you have related records that will be affected if you edit the "Pizza"
table. The database won't allow your edit because the database is enforcing
referential integrity.

Either change that settting in the database (not recommended) or make an
edit that is allowed by the database.

-Scott

"Bauhaus" <amineptine@hotmail.com> wrote in message
news:e80UU6rsIHA.4492@TK2MSFTNGP02.phx.gbl...
>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 update the table ? Do I need to adjust my dataset
> file (contains only the table 'Pizza") or do I have to change my VB code
> (I made a Pizza object and update/delete/insert methods for the Pizza
> table) ?
>
> (Here's the source code: Solution
> (http://users.skynet.be/fc042048/Pizza%20(solution).rar) and Aspx files
> (http://users.skynet.be/fc042048/Pizza%20(aspx%20files).rar))
>
> Bauhaus.

Bauhaus
Sun May 11 09:58:55 CDT 2008

> Either change that settting in the database (not recommended) or make an
> edit that is allowed by the database.

The problem is I don't know how to edit it !

Scott
Sun May 11 15:56:58 CDT 2008

This isn't really a .NET issue, it's an access issue coupled with your
database design. I would suggest that you open Access and attempt an edit
manually and see if the database will allow it. Some edits won't be allow
due to the referential integrity and some will. We can't tell you which
will work and which won't, as it is something that is specific to your
database.

Once you know how to edit your tables manually, you can write .NET code to
do it programmatically.

-Scott

"Bauhaus" <amineptine@hotmail.com> wrote in message
news:%23rNxYe3sIHA.3680@TK2MSFTNGP05.phx.gbl...
>> Either change that settting in the database (not recommended) or make an
>> edit that is allowed by the database.
>
> The problem is I don't know how to edit it !

Bauhaus
Mon May 12 07:20:30 CDT 2008


"Scott M." <smar@nospam.nospam> schreef in bericht
news:eQ%23Ohk6sIHA.1772@TK2MSFTNGP03.phx.gbl...
> This isn't really a .NET issue, it's an access issue coupled with your
> database design. I would suggest that you open Access and attempt an edit
> manually and see if the database will allow it. Some edits won't be allow
> due to the referential integrity and some will. We can't tell you which
> will work and which won't, as it is something that is specific to your
> database.

In Access I added a new PizzaNr in Pizza without any problem. And I could
use this new PizzaNr in the other tables as well...
So it doesnt seem to be a referential integrity problem :s

Scott
Mon May 12 08:36:17 CDT 2008

In your original message, you say you get the error when you attempt to
update or delete a record. Below you say everything is fine when you add
something. These are different operations.

Try updating or deleting a record in Access. The message you got is clearly
a referential integrity message, but adding a new item is not necessarially
going to break referential integrity.

-Scott


"Bauhaus" <amineptine@hotmail.com> wrote in message
news:u$OsjqCtIHA.1436@TK2MSFTNGP05.phx.gbl...
>
> "Scott M." <smar@nospam.nospam> schreef in bericht
> news:eQ%23Ohk6sIHA.1772@TK2MSFTNGP03.phx.gbl...
>> This isn't really a .NET issue, it's an access issue coupled with your
>> database design. I would suggest that you open Access and attempt an
>> edit manually and see if the database will allow it. Some edits won't be
>> allow due to the referential integrity and some will. We can't tell you
>> which will work and which won't, as it is something that is specific to
>> your database.
>
> In Access I added a new PizzaNr in Pizza without any problem. And I could
> use this new PizzaNr in the other tables as well...
> So it doesnt seem to be a referential integrity problem :s

Bauhaus
Mon May 12 15:15:23 CDT 2008

> Try updating or deleting a record in Access. The message you got is
> clearly a referential integrity message, but adding a new item is not
> necessarially going to break referential integrity.

Updated a record in Access: PizzaNr couldnt be changed since its a primairy
key in several tables, but Pizzaname wasnt a problem.
But I found the problem: in my update method, the sql statement also tried
to update PizzaNr, so I had to ommit it.

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 ?

Scott
Mon May 12 18:35:38 CDT 2008

Concurrency errors indicate that a change being attempted conflicts with the
reality of the data (i.e. two users editing simultaneously: one simply
updates a field, while the other person deltes the record and the delte is
committed before the update is).

-Scott

"Bauhaus" <amineptine@hotmail.com> wrote in message
news:%23YwfrzGtIHA.672@TK2MSFTNGP02.phx.gbl...
>> Try updating or deleting a record in Access. The message you got is
>> clearly a referential integrity message, but adding a new item is not
>> necessarially going to break referential integrity.
>
> Updated a record in Access: PizzaNr couldnt be changed since its a
> primairy key in several tables, but Pizzaname wasnt a problem.
> But I found the problem: in my update method, the sql statement also tried
> to update PizzaNr, so I had to ommit it.
>
> 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 ?

Bauhaus
Wed May 14 12:06:18 CDT 2008


"Scott M." <smar@nospam.nospam> schreef in bericht
news:uBGk1hItIHA.5268@TK2MSFTNGP06.phx.gbl...
> Concurrency errors indicate that a change being attempted conflicts with
> the reality of the data (i.e. two users editing simultaneously: one simply
> updates a field, while the other person deltes the record and the delte is
> committed before the update is).

Thanks for your 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.