n last month's 10-Minute Solution, you learned how to display selective data from a database based on the user's choice. This time you'll see how to improve and optimize this technique. Specifically, you'll learn how to display a list of states as they exist in the database as a drop-down menu (combo box) and display more than one field that the user can choose from to filter records.
Before you begin, you need to get your database ready. You can access any ODBC-compliant database from your ASP pages using ActiveX Data Objects (ADO). This means you can access a Microsoft Access database, a Microsoft SQL Server database, or even an Oracle database. For the sake of this article, I will demonstrate how to access a Microsoft Access database.
In this case, you want to provide a means by which the user can filter the data coming from a database. A user may wish to see customers belonging to a particular state or a particular city. The user can choose the state from a list of available states or type in a city name.
First, let's see what the user interface for the filtering portion will look like. Create a simple HTML form and then test it out before you merge it with your ASP code (see Listing 1). This makes it easy to test and debug. The code should result in an HTML form that looks like Figure 1. Notice that you have four FORM controls on the page, a combo box (drop-down menu) called "cboState", a text box called "txtCity", a command button called "cmd", and an invisible textbox called "PASS", which will always contain the value "1". When the user submits this form, you will be able to read the values of these controls.
For this test page, <FORM> alone will not work. The HTML form needs two other attributes within its tag: an ACTION attribute and a METHOD attribute. The ACTION attribute specifies what page on your Web server will receive this form's data and process it. The METHOD attribute informs the browser how the data will be sentas a long string within the query to the target page (GET) or as separate invisible headers to the Web server (POST). Remember, you will be placing all your code in a single ASP pagelet's call it 10MinPrimer3.asp. You will point the form's ACTION attribute to this page. For this demo, let's use a POST METHOD. So, modify the <FORM> tag to read as follows:
<FORM ACTION="10MinPrimer3.asp" METHOD="POST">
Get Data From the Form
Now work with your ASP page that will display selective data from the database. If you followed the instructions in last month's article, you should have a template for your ASP pages that looks like Listing 2. Save your template as a new ASP page, 10MinPrimer3.asp. Then, obtain the data from the HTML form right in the beginning under the line of code that reads:
' All your ASP preprocessing code goes here
To get the data from the form, use the Request object to query the value of the control on the form. For example, to obtain the state entered in the combo box "cboState", use this code:
Dim strState, strCity, intPass
strState = Request("cboState")
You now have the value of the state within the variable strState. Next, get the value of the city, in case the user entered a city:
strState = Request("txtCity")
Now obtain the value of the variable "PASS" from the form into the variable intPass:
intPass = Request("PASS")
The variable "PASS" comes from an invisible textbox on the page so the user cannot tamper with its value. It can have only two possible values: a value of "1", or a value of "" or nothing. The variable "PASS" tells you how the user accessed the page, 10MinPrimer3.asp. If the user is coming to this page for the first time, the variable "PASS" will have no value. If the user is coming to this page for the second or later time (after submitting the form), the variable "PASS" will have a value of "1". You use this variable to decide what you need to do next.
Use a Select Case statement to evaluate the value of the variable intPass. If it is equal to "1", call a separate subroutine to handle the display of the data from the database. If it is anything else, again call a separate subroutine to display the HTML form. Also, make sure that no code below the Select Case gets accidentally executed by "ending" the response with a "Response.End" statement.
' -- Take Action based on how you came into this page
Select Case Trim(intPass)
' -- Repeat Visit, display database data
' -- First Time Visit, display HTML Form
' -- Make sure nothing else gets processed
Populate the Combo Box
Create the DisplayHTMLForm subroutine by simply cutting and pasting the HTML text from the test page you created earlier. However, in the sample HTML code, the states combo box is not filled with values. You'll need to modify the HTML code to get the values from the database itself (see Listing 3).
In the code, you have a string variable called strStatesList. Fill this string with the entire HTML code necessary to display a combo box filled with states/regions. Then incorporate this string within the HTML code between the <SELECT> and </SELECT> statements:
<SELECT NAME="cboState" SIZE="1">
<%= strStatesList %>
Fill the list of states/regions by making a call to a separate function, GetStatesListForComboBox:
strStatesList = GetStatesListForComboBox ()
This function queries the database and returns a list of distinct states/regions (see Listing 4). It then builds the HTML <OPTION> tags that go within an HTML <SELECT> to generate a combo box. Later on, you will see how you can optimize this function. If you run the page now, you should have a combo box that is populated with states/regions from the database (see Figure 2).
You are now ready to create the DisplayDatabaseData subroutine. This routine will use most of the code from last month's article. Because the variables strState and strCity are declared at the very top of the page, they are visible to all subroutines within your page. So this subroutine can access them and use ADO to then obtain and display the data. You create a subroutine by using the Sub and End Sub statements, making sure that they are within your ASP code section bounded by <% and %>.
' Sub Routine: DisplayDatabaseData
' Displays the data from the database
Create ADO Objects in Your ASP Page
The simplest way to access a database with ADO uses just two objects: a Connection object to connect to the database, and a Recordset object to obtain the data from the database. This technique is also the fastest because it returns a read-only, forward-scrolling recordset. In 90 percent of the cases of database access from an ASP page, you only wish to access data from the database and dump the contents on to the browser. For this purpose, a forward-scrolling, read-only recordset is ideal.
Within your new subroutine, use this code to create a Connection object and a Recordset object:
' -- Declare Variables
Dim objConn ' Our Connection Object
Dim objRS ' Our Recordset Object
Dim strSQL ' Our SQL String to access the database
Dim strConnection ' Our Connection string to access the database
Dim i ' a counter variable
' -- Create objects
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
At this point, you have an empty Connection object (objConn) and an empty Recordset object (objRS).
Use the Open method of the Connection object to open a connection to the database. The Open method requires up to three arguments. A connection string is the first argument, followed by an optional user ID and an optional password argument. The connection string tells ADO how to connect to the database. If you are using an ODBC DSN, all you need to provide is the name of your DSN. If the DSN holds the user name and password within it, you do not need to supply any more arguments. If not, you will need to include the user ID and password arguments. In the sample, the file DSN itself contains the user ID and password, so you do not need any more arguments. All you need to write is:
The way you specify the DSN name in the Open method differs based on whether you are using a system DSN or a file DSN. If you are using a system DSN called NWIND, then you would use this code:
If you are using a file DSN, the code is almost identical, except instead of saying "DSN=", you would code it as "FILEDSN=". Also, you need to spell out the full path and name of the DSN file. For example, if the DSN file were physically located on the Web server at "C:\WEBS\ASPPRO\NWIND.DSN", then your code would be:
If you need to provide the user ID and password, add them as separate arguments:
ObjConn.Open "DSN=NWIND", "USERID", "PASSWORD"
To make your code easier to modify, place the connection string in a variable and use the variable name instead:
' -- Connection String Value
strConnection = "FILEDSN=path on my machine\NWIND.dsn"
' -- Open the Connection
Populate the Recordset Object With Data
Because you want to selectively obtain the data from the database, you will need to modify your SQL statement to obtain only records that match particular criteria. Instead of bringing back all records with a "SELECT * FROM CUSTOMERS", you will need to bring back data by using a "WHERE" clause. Evaluate the value of the strState and strCity variables to construct your SQL statement. Place this SQL statement in a variable, again to facilitate code maintenance, and then use the Connection object's Execute method to open a recordset (see Listing 5).
Notice how the multiple filter criteria are determined. If the user chooses a state and nothing else, your SQL statement would be:
SELECT * FROM CUSTOMERS WHERE REGION = 'StateChosen'
If the user chooses a city (or enters a portion of the city's name), you would use a SQL LIKE statement:
SELECT * FROM CUSTOMERS WHERE CITY LIKE '%CityChosen%'
In the LIKE statement, the city value entered is enclosed with percentage (%) signs. This allows you to choose any record where the text occurs anywhere inside the CITY field. So, if the user enters "an", it is equivalent to saying *an*, or any city that has the text "an" anywhere inside it.
What makes the filter criteria complicated is that the user can select a state or a city or both. If both are chosen, then you must 'AND' the two criteria as in this SQL statement:
SELECT * FROM CUSTOMERS WHERE STATE = 'StateChosen' AND CITY LIKE '%CityChosen%'
First, test to see if the user has selected a state. You ignore the combo box line 'Please choose a state' because you know it has a value of "0".
If strState = "0" Then strState = ""
Then check to see if the user has chosen anything at all. If neither state nor city is chosen, you simply display all records. If either state or city is chosen, you build separate WHERE clause portions and then concatenate them at the end. You also build a filter string to display at the top of the resulting records so the user knows what the filter criteria were.
' -- Check the State
if Trim(strState) <> "" Then
strWhereState = " REGION = '" & strState & "' "
strFilter = strFilter & " State/Region = " & strState
When creating the WHERE clause for the city, you check whether a state was also chosen, and if so, you 'AND' the two statements.
' -- Check the City
If Trim(strCity) <> "" Then
' -- do we already have one where clause before this?
if strWhereState <> "" Then
strWhereCity = " AND "
strFilter = strFilter & " and "
strWhereCity = strWhereCity & _
" CITY LIKE '%" & strCity & "%' "
strFilter = strFilter & " City is like: *" & strCity & "* "
Finally, you build your SQL statements by joining all the pieces.
' -- Now concatenate to get the real SQL Statement
strSQL = strSQL & " WHERE " & strWhereState & strWhereCity
So, if a user has chosen the state 'CA' and no city, you would use this SQL statement:
SELECT * FROM CUSTOMERS WHERE REGION = 'CA'
If the user chose 'CA' for state, and entered 'an' for city, your SQL statement would be:
SELECT * FROM CUSTOMERS WHERE REGION = 'CA' AND CITY LIKE '%an%'
At this point, you have a recordset (objRS) filled with data.
Before you proceed with accessing the data from the recordset, it is always a good idea to check to see if you did indeed get back any data or not. To do so, check the recordset's BOF and EOF properties. If they are both true, you got back an empty recordset with no data. In the sample, you are simply outputting an error message and ending the page with a Response.end so that no further code gets processed. In your own application, you may want to provide a better error message, perhaps by redirecting the user to a separate error-handling ASP page:
if (objRS.BOF and objRS.EOF) then
response.write "No records found"
Process the Recordset Data
If you do get data back in the database, you can process the data. You can move from record to record within the recordset by using the recordset's MoveNext method. You can access any field in the recordset by using the Recordset("fieldname") syntax, or the Recordset("fieldnumber") syntax. In the sample, you are outputting all the columns of the recordset as columns of a table, and all the records as rows in the table.
In the <BODY> section of your ASP page, create an HTML table tag:
<TABLE BORDER="1" CELLPADDING="2" CELLSPACING="1" WIDTH="100%">
You will first output a heading row containing the names of the fields in the recordset object. Because you do not know the names, you will just iterate through the fields collection of the recordset to obtain the field names. In this code, you obtain the field names by using objRS.Fields(i).Name:
' -- Output the Field Names as the first row in the table
Response.Write "<TR BGCOLOR=""#CCCCCC"">"
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TH>" & objRS.Fields(i).Name & "</TH>"
You then "walk" through the recordset outputting the field values. Again, in this code, you obtain the value of a field by using objRS.Fields(i). This defaults to the .Value property of the field. You could have also used the code objRS(i) and obtained the same results:
' -- Now output the contents of the Recordset
Do While Not objRS.EOF
' -- output the contents
For i = 0 to objRS.Fields.Count - 1
Response.Write "<TD>" & objRS.Fields(i) & "</TD>"
' -- move to the next record
Once you are done processing data, it is important to close the objects. Doing so not only regains server resources, but it also disconnects from the database server, thereby improving performance in a multi-user scenario. Use the Close method on the objects to physically purge them from memory by setting their values to "Nothing":
set objRS = Nothing
set objConn = Nothing
Get a copy of the code here. I have modified the code to allow you to see the SQL statement that is used to obtain the results. The first time you enter the page, you will see the HTML form. If you select 'CA' for the state and enter 'an' for the city, you should get a screen that looks like Figure 3. I have modified the output by changing the font used for the display (it makes the output look nicer than the default Times New Roman font that the browser uses).
Optimize the GetStatesListForComboBox Function
Here is one final technique to help optimize your code. Each time you run this page and have it display the filter criteria HTML form, you are hitting the database to obtain the list of states/regions in the table. Because this data does not change often (like most lookup values), you do not have to access the database each time. You can modify the code so that you only hit the database the first time. Once you get this data, store or cache it somewhere. Then every time after that, and for every user after that, you can simply use the cached value and prevent a database hit. Because you do not have to access the database each time, this technique will speed up your Web site.
To apply this technique, you can use the built-in ASP Application object. You might be tempted to store a recordset in the Application or Session object, but this approach is too expensive. (Placing objects within Application or Session objects uses extensive server resources, hampers multiple-thread creation, and causes severe performance penalties.) Instead, use the Application object to store values (or strings), rather than objects. In this case, you will use the Application object to store the result generated by the GetStatesListForComboBox functiona string of HTML code.
You will need to modify your GetStatesListForComboBox. Before the CreateObject line, first try to obtain the data from the Application object. Assuming you are storing the states list in an Application variable called 'StatesList', you would use this code:
' -- Do we have the value in our Application Object already?
strResult = Application("StatesList")
If strResult = "" Then
' -- No, so get it from the database
. . . code to get it from the database
You also need to modify the code that gets the states list from the database so that it stores this list in the Application variable called StatesList:
' -- Store the value in the Application object for next visit
Application("StatesList") = strResult
First, Lock the Application object to allow writing to it. Then place the value in an Application-level variable and UnLock the Application object. You will find that this technique speeds up your Web pages. Obviously, if the look-up values change, you will need to figure out how to change the Application-level variable, but that's a whole other topic.