User Tips: Populating a Dropdown from an Application Variable
By Pete D.
There's a lot out there about creating list boxes using a table from a database. But what if the list box
rarely changes and you're constantly hitting the database server for a list box that isn't going to change?
You could put your dropdown in an include file, but then you couldn't change it to reflect a default selection
based on a current value in the database, a user preference, etc.
Instead, put the select list in an application variable. Since application variables are only stored once per
application, they don't use more resources as more users use the site. They also don't require a trip to the
database everytime the select list is used. And, using the Replace function, you can change the
selected value based on a user preference, a current value in a database, etc. I have an intranet database with
17 dropdowns in it. Whenever a user logs on, the dropdowns are all recreated; this keeps them updated even
though they probably change once a month. (There's only about 10 people who use the application regularly, so
it isn't constantly doing this, and it only takes about .3 seconds anyway.)
First, create the dropdown. I created them all in an include file and then include it in my log in page.
'**Create connetion to the database**
SQLSTR="Select listitem from table_of_possible_items"
Set dropdownlist = connect.execute(SQLSTR)
'This starts the select list
Application("droplist1") = "<select name=""SelectListName"" size=""1"">"
'This loop creates all the options
do until dropdownlist.eof
Application("droplist1") = Application("droplist1") & _
"<option Value="""& dropdownlist("listitem")& """>" & _
dropdownlist("listitem") & _
"</option>"
dropdownlist.movenext
Loop
'This closes the select list
Application("droplist1") = Application("droplist1") & "</select>"
|
Now just do a response.write application("droplist1") wherever you want the select list
in your page! Pretty simple and efficient to boot!
Now, how do you change the default selected item in the list? (Maybe you are editing a database record and
want the current value to display as the default value in the form.) Just use the replace
function to replace the option with option selected on the appropriate item when you
write the list. Use an If statement to include an option that is null if the database field is
null. For example:
junk=application("droplist1")
if len(rstData("field1"))<1 then
junk=replace(junk,"</select>", _
"<option selected value="""">Unknown</option></select>")
else
junk=replace(junk,"<option Value=""" & _
rstData("field1") & """>",_
"<option selected Value=""" & _
rstData("field1") & """>")
end if
response.write junk
|
For more information on this topic be sure to read the following articles!
Happy Programming!
