<%@ Language=VBScript %>
<HTML>
<HEAD><TITLE>Select Multiple Products</TITLE></HEAD>
<BODY>
<%
    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 ("

        strSQL = strSelect & strWhere
        rst.Open(strSQL & myValues & ")"), conn, 0, 1
        %>
            <PRE><%= strSQL & myValues & ")" %></PRE>
        <%
    End if

    ' 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
%>
    </SELECT>
    <INPUT Type="Submit" Value="Get Product(s) Details">
    </FORM>
<%
End If
rst.Close
Set rst =  Nothing
conn.Close
Set conn = Nothing
%>
</BODY>
</HTML>
