Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cbrinson
Starting Member
36 Posts |
 Posted - 2002-04-30 : 17:45:06
|
| Can anyone think of a better way to do the following? I have a simple permissions setup for a custom forums system. There is an administrative web page that allows me to edit which forums a moderator has permission to moderate. The page lists all the different forum names with checkboxes beside each name and of course the user's name. The userID is passed as a hidden form field.Currently, I use ASP to iterate through all the checked boxes and call a simple insert statement for each value to add the userID and the forumID to the "moderators" table. The problem is that ASP can't "see" a checkbox unless it is checked. So this method only allows me to add new permissions to a user and not revoke existing permissions. Currently I am having to delete the moderator from the moderators table and add him again if I need to revoke his access.What I really need to do I guess is something like this:memberID = 1forumIDs = 1,4,7,9,11somehow pass the CSVs to a stored proc which would:DELETE * FROM forum_moderators WHERE memberID = 1-- Then iterate through the list of forumIDS and do an insert for each value.I just don't know how to do this with a stored proc and have not had any luck finding an example so far. I would also need to wrap the whole thing in a transaction so I would not delete the user's current permissions unless the new permission were applied successfully. Any ideas?Thanks,Chris |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-30 : 17:51:32
|
| There are a number of articles on SQL Team about parsing CSV values:http://www.sqlteam.com/SearchResults.asp?SearchTerms=csvThat should help you to eliminate the ASP loop at least. I think using the DELETE statement like you are now is a good way to do it, the easiest way at least. |
 |
|
|
cbrinson
Starting Member
36 Posts |
Posted - 2002-04-30 : 18:18:13
|
| Thanks! That was exactly what I was looking for. |
|
|
|
|
|
|
Passing CSVs to a proc and multiple inserts