Best way to show a crosstab in gridview ( m x n table of checkboxes)

Discussion in 'ASP .Net' started by H5N1, Jul 30, 2006.

  1. H5N1

    H5N1 Guest

    Hi there

    It seems I've spotted interesting problem in a job I've got to do.

    I have 3 tables: meals, employees, and intersecting table:
    meals_for_employees with bool field meaning, that given employee wants
    a given meal.

    employees
    -----------------
    emp_id
    name


    meals
    --------------
    meal_id
    name

    meals_for_employees
    -------------------------------
    FK meal_id
    FK emp_id
    order: boolean



    What I'd love to have on a asp.net 2.0 page is this:


    | employee 1 | epmloyee 2 | employee 3 | epmloyee 4
    | ...
    meal1 | 0 | X | 0
    | X |
    meal2 | X | 0 | X
    | 0 |
    meal3 | 0 | 0 | 0
    | 0 |
    meal4 | X | X | 0
    | 0 |



    the problem is that the number of columns is dynamic, (equals to
    "select count(*) from employees") so it cannot be represented with
    straight SQL query.

    I guess it's called a crosstab query.

    My web research from last 15 minutes told me that there are basically 2
    ways to solve it:
    1) dynamic sql query
    2) some people wrote, that Relational DBs are not meant to cope with
    such data, and such table should be composed in the presentation tier
    from some simpler queries.

    in case of 1) outputting would be straightforward (stored proc->table
    adapter->object data source->gridview) at least if I knew how to write
    dynamic sql in SPRC (any helpful links appreciated) though writing
    change values would be a bit harder, wouldn't it?

    could someone sketch me a way to write changes back into a db?

    in case of 2) I' guess I could compose the table (gridview) from single
    columns which would be simple sql queries. but again, what about
    editing, how should I instruct gridview to write changes to db
    properly?


    and third problem - how could I ommit necessity of hitting EDIT link in
    each row to be updated? despite, I'd like to check/uncheck all
    checkboxes and just hit some SAVE button, which would then save the
    whole gridview back into db.



    thanks for any suggestions of how would you attack this problem

    cheers
    HP
    H5N1, Jul 30, 2006
    #1
    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. PW

    Crosstab query output

    PW, May 19, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    1,969
  2. Kamal Ahmed
    Replies:
    0
    Views:
    474
    Kamal Ahmed
    Jun 28, 2006
  3. backer
    Replies:
    0
    Views:
    831
    backer
    Mar 12, 2007
  4. j.t.w

    Crosstab Query in ASP.Net Page

    j.t.w, Jan 25, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    3,865
    j.t.w
    Feb 5, 2008
  5. Craig Buchanan

    Crosstab-type grid control

    Craig Buchanan, Jun 11, 2008, in forum: ASP .Net
    Replies:
    0
    Views:
    395
    Craig Buchanan
    Jun 11, 2008
Loading...

Share This Page