Recently Viewed...
To help you navigate to pages you most recently visited, select from the links below.
Product Forums
Page Tags
Sharepoint Forums
Newsletter
Dynamic Forms Demonstrations
DNN Modules
SnowCovered Top Sellers

Events Calendar and Registration 2.2.7

One stop solution for events calendar and events registration! Demo site available for free trial.

Opt In Email 4.0

'Relationship Building' and 'Communication' are two essential nuts and bolts for a business to prosper. This module allows you to bridge both of these and easily generate continuous awareness of your web site, products and services. Your prospects and customers will greatly appreciate this featur

XMod 5.1

Version 5 of the perennial best-selling tool for creating data-based solutions in DNN without custom programming. This version focuses on greater flexibility, expandability, and ease-of-use.

Ultra Video Gallery 2.4

Ultra Video Gallery is a brother product of Ultra Media Gallery, UVG allows you to add videos in various format and automatically convert them to flv format, you also can add videos from embed code and play them in our integrated flash video player.

Active Forums

The award winning discussion forum and community solution for DotNetNuke is now even better. When you need to build the best community site, you need the best module, Active Forums!

Open-DocumentLibrary v3.0

Powerful, Ajax Enabled, Easy to Use. Document Management has never been better. Open-DocumentLibrary allows DotNetNuke users to share and manage documents in a flexible, intelligent way, offering granular control over Folder and Document access.

Gloss // ALL COLORS! skin // XML Flash // W3C CSS XHTML1.0

Gloss is the first ALL COLORS! skin, featuring a powerful XML-based flash and being 100% W3C CSS XHMTL1.0 compliant. Tune it yourself on the fly, changing colors, backgrounds, containers, text color and size, width and more! Brilliant!Available for DNN4 and DNN5

Minimalist by Evan O'Neil - ALL NEW !

ALL NEW ! - Minimalist includes skin packs in 12 great colors. Each color has Flat, Gradient and Glass versions. Feature rich XML Flash header, perfect for just about any purpose. 9 Different menu options in each skin pack; 3 horizontal menus, 3 vertical menus and 3 all-new Twin level menus . . .

Ultra Flash Player 6.1(16-in-1)

A 16-in-1 dnn module.Powerful yet easy to use. Capable of producing a multitude of functions including:Image rotator/Numeric rotator/ Mp3 player/Wmv player/ Flash video player /YouTube video player/Multimedia player / Text-to-speech podcast / Flipping book player/Banner topic player/Silverlight albu

Ultra Media Gallery 5.4

Ultra Media Gallery is the most popular photo gallery and media gallery solution for DotNetNuke, UMG offers 10 different flash player to browse your gallery with completely different user interface experience.
    |   Register   |   Friday, January 09, 2009   
Data Springs Product Forums...
Forums > Product Discussion - DotNetNuke Modules > Dynamic Registration
Subject: SQL statement for role selection list
Prev Next
You are not authorized to post a reply.

Author Messages
Peter
Posts:3
01/25/2008 5:42 PM  

Greetings all.

I am trying to populate a list of options derived from my DNN roles table. The idea is that a new user selects a role, then at submission they are assigned that role, via an insert statement during either a question or submission event, as part of their user profile.

I admit that my SQL skill is extremely limited so I can't seem to get the syntax correct. I would appreciate any assistance offered.

Thank you.
Chad
Posts:2389
river guide
river guide
01/26/2008 12:52 PM  
Peter,

How many roles are you looking at here? If only a few you could manually type them in and setup DNN Role Completion events instead of SQL Events. If you are looking at more than a few though you could use a SQL Query to pull int he list of roles and then an insert statement as you mentioned based on their response...

So to pull in the roles... This would add a - Select Role - as the initial option in the dropdownlist, you might not need that though if you are using a checkboxlist or listbox etc...

Select '< Select Role >' as QuestionOption, -1 as QuestionOptionValue
Union
Select RoleName as QuestionOption, RoleID as QuestionOptionValue from Roles where PortalID = 0
Order By QuestionOption


Once you get this working you could determine if you want to setup DNN Role Completion Events (one even for each specific role you want to add them to) or use a SQL Event to add the user to the role.

The SQL event would look something like this:
Insert Into UserRoles(UserID, RoleID) Values($(UserID), $(YourRoleShortFieldName))

You would need to replace YourRoleShortFieldName with the short field name for your role field.

-Chad
Peter
Posts:3
01/27/2008 5:16 AM  

Chad, thank you for your reply. This is exactly what I was looking for. I should have realized though that selecting roles from the global allows users to pick from restricted roles, like Admin, or those reserved for staff. I believe I can resolve this issue by creating a Role Group, say called Specialty and only slecting the roles from within it. How would I modify the select and insert statements to reference such a Role Group instead of global?

 
Chad
Posts:2389
river guide
river guide
01/28/2008 3:16 PM  
Well you could just add some addition where clauses to the query such as:

Select RoleName as QuestionOption, RoleID as QuestionOptionValue from Roles where PortalID = 0 AND RoleName <> 'Administrators' AND RoleName <> 'Registered Users'
Order By QuestionOption

OR

Select RoleName as QuestionOption, RoleID as QuestionOptionValue from Roles where PortalID = 0 AND RoleID <> 0 AND RoleID <> 1
Order By QuestionOption
You are not authorized to post a reply.
Forums > Product Discussion - DotNetNuke Modules > Dynamic Registration > SQL statement for role selection list


ActiveForums 3.7

Copyright 2005 - 2009 by Data Springs, Inc.
Terms Of Use | Privacy Statement