M
Mike Kingscott
Ok, I have a table of fruits (Apple, Orange, Pear, Banana), a table of
people (Rod, Jane, Freddy), and table that links who likes what (Rod
likes Apple, Orange; Jane likes Pear, Banana; Freddy likes Orange,
Pear).
(Hopefully you will realise that the first two tables have a primary
key identifier that is used in the many-to-many table of who likes
what - if not, skip this post and try and help a newbie).
What I want to have is a maintenance page for each person, wherein a
list (checkboxlist, multi-select listbox, i don't care) of ALL
categories are displayed, correctly checked/selected for that person's
likes. The user then updates their choices, clicks a save button, and
then their choices are save back to the db.
Here's what I've tried so far:
I could easily extract the categories from the db to build a databound
checkboxlist, but that only gets me a list of categories with no
checks, so that's out. Maybe if I then tried to overlay that with the
users likes by resetting the datasource and re-binding - oops, there
goes my list of fruits!
I could write a view to return the data, and magically set the
Selected property of the item as it gets added to the list - that
fails because a) you can't really set the Selected property and b) you
can't update over a view
I could return two datareaders, one holding the fruits and one holding
the person's likes, and then manually add the items to the
checkboxlist by looping round the fruits datareader - but then for
each one, I'd have to loop around the like's datareader - bad
performance...
And how about that updating? May have to construct some XML and pass
it up to SQL Server so it can do it, or can I use a DataSet somewhere
along the line and update just the likes table... Hmmm...
Any thoughts,
Kind regards,
Mike K.
people (Rod, Jane, Freddy), and table that links who likes what (Rod
likes Apple, Orange; Jane likes Pear, Banana; Freddy likes Orange,
Pear).
(Hopefully you will realise that the first two tables have a primary
key identifier that is used in the many-to-many table of who likes
what - if not, skip this post and try and help a newbie).
What I want to have is a maintenance page for each person, wherein a
list (checkboxlist, multi-select listbox, i don't care) of ALL
categories are displayed, correctly checked/selected for that person's
likes. The user then updates their choices, clicks a save button, and
then their choices are save back to the db.
Here's what I've tried so far:
I could easily extract the categories from the db to build a databound
checkboxlist, but that only gets me a list of categories with no
checks, so that's out. Maybe if I then tried to overlay that with the
users likes by resetting the datasource and re-binding - oops, there
goes my list of fruits!
I could write a view to return the data, and magically set the
Selected property of the item as it gets added to the list - that
fails because a) you can't really set the Selected property and b) you
can't update over a view
I could return two datareaders, one holding the fruits and one holding
the person's likes, and then manually add the items to the
checkboxlist by looping round the fruits datareader - but then for
each one, I'd have to loop around the like's datareader - bad
performance...
And how about that updating? May have to construct some XML and pass
it up to SQL Server so it can do it, or can I use a DataSet somewhere
along the line and update just the likes table... Hmmm...
Any thoughts,
Kind regards,
Mike K.