I tried the code posted in the FAQ. The code looked like it should work, and seemed to be efficient.
The code has some typos however.....It wouldn't run as is.
I corrected the typos.. and tested the asp page. Unfortunatelly it did not work. I don't know what I am missing?
The is the code I am using:
<%@language=vbscript%>
<%option explicit%>
<%
'OUR SQL, CONNECTION, AND RECORDSET STUFF
dim con, strCon, MakeSql, ModelSQl, 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 = "Provider=SQLOLEDB; Data Source= eng_server;"_
+ "Initial Catalog = testDB;User ID=myID; Password=myPswrd "
'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
rsMake.Open makeSQL, con
rsModel.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(rsMake,curMake,"make")
call makeSelect(rsModel,curModel,"model")
%>
</FORM>
</BODY>
</HTML>
<%
'TAKE OUT THE TRASH
set rsMake = nothing
set rsModel = 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
%>
Thanks
