Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
How do I make dynamic listboxes?
Dynamic List Boxes
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:
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:
--------------------------------------------------------------
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
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:
Code:
request.form.countFor this example, our page will be named 'makeModel.asp'
So let's look at some code. I'll comment as I go:
Code:
<%@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