A big problem

Ok, I have spent the last few days trying to solve a problem I have. Here is the deal:

I have two database tables, one with reports (each report has a unique id)
and one table with authorization info (username and reportid) "user_perm" is that tables name.

On the page where the admin can set permissions for the users I want to do it like this:

I have a listbox where the admin select a username
then I need a repeater to be filled with two things for each row

1)A checkbox indicating wether or not this user has permission for the report

2)The name of the report

The name is fetched from the report database,(the repeater datasource but HOW ON EARTH DO I SOLVE IT WITH THE CHECKBOX??? I need some kind of sql query for each "repeat" that make a "select reportid from user_perm where username = listbox1.text" and then I have to check the returned table if it has 1 or 0 returning rows, indicating

But how do I solve this in the code? And more important, how do I update the user_perm db when I say have checked two more boxes and want to add those user_name-report_id to the report_perm table??? I thought about mirror the data to a static datatable, make changes there and then upload changes, but I don't know how to do it when the repeater is involved.


Any kind of info, suggestions is extremly helpful, cause this is really bugging me.. to say the least

kind regards
Henrik

I don't have time to go into much detail but since I was recently faced with a similar challenge, I can at least give you a high-level overview of my solution.

Use an OUTER JOIN query that will return a recordset containing all possibles reports and True/False if a specified user is authorized. The syntax for this query will depend on the DB you are using.

I used a datagrid instead of a repeater. This gives you access to the handy Update Button.

Bind your query to the grid.

The reportname can be a bound column.

Use a template column to contain the checkbox.

Code an event handler for the Update Button in which you can grab the updated row.

If the report is checked, INSERT a new authorized row if one does not already exist. If the report is unchecked, DELETE the appropriate row. Alternatively, you could get smart and only perform the SQL if the row had been changed.

Hope this helps.