SQL Dropdown List for a Document Type

0
Comments
2
Votes
Login to vote

This is just a simple tip of how to create a dropdown list for your document type with a little bit of SQL fanciness.

For my implementation, I needed a dropdown list of States for my custom document Type. One solution was to create a text column called state and enter as the Options:

OO;Select State
AK;Alaska
AK;Alaska
AZ;Arizona
AR;Arkansas
CA;California
CO;Colorado
CT;Connecticut
DE;Delaware
DC;Dist of Columbia
FL;Florida
GA;Georgia
HI;Hawaii
ID;Idaho
IL;Illinois
IN;Indiana
IA;Iowa
KS;Kansas
KY;Kentucky
LA;Louisiana
ME;Maine
MD;Maryland
MA;Massachusetts
MI;Michigan
MN;Minnesota
MS;Mississippi
MO;Missouri
MT;Montana
NE;Nebraska
NV;Nevada
NH;New Hampshire
NJ;New Jersey
NM;New Mexico
NY;New York
NC;North Carolina
ND;North Dakota
OH;Ohio
OK;Oklahoma
OR;Oregon
PA;Pennsylvania
RI;Rhode Island
SC;South Carolina
SD;South Dakota
TN;Tennessee
TX;Texas
UT;Utah
VT;Vermont
VA;Virginia
WA;Washington
WV;West Virginia
WI;Wisconsin
WY;Wyoming
 


I then found that there was already a table within Kentico that contained all of the states called: CMS_State. I found this to be a better approach for my application's needs, so I created another column in my document type called, "StateID" and implemented this SQL Statement:
 

SELECT 0 as StateID, 'Select State' as StateDisplayName, 0 as Orderby
UNION ALL
SELECT StateID, StateDisplayName,1 as Orderby
FROM [dbo].[CMS_State]
WHERE CountryID = 271
Order by Orderby, StateDisplayName


 

 

I deleted my State column and just went with StateID.

 

 
Posted by Jason Ellison on 11/10/2010 5:49:51 PM
Filed under: Dropdown, List, Query, SQL, States
  
Comments
Blog post currently doesn't have any comments.