Multiple Selection Listbox with Database Connectivity

• Download source files - 8.74 Kb

Introduction

This article/code snippet illustrates on how to create a multiple listbox on the fly through an ASP document, having the contents/data/items to be selected stored on a table in a database. The files are organized perfectly for your reference.

Using the code

Database connectivity is found at global.asa, either connect through a simple MS Access database or MS SQL Server database.

Sample code:

'
'FOR MS SQL SERVER DATABASE
'==========================
application("con").open "PROVIDER=MSDATASHAPE;DRIVER=" & _ 
     "{SQL Server};SERVER=[SERVER];DATABASE=[DATABASE];UID=[USERID];PWD=[PASSWORD]; "
      
'FOR MS ACCESS DATABASE
'==========================
application("con").open "Driver={Microsoft Access Driver (*.mdb)};" _
        & "DBQ=" & Server.MapPath("database/main.mdb")

Dealing with Recordsets for data retrieval is found at utility.asp on <modules>.

Sample code:

'
'DEFINITION
'==========================
Sub CrRst(byref rst, rstctr, strsql)
    set rsCreate = server.CreateObject("adodb.recordset")
    ......
    ......

    'IMPLEMENTAION
    '==========================
    CrRst rst, ctr, <SQL>
   'rst - holds the records/data in array, 
   'e.g. rst(1,2), (1) - indicates the field:(2) - indicates the record
   'ctr - record counter
  '<SQL> - SQL statement

Creating the multiple listbox with items/data returned from a Recordset is found in utility.asp on <modules>.

Sample code:

'
'DEFINITION
'==========================
sub listbox_mul(name, strsql, blank, selected, events, width_in, size_in)
    response.write "<SELECT name='" & name & "' id='" & _
                   name & "'" & events & " size='" & _
                   size_in & "' style='width:" & width_in & "px'>" & crlf
    ......
    ......

    'IMPLEMENTAION
    '==========================
    <%listbox_mul "optFilterOpt", _
       "select ID, ITEM from tblRefItems order by ID" , true, "", "", 100, 10%>

Transferring of listbox items/data functionality is found at listbox_option.js on <includes>.

Sample code:

'
'DEFINITION
'==========================
function SelAll(a, b){
    availableList = document.getElementById(a);
    ......
    ......

    'IMPLEMENTAION
    '==========================
    <input type="button" value=">>" 
      name="addAll" onclick="SelAll('optFilterOpt','optFilterSel')">