Last post Mar 02, 2015 08:09 AM by banksidepoet
Feb 28, 2015 11:03 AM|banksidepoet|LINK
Hi, let's say I have a role called "Admin".
A few of the users in my website have been assigned to this role; the vast majority of users are not assigned a role at all.
I need a SELECT statement for a SqlDataSource in markup not code behind which selects ONLY users who have no role and leaves out all the users assigned to Admin. I am currently using: SELECT [UserName] FROM [vw_aspnet_Users] which includes ALL users.
Can it be done in markup? Is it possible to add a WHERE clause to this? If not, I use C# for code.
Thanks in advance.
Mar 01, 2015 12:14 PM|Rion Williams|LINK
If I understand you correctly, you want all of the users that either are not in your Roles table or are assigned as Administrators? Do you have any examples of the current schema you are using (e.g. what the Roles / Users tables look like)?
Generally, you could probably do something like this :
SELECT [UserId], [UserName]
FROM [UsersTable] u
WHERE NOT EXISTS (SELECT * FROM [RolesTable] r WHERE u.UserId = r.UserId OR r.RoleId = 'YourAdminRoleId')
This is obviously a very hastily, untested example, but it might give you a bit of insight in to how you could possibly handle it within SQL code. I'm sure an implementation in C# might be quite a bit easier but it will depend on how you are managing Roles
/ Authorization in your application.
Mar 02, 2015 08:09 AM|banksidepoet|LINK