Database Lookup Using a Multiple Selection List

In the previous section, you've seen an example of looking up product information by selecting a product name from a drop-down list. At times, however, a user may want to view several products at once. To meet this requirement, you will need to create a multiple selection list box and process the user's selections.

Hands-On 28-12 illustrates how you can display a multiple selection list box in a browser, load it with the product names, and return product information formatted in a table.

©Hands-On 28-12: Creating a Web Page with a List Box

If you don't feel like typing, locate the MultiProductLookup.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.

<HEAD><TITLE>Select Multiple Products</TITLE></HEAD>

Dim conn, rst, strSelect, strWhere, strSQL, totalItems, fld

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 strSelect="SELECT ProductId as [ID], ProductName as [Product Name], " &_ "QuantityPerUnit as [Qty/Unit], UnitsInStock as Stock, " &_ "UnitPrice as [Unit Price] FROM Products " strWhere = "WHERE ProductId=" strSQL = strSelect & strWhere totalItems = Request.QueryString("ProductId").Count myValues = Request.QueryString("ProductId").Item

Response.Write "<P><H5><I>The following SQL statement was used:</I>"

If totalItems = 1 Then rst.Open(strSQL & Request.QueryString("ProductId")), conn, 0, 1

<PRE><%= strSQL & Request.QueryString("ProductId") %></PRE>

Else strWhere = "WHERE ProductId IN ("

Part V

strSQL = strSelect & strWhere rst.Open(strSQL & myValues & ")"), conn, 0, 1

<PRE><%= strSQL & myValues & ")" %></PRE>

' get table headings

Response.Write "</H5><P><TABLE Border=1>"

Response.Write "<TR>"

For Each fld in rst.Fields

Response.Write "<TH>" & fld.Name & "</TH>"

Next

Response.Write "</TR>"

' get the data Do While not rst.EOF Response.Write "<TR>"

For Each fld in rst.Fields Response.Write "<TD>" If fld.Name = "UnitPrice" Then

Response.Write FormatCurrency(fld.value,2)

Else

Response.Write fld.value End If

Response.Write "</TD>"

Next

Response.Write "</TR>" rst.MoveNext

Loop

Response.Write "</TABLE>" rst.Close End If rst.Open "Products", conn, 0, 1 If Not rst.EOF Then rst.MoveFirst

<FORM Action="MultiProductLookup.asp" Method="get"> <B><I><FONT Size=2 Face=Tahoma>Hold down CTRL or SHIFT <BR>

to select multiple products:</FONT></I></B><BR> <SELECT Name="ProductId" MULTIPLE Size=8>

Do While Not rst.EOF

OPTION Value="<%=rst.Fields("ProductId")%>"> <%=rst.Fields("ProductName")%></OPTION>

rst.MoveNext Loop

<INPUT Type="Submit" Value="Get Product(s) Details"> </FORM>

Taking Your VBA Programming Skills to the Web

End If rst.Close

Set rst = Nothing conn.Close

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

The VBScript code segment above establishes a DSN-less connection to the Northwind database by using the Microsoft Access driver and instantiates a Recordset object. Refer to the previous hands-on exercise for an explanation of the Connection object's ConnectionTimeout and CommandTimeout properties and the Server object's MapPath method.

The code proceeds to check whether the user has selected any items in the list box. If at least one product was picked from the list, the procedure defines the SQL SELECT statement and uses the QueryString method of the Request object to retrieve the total number of selected products. This number is then stored in the totalItems variable.

The next Request.QueryString statement retrieves the IDs of the selected items and places them in the myValues variable.

The next statement announces that the line that follows is the SQL statement the user has selected. This statement is formatted with the HTML <H5> and <I> tags. This will make the enclosed text an italicized heading of size 5 (the largest heading is 1 and the smallest 6). The <P> tag designates the text as a plain paragraph. The ending </P> tag is optional.

If one product was selected in the list box, a recordset is opened using the following statement:

rst.Open(strSQL & Request.QueryString("ProductId")), conn, 0, 1

Recall that 0 and 1 at the end of this statement indicate a forward-only and read-only recordset.

The statement

<PRE><%=strSQL & Request.QueryString("ProductId") %></PRE>

will write the complete SQL statement to the browser for the user to see. When you use the HTML <PRE> and </PRE> tags, the text between these tags is formatted exactly as it is typed. Spaces and carriage returns are preserved.

If more than one product was selected in the list box, we need to change the contents of the strWhere variable to include the IN keyword in the WHERE clause. The IN keyword restricts the rows being selected to those rows where the column values are in the list presented in the SQL statement.

Assuming that the user selected products with IDs of 1, 3, and 6 in the list box, the following SQL statement will be generated:

SELECT ProductId as [ID], ProductName as [Product Name], QuantityPerUnit as

[Qty/Unit], UnitsInStock as Stock, UnitPrice as [Unit Price] FROM Products WHERE ProductId IN (1, 3, 6)

Part V

The remaining code segment in the code above creates a table in a browser. We use the For Each.. .Next loop to write out the column names to the browser:

For Each fld in rst.Fields

Response.Write "<TH>" & fld.Name & "</TH>"

Next

The <TH> tag makes a cell a table heading. This automatically makes the text bold.

