Searching a database using drop-down lists

This tutorial explains how to use a pair of drop-down lists to bring up results from a database.

Our example uses an Access database called CarListings that has a table containing cars for sale. The fields in the table are Manufacturer, Model, and Price.

We will construct a search page where a first drop-down list shows the manufacturer, and when a selection is made from this list, a second drop-down list shows all the Models from that manufacturer. Upon selection of both Manufacturer and Model, a GridView displays all the records in the database of the manufacturer and model along with the price.

To follow this example, construct an Access database named "CarListings" with a table titled "CarsForSale". Add fields "Manufacturer", "Model" and "Price" to the table. Then fill in the table with some data, using several car manufacturers such as BMW, Benz, Volkswagen etc. and some various models for each record, and save the database. Open Expression, highlight the fpdb folder and do file / Import and import the database.

The steps to building a search page are:

1. Create a page titled SearchCarsForSale.aspx

2. From the ASP.NET toolbox, drag a drop down list onto the page

3. Click on the expander arrow at the top right of the control, chech "enable autopostback" and select Choose Data Source, select New Data Source in the next window, click on Access Database, and in the Specify ID box, change the name AccessDataSource1 to something meaningful like MfrsOfCarsForSale, continue on and browse to the fpdb folder and highlight the CarListings.mdb database and click Open, click next, and in the next window, configure the Select statement by checking the "manufacturers" box and check the "return only unique rows" box and click Next, the click Test Query to see how it looks, and finish. Save, do file / preview in browser, and you should get a dropdown list of manufacturers populated from the database. It is good practice to give your dropdown list an ID that is meaningful, like "DDLManufacturers".

4. Drag a second dropdown list onto the page, click the expander arrow, select "enable autopostback" and in the choose data source window select new data source from the dropdown list. Follow steps just like in step 3 but this time select "model" column for display when you configure the select statement, then click on the Where button. This brings up a window where you can select the criteria, so pick the Manufacturer column, leave the Operater at "=" , and under Source, choose Control. This will cause the selection of manufacturer to come from the first control. Then pick the control from step 3 as the source. Click Add, and click OK and proceed to test the query and to finish. Now save, file / preview in browser and you should find the second dropdown list populated by the models corresponding to the manufacturer selected in the first dropdown list. Again, good practice is to name the dropdown list meaningfully, for example, giving it an ID of "DDLModels".

5. Drag in a GridView, again select new data source when you configure, browse to the same access database and this time choose all the data fields you wish the GridView to display. Click the Where button, select "manufacturer" column, leave Operater at "=" , select "control" as the source and select the DDL showing manufacturers in the parameter properties, and click Add. Then select the "model" column, leave operator at "=" , select "control" as the source, and select the DDL showing models in the parameter properties, and click Add. Click OK, Next, Test Query and proceed to finish.

Save, preview in Browser and you are done.