|
Dynamic Dependent Lists/JScript
generated from Relational Database
This example shows how to tie a relational database together
to form dependent list boxes with Client Jscript. We have made a generic subroutine to
handle it, but be warned... The insides of it are one of the most complicated examples on
this site.
filename=/learn/test/listdynamicdb.asp
<html><head>
<title>listdynamicdb.asp</title></head>
<BODY OnLoad="StartMeUp();">
<FORM Name="myForm">
<%
accessdb=server.mappath("/learn/test/biblio.mdb")
strconn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
strconn=strconn & accessDB & ";"
mySQL= "SELECT Titles.Title, Publishers.Name "
mySQL= mySQL & "FROM Publishers "
mySQL= mySQL & "INNER JOIN Titles ON Publishers.PubID = Titles.PubID "
mySQL= mySQL & "WHERE Publishers.state='NY' "
mySQL= mySQL & "Order by Publishers.name"
call listmaker(strConn, mySQL, _
pubnames,"publist",booknames,"booklist",_
pubevent,pubfun, "myForm")
%>
Publisher:<br>
<%response.write pubnames%><br>
Books:<br>
<%response.write booknames%><br>
</Form>
</Body></Html>
<SCRIPT Language="JavaScript"><!--
function StartMeUp()
{
alert("All Books are Loaded Now. Thanks for waiting!");
}
<%=pubevent%>
<%=pubfun%>
--></Script>
<!--#include file="lib_listdynamicdb.asp"-->
Here is the include file that defines the subroutine that
does most of the work:
filename=/learn/test/lib_listdynamicdb.asp
<%
SUB listmaker(myDSN,query,byref list1, listname1, byref list2, listname2, byref myevent, byref myfun, myform)
' Build the INNER JOIN needed first
key=listname1 & listname2
set conntemp=server.createobject("adodb.connection")
conntemp.open myDSN
set rstemp=conntemp.execute(query)
list1="<select name=""" & listname1 & """"
list1=list1 & " OnChange=""Build" & key
list1=list1 & "(this.selectedIndex+1);"">"
list2="<select name=""" & listname2 & """>"
loopcounter=0
lastvalue=rstemp(0)
redim tempArray(1)
thisgroupcount=0
howmanygroups=0
alldata=rstemp.getrows
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
numrows=ubound(alldata,2)
dim tempScript
redim tempScript(numrows+1)
FOR rowcounter= 0 TO numrows
loopcounter=loopcounter+1
thisvalue=alldata(1,rowcounter)
thisvalue2=alldata(0,rowcounter)
if thisvalue<>lastvalue then
tempArrayDef=key & "[" & howmanygroups & _
"]=new Array(" & thisgroupcount & ");" & _
vbcrlf & tempArrayDef
thisgroupcount=0
howmanygroups=howmanygroups+1
end if
if thisgroupcount=0 then
tempSTR=tempSTR & vbcrlf & "// " & thisvalue & vbcrlf
list1 = list1 & "<option>" & trim(thisvalue) & "</option>" & vbcrlf
end if
tempSTR=TempSTR & key & "[" & howmanygroups & "][" & thisgroupcount & "]=""" & thisvalue2 & """;" & vbCRLF
thisgroupcount=thisgroupcount+1
if howmanygroups=0 then
list2 = list2 & "<option>" & trim(thisvalue2) & "</option>" & vbcrlf
end if
lastvalue=thisvalue
IF LOOPcounter MOD 100 THEN
IF response.isclientconnected=false THEN
EXIT FOR
END IF
END IF
tempScript(loopcounter)=tempSTR
tempSTR=""
NEXT
tempSTR=tempArrayDef
tempSTR=tempSTR & key & "[" & howmanygroups & "]=new Array(" & thisgroupcount & ");" & vbcrlf
tempSTR=tempSTR & vbcrlf & join(tempScript)
list1=list1 & "</select>"
list2=list2 & "</select>"
myevent=vbcrlf & key & "=new Array(" & howmanygroups+1
myevent=myevent & ");" & vbcrlf & tempSTR
tempSTR =vbcrlf & "function Build" & Key & "(num)" & vbcrlf
tempSTR =tempSTR & "{" & vbcrlf
tempSTR =tempSTR & "document." & myForm & "."
tempSTR =tempSTR & listname2 & ".selectedIndex=0;" & vbcrlf
tempSTR =tempSTR & "for(ctr=0;ctr<" & key & "[num].length;ctr++)" & vbcrlf
tempSTR =tempSTR & "{" & vbcrlf
tempSTR =tempSTR & "document." & myform & "." & listname2
tempSTR =tempSTR & ".options[ctr]=new Option(" & key & "[num][ctr],"
tempSTR =tempSTR & key & "[num][ctr]);" & vbcrlf
tempSTR =tempSTR & "}" & vbcrlf
tempSTR =tempSTR & "document." & myForm & "." & listname2
tempSTR =tempSTR & ".length=" & key & "[num].length;" & vbcrlf
tempSTR =tempSTR & "}" & vbcrlf
myfun=tempSTR
END SUB
%>
|