what is the proper sql update statement for this

[seco]

this is my database and i want to write the update statement for the joined tables

thanks in advance.

 

[cjard]

You write two update statements. Most RDBMS (especially the good ones) wont update joined tables unless you preserve the keys of the join (hard to do, given that normalising a database logically should create key-cartesian situations, or 1:M scenarios in other words)

Dont update joined tables.. Infact, dont even select from them in ADO.NET - if you have to there is likely a design flaw in your program

 

[Arg81]

As Cjard says, you want 2 update statements. One for your Bills table and one for your Purchases table.

So you'd want 2 TableAdapters - 1 for each table, with their own select, delete, insert and update commands.

By using a dataRelation in VS, you can easily update your child table from your app when you select the parent.

 

[seco]

thanks for reply

you says " By using a dataRelation in VS, you can easily update your child table from your app when you select the parent"

but i have concurrency violation exception raised randomly and i ask for it they says that you shouldnt use command builders for update and delete
and use customized statements

what can i get it right?
thanks

 

[Arg81]

Can you post a screenshot of the error or type the exact error as it'll be more helpful to troubleshoot this.

and i ask for it they says that you shouldnt use command builders for update and delete
and use customized statements

not exactly sure what you mean by this, and who are "They"???

 

[SteveInBeloit]

cjard:
I don't quite understand this:

Dont update joined tables.. Infact, dont even select from them in ADO.NET - if you have to there is likely a design flaw in your program

Don't select columns from joined tables?? If I have an order detail table that has the key to the customer Item table. Would you not write a stored procedure that would join the tables and return them as a cursor, with one row for each detail line? Or is there something in this type of framework that makes this not the preferred method.

Thanks.

 

[cjard]

er.. no; its not the preferred method because database normalisation is supposed to realise manyne relationships within data.

Thus, you have many children for a single parent, and joining them to provide that info in the client app is not wise because you cannot perform something called key preservation.

Suppose one parent has 4 children. In the parent and child tables we have just an id and a data value. We join them together and our results look like:

[FONT=Courier New]ParentID, ParentData, ChildParentID, ChildID, ChildData[/FONT]
[FONT=Courier New][FONT=Courier New]1         apple       1              1        green[/FONT]
[FONT=Courier New][FONT=Courier New]1         apple       1              2        red[/FONT]
[FONT=Courier New][FONT=Courier New]1         apple       1              3        blue[/FONT]
[FONT=Courier New][FONT=Courier New]1         apple       1              4        yellow[/FONT]
[/FONT][/FONT][/FONT][/FONT]

Now we have a repeated set of parent rows. If we show this to the user and they edit the grid to be like this:

[FONT=Courier New][FONT=Courier New]ParentID, ParentData, ChildParentID, ChildID, ChildData[/FONT]
[FONT=Courier New][FONT=Courier New]1         apple       1              1        green[/FONT]
[FONT=Courier New][FONT=Courier New]1         [B]pear[/B]        1              2        red[/FONT]
[FONT=Courier New][FONT=Courier New]1         [B]lemon[/B]       1              3        blue[/FONT]
[FONT=Courier New][FONT=Courier New]1         [B]orange[/B]      1              4        yellow[/FONT]
[/FONT][/FONT][/FONT][/FONT][/FONT]

How do you rationalise this? youre trying to set the same parent row to 4 different values.

Performing a join, then selecting from it forces all parent rows to be non-editable


We dont do this in any relational data system, not just ADO.NET - for purposes of updates, key preservation must be supported. As soon as you generate a result set where the primary key of a set is repeated, the key is not preserved. Doing this even for view isnt always sensible, though the occasions where it is necessary are more common

 

[SteveInBeloit]

I can understand about not wanting to update them. But I didn't understand why not even to select data from joined tables. Say your stored proc is returning rows for a report, and the data is from many tables. Surely that would be OK to do all the joins in the stored proc and return detail lines for the report.

Thanks for the information you have shared lately, it is really helping me.

Steve

 

[cjard]

Oh, yes.. for reporting purposes it's ok, sometimes expected.. THose are the times that fall into "the occasions where it is necessary [are more common]"

 

[TechGnome]

That's the key to anything.... use the right tool for the right job for the right reason. In the case of a grid of parents, which when clicked will display child records else where, then it makes sense. BUT.... in the case of our current app, that wouldn't work because it isn't grid based. A single instance of the screen represents an object and all its subordinates. Making 10 calls to get the data from the different tables we need doesn't make sense when we can join them together and get the data once. Now, updates, and inserts too are a diferent story. There we do 10 calls to the database, once for the parent, returning the ID Key, then use that to insert/update the child recs.

It's all about using a screwdriver for screws and a hammer for nails.

-tg

 

[SteveInBeloit]

Yes, I agree. In ACCESS, when you have joined on a couple of tables, you are able to set the UniqueTable property so it knows which one to update. Best practice would probably say to stay away from this if possible.

My shop is converting from ACCESS to VB .NET right now, and I have a few projects on my plate and have started. I am trying to not get into any bad habits right off the bat!

Steve

 

[seco]

thanks for all replies

i will stay way from that .

 

[cjard]

in the case of our current app, that wouldn't work because it isn't grid based. A single instance of the screen represents an object and all its subordinates.

It's not often that I work on such a system; are they very popular? For all systems I've written where parent/child relationships are supported, have been the typical way we see it done in ADO.NET - fill parent, fill children, datareltion sorts things out. Interstingly, the onyl time joined tables have been used as a datasource, it has been for grids, but in a readonly fashion (search results for example)