Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Best way to show a crosstab in gridview ( m x n table of checkboxes)

Thread Tools

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

Posts: n/a
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.



FK meal_id
FK emp_id
order: boolean

What I'd love to have on a 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

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


Reply With Quote

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Crosstab-type grid control Craig Buchanan ASP .Net 0 06-11-2008 09:42 PM
Crosstab Query in ASP.Net Page j.t.w ASP .Net 2 02-05-2008 09:55 AM
crystal report column width in crosstab reports backer ASP .Net 0 03-12-2007 08:21 PM
How to generate data driven HTML Table like CrossTab Kamal Ahmed ASP .Net 0 06-28-2006 02:00 PM
Crosstab query output PW ASP .Net 0 05-19-2006 02:22 AM