s
Contact Login Register
h M

SQL Driven Queries for Combo Boxes, Radio Buttons, Listboxes, and Checkbox Groups

Author: Chad Nash/Monday, March 25, 2013/Categories: In The Flow

Rate this article:
No rating

Dynamic Forms & Dynamic Registration require that you return at least two columns for a SQL Driven questions: QuestionOption and QuestionOptionValue

QuestionOption = Text User will see

QuestionOptionValue = Value behind the Item that User will see

 

For Dynamic Views, you can have SQL Driven Combo Boxes and Radio Buttons for Search Filters. This requires that you return at least two columns named SearchOption and SearchOptionValue.

SearchOption = Text User will see

SearchOptionValue = Value behind the Item that User will see

This means that you can use the queries below in Dynamic Forms / Dynamic Registration or Dynamic Views by simply changing the alias of the columns being returned.

 

Example:

Query for Dynamic Forms / Dynamic Registration:

SELECT RoleName AS QuestionOption, RoleId AS QuestionOptionValue FROM Roles 

Query for Dynamic Views:

SELECT RoleName AS SearchOption, RoleId AS SearchOptionValue FROM Roles 

 

Below are queries that I use often:


1.) Get States
---------------------------------------------------------------------------------------------------

SELECT '-- Select State --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue, Text AS SortOrder
FROM Lists
WHERE ListName = 'Region' AND ParentID = 221
ORDER BY SortOrder

---------------------------------------------------------------------------------------------------

2.) Get States and Territories:
---------------------------------------------------------------------------------------------------

SELECT '-- Select State --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue, Text AS SortOrder
FROM Lists
WHERE ListName = 'Region'
ORDER BY SortOrder

---------------------------------------------------------------------------------------------------

 

3.) Get Countries with United States at the top:

---------------------------------------------------------------------------------------------------

SELECT '-- Select Country --' AS QuestionOption, '-1' AS QuestionOptionValue
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue
FROM Lists
WHERE ListName = 'Country' AND Text = 'United States'
UNION ALL
SELECT Text AS QuestionOption, Value AS QuestionOptionValue
FROM Lists
WHERE ListName = 'Country' AND Text <> 'United States'

---------------------------------------------------------------------------------------------------

 

4.) All Users by “Last Name, First Name” including Admin Accounts

---------------------------------------------------------------------------------------------------

SELECT '—Select Person --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT LastName + ', ' + FirstName AS QuestionOption, UserID AS QuestionOptionValue, LastName + ', ' + FirstName As SortOrder
FROM Users
ORDER BY SortOrder
---------------------------------------------------------------------------------------------------

 

5.) All Users by “Last Name, First Name” without Admin Accounts:

---------------------------------------------------------------------------------------------------

SELECT '—Select Person --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT top 10 A.LastName + ', ' + A.FirstName AS QuestionOption, A.UserID AS QuestionOptionValue, A.LastName + ', ' + A.FirstName As SortOrder
FROM Users A
INNER JOIN UserRoles B ON A.UserID = B.UserID
INNER JOIN Roles C ON B.RoleID = C.RoleID
WHERE B.UserID NOT IN(Select Z.UserID from UserRoles Z INNER JOIN Roles X ON Z.RoleID = X.RoleID WHERE Z.UserID = B.UserID AND X.RoleName LIKE '%Administrators%')
ORDER BY SortOrder

---------------------------------------------------------------------------------------------------

 

6.) All Users by “Last Name, First Name” who have logged into their account the past 30 days:

---------------------------------------------------------------------------------------------------

SELECT '—Select Person --' AS QuestionOption, '-1' AS QuestionOptionValue, '0' AS SortOrder
UNION ALL
SELECT top 10  A.LastName + ',' + A.FirstName AS QuestionOptionValue, A.UserID AS QuestionOptionValue, A.LastName + ',' + A.FirstName As SortOrder
FROM Users A
INNER JOIN ASPNET_Users B on A.UserName = B.UserName
INNER JOIN ASPNET_Membership C ON B.UserID = C.UserID
WHERE CONVERT(DATE,C.LastLoginDate,101) > DATEADD(DD, -30, CONVERT(DATE, GETDATE(), 101))

---------------------------------------------------------------------------------------------------

 

These are among the most used SQL Queries for Combo Boxes that I’ve come across. 

Please let us know if you have any questions or if this blog post was helpful.

Number of views (103487)/Comments (-)

blog comments powered by Disqus

Enter your email below AND grab your spot in our big giveaway!

The winner will receive the entire Data Springs Collection 7.0 - Designed to get your website up and running like a DNN superhero (spandex not included).

  
Subscribe