|
Resistance
My father's nickname for me was "Electricity." He called me this because he said I would always take the path of least resistance. Well, that may be so, but it may not always by such a bad thing. I'm always looking for a quicker (and now more efficient) way to do things, so when faced with the challenge of repopulating an entire page of drop-down boxes from values stored in a database, I plugged back in. The problem is simple and one that many developers face every day. In my case, the task was to convert an initial profile page that collected information from about 20 drop-down boxes into a page that could also handle allowing the user to update their previous selections.
The Problem
Creating the page for a new user was quick enough. I wanted to collect a lot of standardized information as well, so drop-down boxes were the obvious choice. The problem came when I decided to use the same page to allow users to edit their choices later. Storing the values from the initial selection is the easy part. The difficulty comes when you have a drop-down list. Let's take a listing of the United States, for example, and you need to know which one to mark with the "selected" attribute in order for the drop-down box to reflect the user's choice. The typical way to get the correct option to appear is to test the value in the database against the one that the option reflects. This might be performed as follows:
<select name="selState">
<option value="AL"
<%if oRec.fields("strState")="AL" then Response.Write(" selected")%>>AL</option>
<option value="AK"
<%if oRec.fields("strState")="AK" then Response.Write(" selected")%>>AK</option>
<option value="AZ"
<%if oRec.fields("strState")="AZ" then Response.Write(" selected")%>>AZ</option>
...the other 47 entries
</select>
In a listing of 50 states (including Alaska, of course) this kind of operation, especially querying the value of the recordset's field every time, could become very resource intensive. Add onto that the other 11 drop-down boxes on the page, times the average number of options, and it turns into one seriously resource-intensive page! Instead, there are some more efficient methods. The first action we can take to clean this up a bit is to take the recordset value out and place it in a variable. Querying the value of a variable is much less expensive than querying the recordset object itself. At this point, we haven't changed much of the logic, just added a little more efficiency within the logic. Note that the following code will only work in the case of a returning user. A new user will not yet have a recordset, therefore the recordset would be empty and throw an error when we asked for the value of oRec.fields("strState").
<%
Dim strState
strState=oRec.fields("strState")
if oRec.state=1 then oRec.close
if isObject(oRec) then set oRec=nothing
%>
<form name="frmInput>
<select name="selState">
<option value="AL" <%if strState="AL" then Response.Write(" selected")%>>AL</option>
<option value="AK"<%if strState="AK" then Response.Write(" selected")%>>AK</option>
<option value="AZ" <%if strState="AZ" then Response.Write(" selected")%>>AZ</option>
...the other 48 entries
</select>
</form>
In the above code, the recordset is closed before the page continues processing, instead of closing at the end of the page. Since the code beyond this point may require significant processing time, the server is allowed to release the resources the recordset is using, instead of holding onto them unnecessarily. The other major benefit comes when we want to use this page for both the initial registration, when there won't be a corresponding recordset to draw values from, and also for maintenance, when the values exist there. We can create the variables and fill them with default values, check to see if this is a new entry or an update, and in one "if" block, create the recordset and replace the default values with the returning user's values. Here's how this might work:
Dim strState, strCountry, strIndustry
strState="AL" 'The first option that should appear by 'default in the case of a new registration
strCountry="USA"
strIndustry="Computers"
if Request.Querystring("update")=1 'This tells me we're doing an update
Dim oRec
Set oRec=Server.CreateObject("ADODB.Recordset")
oRec.ActiveConnection="DSN=MyDSN"
oRec.open "select strState, strCountry, strIndustry from tbl_profile where userid=" & userID
if not oRec.EOF then
strState=oRec.Fields("strState")
strCountry=oRec.Fields("strCountry")
strIndustry=oRec.Fields("strIndustry")
if oRec.state=1 then oRec.close
is isobject(oRec) then set oRec=nothing
end if
end if
Later in the code
<form name="frmInput>
<select name="selState">
<option value="AL" <%if strState="AL" then Response.Write(" selected")%>>AL</option>
<option value="AK"<%if strState="AK" then Response.Write(" selected")%>>AK</option>
<option value="AZ" <%if strState="AZ" then Response.Write(" selected")%>>AZ</option>
...the other 47 entries
</select>
</form>
The above code requires the use of a querystring variable to let us know if we are dealing with a new user or a returning user. When our page is used for initial registration (a brand new user), the value of the selState drop-down box will be "AL" (the 0th option) by default because we started by loading the value "AL" into the selState variable. The only time the default values will be overwritten with stored values from the database is when the querystring's value equals 1. Using this method, we again get the added benefit of being able to close out our recordset early and release the resources. This provides a much more efficient method for choosing which option will be selected, however, we are still using quite a few operations because we check each of our 50 options to see which one matches. There is still a more efficient way.
Thinking It Through
The key to our next method for selecting the appropriate option lies in the schema of our database. Let's keep with our states example, however, this technique will work for any list of data as long as it is normalized in the following fashion. For the tbl_State table, we will use an identity column to reference which state is which.
It is important that the identity key corresponds to the ordinal position of the corresponding option in the drop-down box. The state table will look as follows:
stateID StateName
-------------------------------
0 AL
1 AK
2 AZ
...and so on until we have all our states
Our drop-down list must then follow suit:
<select name="selState">
<option value=0>AL</option>
<option value=1>AK</option>
<option value=2>AZ</option>
In the tbl_profile table, the reference to the user's state is stored
userID StateID
--------------------------
1 2
2 7
3 1
... and so on.
The first part of our trick is when we return to do maintenance on the user's profile, we check the querystring and see that this is not a new user, but a returning user. Therefore we hit the database and pull out our recordset containing the user's information. Notice that the state value from the user's recordset matches the ordinal position of the option the user chose. Ah, see where we're going here?
Initializing the Page
Now comes the easy part (kind of). Since we have all of the ordinal positions of the options selected by the user, we can use a little client-side initialization to make the drop-down boxes reflect the user's choices. Since we want the page to appear with the options already appropriately selected, it makes sense to use the <BODY> tag's onload event to fire the initialization code. Here's the neat part -- the server is going to write the client-side code for us. The client-side function will only be written in the case of a returning user. The best part is that the form itself no longer needs the individual checks inside of each option tag. It's just a regular form with the manipulation happening through the Document Object Model (DOM).
<html>
<head>
<title></title>
<%
if Request.Querystring("update")=1 then 'This tells me we're doing an update
Dim oRec
Set oRec=Server.CreateObject("ADODB.Recordset")
oRec.ActiveConnection="DSN=MyDSN"
oRec.open "select iState, iCountry, iIndustry from tbl_profile where userid=" & userID
if not oRec.EOF then
Response.Write("<script language=" & chr(34) & "JavaScript" & chr(34) & ">" &VbCrLf)
Response.Write("function init(){" & VbCrLf)
Response.Write("document.frmInput.selState.selectedIndex=" & oRec.fields("iState") & VbCrLf)
Response.Write("document.frmInput.selCountry.selectedIndex=" & oRec.fields("iCountry") & VbCrLf)
Response.Write("document.frmInput.selIndustry.selectedIndex=" & oRec.fields("iIndustry") & VbCrLf)
Response.Write("}" & VbCrLf & "</script>")
if oRec.state=1 then oRec.close
if isobject(oRec) then set oRec=nothing
end if
end if
%>
</head>
<body <% if Request.Querystring("update")=1 then Response.Write(" onload=" & chr(34) & "init()" & chr(34)) %>>
<form name="frmInput">
<select name="selState">
<option value=0>AL</option>
<option value=1>AK</option>
<option value=2>AZ</option>
</select>
...Rest of code here
</form>
Let's walk through the code to see what it's doing. First we check our querystring variable to see if we are returning. If we are, the server will write our client-side JavaScript function into the page using the Response object. Next, we open our recordset and fill it with the options the user chose. With a little string concatenation to append the values to our strings, we now have valid client-side JavaScript. Finally, we use the DOM to gain access to the individual select boxes and dynamically set the selected option using the selectedIndex property.
Here's what the code will look like when it gets to the browser (notice that I've used VbCrLf, basically a carriage return, to format the code so that it won't appear all in one line):
<script language="JavaScript">
function init(){
document.frmInput.selState.selectedIndex=2
document.frmInput.selCountry.selectedIndex=1
document.frmInput.selState.selIndustry =4
}
</script>
The Final Piece
After creating this great function that will initialize all of our select boxes on the page, all that is left to do is call the function from the onload event of the body tag. This is accomplished in a similar fashion, by checking once again to see if the user is returning. A quick check of the querystring variable will tell us, allowing us to dynamically write the onload event into the body tag to fire the function.
<body <% if Request.Querystring("update")=1 then Response.Write(" onload=" & chr(34) & "init()" & chr(34)) %>>
...The rest of your page is here.
The page that I created contains 12 drop-down boxes and initializes instantly. Keep in mind that the selectedIndex property is a base 0 property. Therefore, if you use an identity field in your database, make sure to set the identity seed to 0 so your data and select boxes will be in sync!
About the Author
Brian Baldwin is a senior applications developer at Parsons Brinkerhoff in Denver. He teaches the advanced e-commerce courses at Westwood College of Technology. He has worked in the ASP field for about 3 years, primarily developing back-end components using Visual Basic and SQL Server. He can be reached at brian.baldwin@company39.com.
|
