Alright smarty-pants, solve this problem:

Discussion in 'ASP .Net Web Controls' started by Mike Kingscott, Oct 29, 2003.

  1. 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.
    Mike Kingscott, Oct 29, 2003
    #1
    1. Advertising

  2. Mike Kingscott

    Daryl Guest

    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


    "Mike Kingscott" <9.co.uk> wrote in message
    news:...
    > 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.
    Daryl, Oct 29, 2003
    #2
    1. Advertising

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

    "Daryl" <> wrote in message news:<>...
    > 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
    Mike Kingscott, Oct 30, 2003
    #3
  4. Mike Kingscott

    Daryl Guest

    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

    "Mike Kingscott" <9.co.uk> wrote in message
    news:...
    > 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.
    >
    > "Daryl" <> wrote in message

    news:<>...
    > > 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
    Daryl, Oct 30, 2003
    #4
  5. 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.
    Mike Kingscott, Oct 31, 2003
    #5
  6. Mike Kingscott

    Daryl Guest

    Cheers Mike,

    Have fun

    Daryl

    "Mike Kingscott" <9.co.uk> wrote in message
    news:...
    > 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.
    Daryl, Oct 31, 2003
    #6
  7. 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
    Mike Kingscott, Nov 4, 2003
    #7
  8. Mike Kingscott

    Daryl Guest

    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


    "Mike Kingscott" <9.co.uk> wrote in message
    news:...
    > 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
    Daryl, Nov 4, 2003
    #8
  9. 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.
    Mike Kingscott, Nov 5, 2003
    #9
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Greg
    Replies:
    3
    Views:
    509
    =?UTF-8?B?UmFsZiBNw7ZybGVy?=
    Apr 15, 2005
  2. Template Engine like Smarty

    , Jun 22, 2006, in forum: C Programming
    Replies:
    10
    Views:
    766
    Neurotic
    Jun 23, 2006
  3. Blind Melon

    Alright, I need a lot of help.

    Blind Melon, Mar 23, 2007, in forum: C Programming
    Replies:
    1
    Views:
    320
    Blind Melon
    Mar 24, 2007
  4. flaming pants

    , Jun 7, 2008, in forum: C++
    Replies:
    1
    Views:
    358
  5. Marc-André Cournoyer

    [ANN] Thin 0.7.1 Fancy Pants release

    Marc-André Cournoyer, Mar 6, 2008, in forum: Ruby
    Replies:
    0
    Views:
    95
    Marc-André Cournoyer
    Mar 6, 2008
Loading...

Share This Page