s
Contact Login Register
h M

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

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 Lis

Monday, March 25, 2013/Author: Chad Nash/Number of views (296177)/Comments (-)/ Article rating: No rating
Categories: In The Flow
RSS

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