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,