After populating the table with the headings, we use the Do While loop to write out the table rows until the end of the recordset is encountered. We must obtain field values for each column in a row. This is done with the For Each. Next loop like this:

For Each fld in rst.Fields Response.Write "<TD>" If fld.Name = "UnitPrice" Then

Response.Write FormatCurrency(fld.value,2)

Else

Response.Write fld.value End If

Response.Write "</TD>"

Next

Notice the conditional statement within the above code segment. We use it to perform an additional operation on the UnitPrice field. We format this field as currency using the FormatCurrency function.

When all the table rows are written to the browser, the table is closed with the HTML table close tag </TABLE>, and the recordset itself is closed.

Next, the VBScript code continues by opening the recordset based on the Products table and cycling through this recordset to retrieve the product IDs and product names for inclusion in the list box. The HTML form section contains the MULTIPLE keyword in the <SELECT> tag to indicate that the list box should be created. The size of the list box is set to display eight products like this:

<FORM Action="MultiProductLookup2.asp" Method="get">

<B><I><FONT Size=2 Face=Tahoma>Hold down CTRL or SHIFT <BR>

to select multiple products:</FONT></I></B><BR> <SELECT Name="ProductId" MULTIPLE Size=8>

Notice that the above code is a client-side script. This code appears outside the VBScript <% and %> delimiters. To make this code a part of the VBScript you may want to rewrite it as follows:

Response.Write "<FORM Action=""./MultiProductLookup3.asp"" Method=""get"">" Response.Write "<B><I><FONT Size=2 Face=Tahoma>Hold down CTRL or SHIFT <BR>" Response.Write "to select multiple products:</FONT><I></B><BR>" Response.Write "<SELECT Name=""ProductId"" MULTIPLE Size=8>"

Once we have defined the list box we can populate it with product names using a Do While loop. We use the <OPTION> tag with the value attribute <OPTION value=" "> to specify items in the list:

Taking Your VBA Programming Skills to the Web

Do While Not rst.EOF

OPTION Value="<%=rst.Fields("ProductId")%>"> <%=rst.Fields("ProductName")%></OPTION>

rst.MoveNext

Loop

Notice again that we set the list values outside the VBScript. For better understanding, and to practice various methods of coding, you can rewrite this code like this:

Do While Not rst.EOF

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

Loop

To allow the user to submit selections to the server, the form contains the submit button titled "Get Product(s) Details." When the user presses this button, the form data will be submitted using the GET method.

The procedure ends by closing both the Recordset and Connection objects and freeing up memory.

Let's go on to test our work in the browser.

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

Lookup.asp in the address bar.

4. Press Enter or click Go.

5. The browser will display a list box. Select the items as shown in Figure 28-26 and press the Get Product(s) Details button.

Figure 28-26: You can allow users to filter the data by using a multiple selection list box.

"H Select Multiple Products

mmm

I File Edt View Favorites Took Help

*

Qm - O L*I @1 ü >)seatdi tVFavOTites o "I

[ Addre; : ^http^ocahost/NorthDB/^ffVoductLoiíijp.asp v | Q Go 1

Hold down CTRL or SHIFT to select multiple products:

Chef Anton's Gumbo Mix

Norihwoods Cranberry Sauce S||

1 Get Product(s) Details |

VJ Local intranet

Part V

3 Select Multiple Products

Fife Edt View Favorites Tools Hefp

Address http;fllocdhost/NorthDB/Mumodu£tLix^p.^ vj S3 Go

Tfte/erf/flivf'wg siotemmt t*as used:

SELECT Productld as IIDJ, ProductMame as [Product Bams], Quantity? trVnit as [Qty/Vnit J ,ltaitsIaStock as Sto

Fife Edt View Favorites Tools Hefp

Address http;fllocdhost/NorthDB/Mumodu£tLix^p.^ vj S3 Go

Tfte/erf/flivf'wg siotemmt t*as used:

SELECT Productld as IIDJ, ProductMame as [Product Bams], Quantity? trVnit as [Qty/Vnit J ,ltaitsIaStock as Sto

ID Product Name

Qtyi'Uïiit Stock

Unit Price

1 Chai

10 boxes x 20 bags 39

IS

3 Aniseed Syrup

12 ■ 550 mi bottles 13

10

4 Chef Anton's Cajun Seasoning

43-6 oz jars ¡53

22

7 TJncit Bob's Organic Dried Pears

12 -1 lb pkgs g

30

Hofd down CTRL or SHIFT to select multiple products:

Chai Chang

Aniseed Syrup

Chef Anton's Cajun Seasoning Chef Anton's Gumbo Mix Grandma's Boysenberty Spread Uncle Bob's Organic Dried Pears Worihwoods Cr&nbeny Sauce

Hofd down CTRL or SHIFT to select multiple products:

Chai Chang

Aniseed Syrup

Chef Anton's Cajun Seasoning Chef Anton's Gumbo Mix Grandma's Boysenberty Spread Uncle Bob's Organic Dried Pears Worihwoods Cr&nbeny Sauce

[ Get Produces) Details |

Local Wrafiet

Figure 28-27: After selecting the products in the list box (see Figure 28-26) and clicking on the Get Product(s) Details button, your browser displays data as shown here.

0 0

Post a comment