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!

• Speeding up ASP Pages with Caching
  
• Caching a Web Page
  
• Caching Data

Happy Programming!

Return to user tips...