September 29, 2009 at 8:04 am
Hi,
I have these two tables for example:
Table A
id|name
1|Gustav
2|Dörte
Table B
id|Aid|somevalue
1|1|dfsjkfjkd
2|2|fdsajffdd
3|2|adfdfdfd
Now I'm displaying these two tables with a view like this:
SELECT A.name AS AName, B.somevalue AS BValue
FROM B
INNER JOIN A ON B.AID = A.ID
AName|BValue
Gustav|dfsjkfjkd
Dörte|fdsajffdd
Dörte|adfdfdfd
These values get displayed on a .NET form but I don't know how its best to update data from one form across multiple tables.
I can update the view fields (AName, BValue) but then the AID won't change, It updates the base table. It works like I want when I'm updating the foreign keys of the view, but then I would have to put all this "get all possible Anames in a dropdown-box and just update the id to the database" logic into .net and I want to keep it as simple as possible.
I hope you know what I mean, I'm new to .Net VB...is there a best way how to work with normalized databases in .NET VB?
I know it would work with stored procedures or Instead Of Triggers, but I think there has to be a better way?
Thanks!
September 29, 2009 at 8:40 am
Why do you want to update the ID field? Typically these are primary keys for each table, which means that they are not updateable.
If you have fields from two tables in a view, you have to use two update statements to change values in two tables. Either you update each table separately, or you update the view twice, each time hitting fields from a different table. You cannot update two tables at one time in a view.
September 29, 2009 at 8:47 am
I don't want to update the primary keys but the foreign keys.
But I really don't want to do that, I just want to update my data conveniently.
Let's use my example again. At the beginning the tables are looking like this:
Table A
id|name
1|Gustav
2|Dörte
Table B
id|Aid|somevalue
1|1|dfsjkfjkd
2|2|fdsajffdd
3|2|adfdfdfd
Now I update the AName field on my view.
UPDATE view SET Aname = 'Dörte' WHERE Aname = 'Gustav'
I want it to look like this now:
Table A
id|name
1|Gustav
2|Dörte
Table B
id|Aid|somevalue
1|2|dfsjkfjkd
2|2|fdsajffdd
3|2|adfdfdfd
But it really looks like this:
Table A
id|name
1|Gustav
2|Gustav
Table B
id|Aid|somevalue
1|1|dfsjkfjkd
2|2|fdsajffdd
3|2|adfdfdfd
When I do this:
UPDATE view SET Aid = 2 WHERE Aid = 1
it does what I wanted:
Table B
id|Aid|somevalue
1|2|dfsjkfjkd
2|2|fdsajffdd
3|2|adfdfdfd
But I don't want to change the foreign keys but the values directly - but I guess MSSQL is not intelligent enough for that 🙁
September 29, 2009 at 9:05 am
You're looking at this wrong. And that's why it's not making sense. You are changing the parent, which is fine, and the child stays linked. But you're saying you want the child linked, or moved, to a new parent.
If you update the A table to "Dorte", that parent child link, AID = 1, stays in place with the child. If what you want is for the children to move to the other parent, what you would update is
update tableB
set aid = 2
where aid = 1
That's moving the child and achieves what you are looking for.
If you want the user to edit the "aName" and make it match the "bname", then you are asking for trouble. People will typo things. You can code to check and see if their new name matches and existing one and then decide if you update the ID or the name, but you need to decide if you are changing the value of the parent (table A), or moving the children between parents.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply