Alright smarty-pants, solve this problem:

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.
 
D

Daryl

Hi Mike,

The way I do this is to have two datareaders.
The first is your list of people that populates the first listbox.

Then I set the second list box based on another data reader.
This datareader uses a storedprocedure or select that does a left outer join
on the link table and the catagories.
This join will return all rows from one table and only rows that match from
the second table. with columns in the second table that are missing set to
dbnull.
Now you can use the datareader to set the checkbox or whatever based on if
the id from the link table is null or not.

Updating I use one of two solutions
1: used if large batch or you have a save button.

optionally start a transaction

delete all rows in the link table for the personid
scan thru your list and insert for each item selected

commit the transaction

-- A transaction is used so that if anything goes wrong everything is undone
in the db back to when you started it.

2: on the fly changing.
I attach to the checkbox change event then based on the check value either
do a delete for the personid / catagoryid

or

insert this id pair into the link table

Hope this helps
Daryl
 
M

Mike Kingscott

Hi Daryl,

Thank you for your answer! Hmmm, interesting - it looks like I'll have
to manually construct the CheckBoxList or ListBox Items collection by
looping through the DataReader, instead of using DataBind(). It'd work
though...

Also, the batch update feature - i'm wondering if I can use a dataset
to hold the choices of the people and then call the update method on
the table so it does the required inserts/deletes for me - I'm quite
lazy ;-) I wonder how that would tie in with the above...?

Hmmm, lots of food for thought... Thank you again.

Kind regards,

Mike K.
 
D

Daryl

Mike,

What you can do in the outer join statement is to convert the null personid
to a -1 then if you use a datagrid the checkbox can be set based on that new
column. I haven't tested that extensively because scanning thru the
datareader is extremely fast because generally the number of catagories will
be small say less than 100, any more than that and the UI becomes
unmanagable.
As I say most of the time lookup lists are around 10 - 40 items.
The other reason I scan thru is so I can customise the look of the list and
because if you use this technique for things like security then the
catagories may contain many mutually exclusive sets.
For example file access read,write,execute on files a,b,c for person x,y,z

Then I would have a list like the following

If the user chooses person X
r w e file
1 0 0 a
0 1 1 b
1 1 1 c

this would be a list of three checkboxes per file.
Hence you can then configure things quickly.

Another reason is that I have also had the requirement for a multistate
field.
That is say a checkbox which has yes/no and unknown.
This can be implemented easily by controlling the src of an image.

For the update again there are are ways to make it work.
depends on how you set it up.
I just prefer this method because the dataset seams like over kill.

This is one of the great things about the .net framework. You can solve
problems using a variety of techniques.

Regards
Daryl
 
M

Mike Kingscott

Daryl,

Thank you for your further reply - plenty of food for thought there, I
think I'll give it a go... The DataGrid is acceptable (this is for
admin-only maint screens), still got the issue of updating though but
i'm sure I can suss that out.

Thanks again, and rest assured you're elevated above the level of
'smarty-pants' ;-)

Mike K.
 
M

Mike Kingscott

Daryl,

I have a solution :) It's rather good (even if I do say so myself),
using a DataSet, couple of dataadapters, couple of datatables, a
datarelation, and a sqlcommand builder...

I will be posting the code shortly to a site in order to share - any
suggestions? I was thinking of asp.net, aspalliance.com, etc.

Thanks for your help,

Mike Kingscott
 
D

Daryl

Mike,

Your solution sounds interesting.
I quite often browse www.gotdotnet.com
but most of the sites are similar and are scanned by the search engines.

Have fun
Daryl
 
M

Mike Kingscott

Hi Daryl,

Thanks for the tip, I'll think I'll throw it onto CodeProject (if
they'll let me), it seems to be more article-oriented then forum-led,
as my friend pointed out.

Kind regards,

Mike K.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,483
Members
44,901
Latest member
Noble71S45

Latest Threads

Top