devxlogo

Filter Data from a Database

Filter Data from a Database

Why Trust Us

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,

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 sent?as 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 page?let’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 tag to read as follows:

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, intPassstrState  = 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 pageSelect Case Trim(intPass)	Case Trim("1")		' -- Repeat Visit, display database data		DisplayDatabaseData	Case Else		' -- First Time Visit, display HTML Form		DisplayHTMLFormEnd Select' -- Make sure nothing else gets processedResponse.End

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 statements:

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