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

H

H5N1

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
 

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

Top