Database Lookup Using Drop Down Lists

Access forms often use a combo box to look up information in a database. When you use a drop-down box, the available choices are limited, so you don't need to worry that the user will enter incorrect information. Hands-On 28-11 illustrates how you can display a drop-down list box in a browser, load it with the product names, and return product information formatted in a table.

©Hands-On 28-11: Creating a Web Page with a Drop-down List Box

If you don't feel like typing, locate the ProductLookup.asp file from the downloadable files and copy it to your Learn_ASP folder.

1. Start Windows Notepad and enter the ASP code as shown below.

Dim conn, rst, strSQL

Set conn = Server.CreateObject("ADODB.Connection") conn.ConnectionTimeout = 15 conn.CommandTimeout = 30

conn.Open "Driver={Microsoft Access Driver (*.mdb)}; DBQ=" &_

Server.MapPath("Northwind.mdb") & ";" Set rst = Server.CreateObject("ADODB.Recordset") If Len(Request.QueryString("ProductID")) <> 0 Then strSQL="SELECT * FROM Products WHERE ProductId=" rst.Open(strSQL & Request.QueryString("ProductId")), conn, 0, 1 If Not rst.EOF Then rst.MoveFirst

Response.Write "<TABLE Border=1>" Response.Write "<TR>"

Response.Write "<TD><B>Product Id</B></TD>" Response.Write "<TD><B>Product Name</B></TD>" Response.Write "<TD><B>Quantity Per Unit</B></TD>" Response.Write "<TD><B>Units in Stock</B></TD>"

Part V

Response.Write "<TD><B>Unit Price</B></TD>" Response.Write "</TR>" Response.Write "<TR>" Response.Write "<TD Align='Center'>" Response.Write rst.Fields("ProductId") & "</TD>" Response.Write "<TD Align='Left'>" Response.Write rst.Fields("ProductName") & "</TD>" Response.Write "<TD Align='Left'>" Response.Write rst.Fields("QuantityPerUnit") & "</TD>" Response.Write "<TD Align='Center'>" Response.Write rst.Fields("UnitsInStock") & "</TD>" Response.Write "<TD Align='Right'>"

Response.Write FormatCurrency(rst.Fields("UnitPrice"),2) & "</TD>" Response.Write "</TR>" Response.Write "</TABLE>" End If rst.Close End If rst.Open "Products", conn, 0, 1 If Not rst.EOF Then rst.MoveFirst

Response.Write "<FORM Action='./ProductLookup.asp' Method='get'>" Response.Write "<B>Select a Product:</B><BR>" Response.Write "<SELECT Name='ProductId'>" Response.Write "<OPTION></OPTION>" Do While Not rst.EOF

Response.Write "OPTION value=" & rst.Fields("ProductId") & ">" Response.Write rst.Fields("ProductName") & "</OPTION>" rst.MoveNext


Response.Write "</SELECT>"

Response.Write "<INPUT Type='Submit' Value='Get Product Details'>" Response.Write "</FORM>" End If rst.Close

Set rst = Nothing conn.Close

2. Save the file as ProductLookup.asp in the Learn_ASP folder.

The VBScript code segment above begins with establishing a connection with the data source. Instead of using a fully qualified path to the Northwind database, the code shows you how to use the MapPath method of the ASP Server object to retrieve the path to the database. The statement Server.MapPath("Northwind.mdb") will return the following path: Learn_ASP\Northwind.mdb. In fact, if you add Response.Write Server.MapPath("Northwind") to the code above, the filename with its path will appear in the browser. It is not difficult to guess that using Server.MapPath generates an additional request for the server to process. Therefore, when deploying your web site, you should replace Server.MapPath with a fully qualified path to get a better performance (see the previous hands-on examples for how this is done).

Taking Your VBA Programming Skills to the Web

Notice that before the connection to the database is opened, the following statements are used:

conn.ConnectionTimeout = 15 conn.CommandTimeout = 30

The first statement instructs the Connection object's ConnectionTimeout property to wait 15 seconds before abandoning a connection attempt and issuing an error message. In the second statement above, the CommandTimeout property of the Connection object specifies how long to wait while executing a command before terminating the attempt and generating an error. The default for the ConnectionTimeout and CommandTimeout properties is 30 seconds. Using ConnectionTimeout and CommandTimeout in this example procedure is optional. Before utilizing these properties in your own database applications, make sure that the data source and the provider you are using support them.

Next, the script above instantiates a Recordset object and opens it using the open connection. The Recordset is opened as forward-only (0 = adOpen-ForwardOnly) and read-only (1 = adLockReadOnly). As mentioned earlier in this chapter, to use enumerated ADO constants, you need to add the #INCLUDE FILE directive at the beginning of the Active Server Pages file.

The SQL SELECT statement contains the WHERE clause that will pull only the record for a selected product ID if the user makes a selection from the drop-down box. The data available for the selected record is then placed in a table. In this example, the table headings are hard-coded. If you don't want to hard-code the headings, you could loop through the recordset to read the field names (see the FastRetrieve.asp file created earlier for an example).

After writing out table headings, the procedure fills the table cells with data. The table will contain only one row of data because the recordset is limited to one product selected from the drop-down list. After the data is presented in a table, the Recordset object is closed.

Next, another recordset is opened. This time the code opens the entire Products table. We loop through the recordset to build a drop-down list box. For each record, an <OPTION> tag is created, its value is set to the ProductId field, and the text is set to the ProductName. The first entry in the drop-down list is a blank line. This effect is achieved by omitting the value and text attributes inside the HTML <OPTION> tag:

The drop-down list box is part of a form. The <FORM> tag is used to generate an HTML form.

Forms allow user input into the browser and act as a container for ActiveX controls. There are two types of forms: GET and POST. This example uses the GET method to send information. (See Hands-On 28-13 for an example of processing form input with the POST method.) Within a <FORM> and </FORM> block, you can insert tags representing various HTML controls. In this example, the form contains the list box produced by the <SELECT> tag and a command button produced by the <INPUT> tag. When the user clicks a submit form button labeled "Get Product Details," the data gathered from the

Part V

drop-down list box is passed to the Active Server Pages file specified within the <FORM> statement by the ACTION parameter.

3. Open your browser and type http://localhost/NorthDB/ProductLook-

up.asp in the address bar.

4. Press Enter or click Go. The web page displays a drop-down box and a button as shown in Figure 28-24.

Figure 28-24: By using a drop-down box in a web page, you can provide a user-friendly interface for selecting records.

Figure 28-24: By using a drop-down box in a web page, you can provide a user-friendly interface for selecting records.

Open the drop-down list. When you open a drop-down list box, the list of products appears. Notice that the first entry in the list is a blank line.

Select a product from the drop-down list and click the Get Product Details button. The product details appear in a table, as shown in Figure 28-25.

Figure 28-25: When you select a product from the drop-down list and click the Get Product Details button, the selected product information is presented at the top of the web page.

Figure 28-25: When you select a product from the drop-down list and click the Get Product Details button, the selected product information is presented at the top of the web page.

When you use the form with the GET method to send the information, the data is appended to the request for the processing page. The data being passed is visible in the address bar in your browser (see the figure above). Because the data is visible, you can easily troubleshoot any problems by looking at the address bar. The drawback of using the GET method for sending information is that the data is not secure and it is limited in size to the maximum length of the request string.

Taking Your VBA Programming Skills to the Web

0 0

Post a comment