INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dynamic List Boxes

How do I make dynamic listboxes? by link9
Posted: 27 Jan 02

The question:
How can I make the dropdowns on my ASP page linked to one another?

I will construct a simple example here using something we all know and love: cars  

For any one make, there can be several models, right?  And if you wanted someone to select a make and a model, then it would be easier for them to do so if they first select a make, and then you narrow the list of models to only those that correspond to the make that they chose, yes?

Let's first examine how you **might** set up your database.

Your database has two tables, 'make', and 'model'.  Here's what they look like:

Make
make

Model
model
make

So that it's very easy to see the relationship between the two tables.  One make has many models.

Easy enough, right?  So now for the ASP implementation.  We're going to use something here that I call a recursive form.  There may be some other official name for it, but if there is, I'm unaware.  Basically what it means is that the form that we will create here will point to itself (i.e. the action will be the same page's name), so that when it's submitted, it's almost like hitting "Refresh" on your browser, except values will be sent along with this refresh, which we can then snap up and use to link our listboxes.

So that brings up the next question.  If we are reloading the page, then we'll need some way of determing whether the page is a first load, or a recursive load.  In the first case, we'll build up our default SQL Statements, and in the second case, we'll want to evaluate the user's input to build up a dynamic SQL Statement, right?

So the simplest way to do this is to evaluate the .count property of the form object, which is a member of the request object, or in simpler terms:

request.form.count

In this simple example, if the result of that question is 0, then we will assume that it is a first load, and will treat it as such.  If it's > 0, then we'll assume it's a recursive load, and treat it accordingly.

For this example, our page will be named 'makeModel.asp'

So let's look at some code.  I'll comment as I go:


<%@language=vbscript%>
<%option explicit%>
<%
'OUR SQL, CONNECTION, AND RECORDSET STUFF
dim con, strCon, sqlMake, sqlModel, rsMake, rsModel

'OUR USER DEFINED STUFF
dim curMake, curModel

'INSTANTIATE THE OBJECTS TO BE USED
set con = server.createObject("ADODB.Connection")
set rsMake = server.createObject("ADODB.Recordset")
set rsModel = server.createObject("ADODB.Recordset")

'HERE I'LL USE A DATA SOURCE NAME CALLED
'  'makeModel' TO OPEN OUR CONNECTION
strCon = "DSN=makeModel;UID=userID;PWD=password"

'THIS IS WHERE WE EVALUATE OUR Request.Form.Count
'  TO DECIDE WHAT TO DO
if (Request.Form.Count = 0) then
    
  'THIS IS A FIRST LOAD OF THE PAGE
  '  WE DON'T NEED TO GRAB ANY USER DATA

  '**note that I'm giving our fields aliases here**
  '  I'm doing this so that we can use the same
  '  subprocedure to create our listboxes later on
  '  in the code.  Keep an eye on how we do that.

  makeSQL = "SELECT make AS description"
  makeSQL = makeSQL & " FROM make ORDER BY make"

  modelSQL = "SELECT model AS description"
  modelSQL = modelSQL & " FROM model ORDER BY model"

else

  'THIS IS A RECURSIVE LOAD
  '  WE NEED TO SEE WHAT THE USER HAS CHOSEN

  curMake = Request.Form("make")
  curModel = Request.Form("model")

  'NOW WE WILL BUILD OUR DYNAMIC SQL STATEMENT

  'LET'S STILL SELECT ALL MAKES, SO THAT THE USER CAN
  '   SELECT A DIFFERENT ONE IF THEY WISH
  makeSQL = "SELECT make AS description FROM make"
  makeSQL = makeSQL & " ORDER BY make"

  'LET'S ONLY SELECT THE MODELS THAT CORRESPOND TO
  '  THE USER'S CHOICE
  modelSQL = "SELECT model AS description"
  modelSQL = modelSQL & " FROM model"
  modelSQL = modelSQL & " WHERE make = '" & curMake & "'"
  modelSQL = modelSQL & " ORDER BY model"
    
end if

'SO AT THIS POINT, NO MATTER WHETHER THE LOAD IS FIRST,
'   OR RECURSIVE, WE HAVE THE APPROPRIATE SQL STATEMENTS
'   BUILT UP, SO LET'S NOW OPEN OUR
'   CONNECTION AND RECORDSETS

con.Open strCon

makeRS.Open makeSQL, con
modelRS.Open modelSQL, con
%>

<HTML>
<HEAD>
<TITLE>Dynamic List Box Test</TITLE>

<SCRIPT LANGAUAGE=javaScript>
    //THIS IS THE FUNCTION THAT WILL BE CALLED
    // onChange FOR EITHER OF THE LIST BOXES
    // ALL IT WILL DO IS SUBMIT THE FORM FOR US
    
    function submitMe(){
        document.theForm.submit();
    }
</SCRIPT>

</HEAD>
<BODY>

<FORM NAME=theForm METHOD=post ACTION=makeModel.asp>
    
  <%
    'I'M GOING TO USE A SUBPROCEDURE TO WRITE OUT
    '  OUR SELECTS -- SENDING IT THE APPROPRIATE VARIABLES
    '  TO WRITE THE PROPER SELECT

    call makeSelect(makeRS,curMake,"make")
    call makeSelect(modelRS,curModel,"model")
  %>

</FORM>
</BODY>
</HTML>
<%
'TAKE OUT THE TRASH
set makeRS = nothing
set modelRS = nothing
set con = nothing

sub makeSelect(lRS,curValue,selectName)

  with response
        
    .Write("<SELECT NAME=" & selectName)
    .Write(" onChange=""submitMe();"">" & vbcr)
        
    'LET'S PUT A DEFAULT SELECTION IN THAT
    '  WILL AUTOMATICALLY SHOW IF
    '  THE USER HAS NOT YET MADE A CHOICE
    .Write("<OPTION VALUE=none>SELECT ONE</OPTION>" & vbcr)
        
    'LOOP THROUGH OUR RECORDSET,
    '  OUTPUTTING WHATEVER'S THERE
    while not lRS.eof
      .Write("<OPTION VALUE=""" & lRS("description") & "")
            
      'CHECK TO SEE IF THE CURRENT VALUE WAS SELECTED
      '  BY THE USER AND SELECT IT IF SO
      if curValue = lRS("description") then
        .Write(" selected")
      end if
            
      .Write(">" & lRS("description") & "</OPTION>" & vbcr)
            
      lRS.MoveNext
    wend
        
    .Write("</SELECT>")
    
  end with

end sub
%>


--------------------------------------------------------------

And there you have it, friends.  Obviously, I spent no time formatting our HTML page, or anything like that.  If you should simulate this particular example, then all you will see is a blank page with two list boxes...  no buttons or anything.  The framework is there, though, to do anything you like with the values.  You could make a third recordset and display it below the two listboxes, perhaps displaying performance information for the selected automobile.  If you do that, then you'll have a third condition to watch for before you try to open that recordset.  Have both selections been made?  If so, build your third SQL Statement, open your recordset, and display your information.

I'll leave those types of questions completely up to you, but if you have problems, jump over into our forums and post away.

Happy Coding!  
Paul Prewett

Back to Microsoft: ASP (Active Server Pages) FAQ Index
Back to Microsoft: ASP (Active Server Pages) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close