HOWTO: Use Multi-Select List Boxes to Display Records

ID: Q188714

The information in this article applies to:

Microsoft OLE DB, versions 1.0, 2.0
Active Server Pages
ActiveX Data Objects (ADO)
Microsoft Active Server Pages version 1.0
Microsoft Data Access Components version 2.5
Microsoft Internet Information Server version 4.0

SUMMARY

This article contains a sample that provides the same functionality you see when you use a browser that does not support Remote Data Services to access a Web site. The sample populates a multi-select list box with database records. After the user selects one or more records for filtering, the server returns only those records to the Web client.

MORE INFORMATION

Sample Code

   <%@ LANGUAGE="VBSCRIPT" %>

   <HTML>
   <HEAD>
   <META NAME="GENERATOR" Content="Microsoft Visual InterDev 1.0">
   <META HTTP-EQUIV="Content-Type" content="text/html; charset=iso-8859-1">
   <TITLE>Document Title</TITLE>
   </HEAD>
   <BODY>
   <%
   Set cnnPubs = Server.CreateObject("ADODB.Connection")
   cnnPubs.Open "MyDSN", "MyUserName", "MyPassword"

   Set cmdTemp = Server.CreateObject("ADODB.Command")
   Set cmdTemp.ActiveConnection = cnnPubs

   Set rstAuthors = Server.CreateObject("ADODB.Recordset")

   If Request.Form("Authors")="" Then
   ' Populate the listbox(es)%>
      <!--METADATA TYPE="DesignerControl" startspan
         <OBJECT ID="objRst" WIDTH=151 HEIGHT=24
           CLASSID="CLSID:7FAEED80-9D58-11CF-8F68-00AA006D27C2">
           <PARAM NAME="_Version" VALUE="65536">
           <PARAM NAME="_Version" VALUE="65536">
           <PARAM NAME="_ExtentX" VALUE="3969">
           <PARAM NAME="_ExtentY" VALUE="635">
           <PARAM NAME="_StockProps" VALUE="0">
           <PARAM NAME="DataConnection" VALUE="cnnPubs">
           <PARAM NAME="CommandText" VALUE="SELECT au_id, au_lname,
              au_fname, phone FROM  authors">
          </OBJECT>
   -->
   <%
   cmdTemp.CommandText = "SELECT au_id, au_lname, au_fname, phone FROM
     authors"
   cmdTemp.CommandType = 1
   Set rstAuthors = cmdTemp.Execute
   %>
   <!--METADATA TYPE="DesignerControl" endspan-->
   <FORM ACTION="multiselect.asp" METHOD="POST">
   <SELECT NAME="Authors" MULTIPLE SIZE="7">
      <%
      Do Until rstAuthors.EOF
         Response.Write("<OPTION VALUE=""" & rstAuthors(0) & """>" &
     rstAuthors(1) & ", " &  rstAuthors(2))
         rstAuthors.MoveNext
      Loop
      rstAuthors.Close
      Set rstAuthors = Nothing
      %>
   </SELECT>
   <INPUT TYPE=SUBMIT VALUE="Select">
   </FORM>
   <%
   Else
   ' Prepare to fetch selected authors
   If Request.Form("Authors").Count > 0 Then
      Response.Write "The following authors were selected:<HR>"
      strWhere=""
      For Each objSel in Request.Form("Authors")
         Set objParam = cmdTemp.CreateParameter(,129,1,11,objSel)
         cmdTemp.Parameters.Append objParam
         If strWhere="" Then
            strWhere="au_id=?"
         Else
            strWhere=strWhere & " OR au_id=? "
         End If
      Next
      cmdTemp.CommandText = "SELECT au_id, au_lname, au_fname, phone FROM
        authors WHERE " &  strWhere
      Set rstAuthors = cmdTemp.Execute

      ' Display selected records
      Response.Write("<TABLE BORDER=1>")
   Response.Write "<TR><TD>ID</TD><TD>LAST NAME</TD><TD>FIRST
    NAME</TD><TD>PHONE</TD></TR>"
      Do Until rstAuthors.Eof
         Response.Write("<TR>")
         For Each objFld in rstAuthors.Fields
            Response.Write "<TD>" & objFld & "</TD>"
         Next
         rstAuthors.MoveNext
         Response.Write("</TR>")
      Loop
      Response.Write("</TABLE>")

     End If
   End If
   %>
   </BODY>
   </HTML>

Additional query words: adovi adoengdb AXSFVBS

Version           : WINDOWS:1.0,2.0,2.5; WINNT:1.0,4.0
Platform          : WINDOWS winnt
Issue type        : kbhowto

Last Reviewed: July 14, 1998