|
xxx
JavaScript Dependent Lists from Database
by Charles Carroll
Jscript and ASP.net CAN mix.
Here is an example of a Database generating Javascript dynamically:
filename=/experiments/clientscript/listdynamicdb.aspx
<%@ Trace="true" traceMode="SortByTime" %>
<%@ Import NameSpace="system.data.oledb" %>
<%'@ Import NameSpace="system.data.sqlclient" %>
<%@ Import NameSpace="system.environment" %>
<script language="vb" runat="server">
dim strConn as string
sub Page_load(s as object, e as eventargs)
' strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;Password=password;Persist Security Info=True;User ID=sa;Initial Catalog=Northwind;Data Source=pceinc\netsdk"
strConn="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE="
strConn &= server.mappath("/experiments/data/biblio.mdb") & ";"
Dim strSQL as string
strSQL="select distinct [Name] from publishers " '' where state='NY'"
WebControlFill(strConn,strSQL,dropPublishers) 'Populate 1st dropdownlist - Publishers
dim intPublisherCount = dropPublishers.Items.Count()
dim sbSQL as new stringbuilder
with sbSQL
.append("SELECT Titles.Title, Publishers.Name ")
.append("FROM Publishers ")
.append("INNER JOIN Titles ON Publishers.PubID = Titles.PubID ")
'.append("WHERE Publishers.state='NY' ")
.append("Order by Publishers.name")
end with
dim sbJavaScript as new stringbuilder
with sbJavaScript
.append("<")
.append("SCRIPT Language=""JavaScript""><!--")
.append(newline)
.append("var PubBooks =new Array( " & intPublisherCount & " ) ;")
dim strJSArray as string
strJSArray=FieldsToJSArray(strConn,sbSQL.tostring(),"PubBooks")
.append(strJSArray)
.append(newline)
.append("function StartMeUp()")
.append("{")
.append("//BookListRebuild(0);")
.append("document.Form1.dropBooks.focus();")
.append(newline)
.append("alert(""All Books are Loaded Now. Thanks for waiting!"");")
.append("}")
'pchu - we move the BookListRebuild function into the same Javascript Block which
' is placed below the html Form on the page
.append(" " & vbcrlf)
.append("//Call this to build Booklist for publishers" & vbcrlf)
.append("function BookListRebuild(num)" & vbcrlf)
.append("{" & vbcrlf)
.append("//Select the first Book entry" & vbcrlf)
.append("var ctr; "& vbcrlf)
.append(" document.Form1.dropBooks.selectedIndex=0;" & vbcrlf)
.append(" //For every contact in the array for this person, add a new option" & vbcrlf)
.append(" for(ctr=0;ctr<PubBooks[num].length;ctr++)" & vbcrlf)
.append("{ document.Form1.dropBooks.options[ctr]=new Option(PubBooks[num][ctr],PubBooks[num][ctr]);}" & vbcrlf)
.append("//Set the length of the select list" & vbcrlf)
.append("document.Form1.dropBooks.length=PubBooks[num].length;" & vbcrlf)
.append("document.Form1.dropBooks.size=PubBooks[num].length;" & vbcrlf)
.append("}"& vbcrlf)
'pchu - we call the StartMeUp() javascript here instead of the BODY ONLOAD="StartMeUp();"
' because the javascript is defined here below the Body tag and the function startmeup
' is undefined.
.append("StartMeUp(); "& vbcrlf)
' make the function that dynamically fills 2nd list
.append("--><")
.append("/Script>")
end with
'Page.RegisterStartupScript("blah",sbJavaScript.ToString())
Page.RegisterClientScriptBlock("blah",sbJavaScript.ToString())
'trace.write("sbJavaScript",sbJavaScript.ToString)
end sub
sub WebControlFill(ParmStrConn as string, parmStrSQL as string, ParmObject as object)
dim conn as oledbConnection
dim rdr as oledbdatareader
TRY
Conn=New OLEDBConnection(ParmStrConn)
Dim Cmd as New OLEDBCommand(ParmStrSQL,Conn)
Conn.Open()
Rdr=Cmd.ExecuteReader()
with ParmObject
.DataSource = Rdr
.DataBind()
end with
CATCH exc1 as exception
trace.write("Bad","things",exc1)
FINALLY
IF Not(rdr Is Nothing)
IF rdr.IsClosed=false THEN Rdr.Close()
End If
If not(conn is Nothing)
IF Conn.State=System.Data.Connectionstate.Open THEN Conn.Close()
End If
END TRY
end sub
function FieldsToJSArray(ParmStrConn as string,ParmStrSQL as string,ParmStrArrayName as string) as string
' Initialize The JSArray Use a DataReader for speed and scalablity
' DataReader has 2 fields Publisher,Title
trace.write("ParmStrSQL",ParmStrSQL)
dim sbJSAll as new stringbuilder
dim sbJSArrayData as new stringbuilder
dim strPubPrev,strPubCurrent,strTitleCurrent as string
dim intGroupCurrentCount,intGroupcount as integer
dim conn as oledbconnection
DIM reader1 as oledbdatareader
TRY
conn=new oledbconnection(strConn)
dim cmd as new oledbcommand(ParmStrSQL,Conn)
Conn.Open()
reader1=cmd.executereader()
'If trace.isenabled
' grdQuery.DataSource=reader1
' grdQuery.DataBind()
' return("--")
'end if
strPubcurrent=""
strPubPrev=""
If reader1.hasrows()=false
return("no data no JS, duh!")
end if
reader1.read()
DO
If strPubPrev=strPubCurrent
with sbJSArrayData
.Append(parmstrArrayName)
.Append("[")
.Append(intGroupcount)
.Append("][")
.Append(intGroupCurrentCount)
.Append("]=""")
.Append(strTitleCurrent)
.Append(""";")
.Append(newline)
end with
else
' We just switched to New Publisher
dim sbJSDeclareArray as new stringbuilder
with sbJSDeclareArray
.append(parmstrArrayName)
.Append("[")
.Append(intGroupcount)
.Append("]=new Array(")
.Append(intGroupcurrentCount)
.Append(");")
.Append(newline)
.append("// ")
.append(strPubCurrent)
.append(newline)
end with
sbJSAll.append(newline)
sbJSAll.append(sbJSDeclareArray)
sbJSAll.append(sbJSArrayData)
sbJsArrayData.Remove(0,sbJsArrayData.Length)
intGroupCurrentCount=0
intGroupcount+=1
' If intGroupcount=6 'pchu THIS TEST STOPS AFTER 5/6 Publisners
' return(sbJSAll.ToString())
' End if
end if
strPubPrev=strPubCurrent
strPubCurrent=reader1(1)
strTitleCurrent=reader1(0)
IF true=false
trace.write("strPubCurrent",strPubCurrent)
trace.write("strTitleCurrent",strTitleCurrent)
trace.write("strPubPrev",strPubPrev)
trace.write("intGroupCount",intGroupCount)
trace.write("intGroupCurrentCount",intGroupCurrentCount)
end if
intGroupCurrentCount+=1
LOOP WHILE reader1.Read()
return(sbJSAll.ToString()) 'pchu - move here to build all dependent dropdown array entries
CATCH exc1 as exception
trace.write("Bad Things","",exc1)
return(sbJSAll.ToString())
FINALLY
IF Not(reader1 Is Nothing)
IF reader1.IsClosed=false THEN Reader1.Close()
End If
If not(conn is Nothing)
IF Conn.State=System.Data.Connectionstate.Open THEN Conn.Close()
End If
END TRY
return(sbJSAll.ToString())
end function
</script>
<html><head>
<title>listdynamicdb.asp</title></head>
<BODY bgcolor="#FFFFFF" OnLoad="">
<FORM Name="Form1" clientID="Form1" runat="server" ID="Form1">
<asp:datagrid id="grdQuery" runat="server" />
Publisher:<br>
<ASP:dropdownlist enableviewstate="false" id="dropPublishers" datatextfield="name" name="pub" onchange="BookListRebuild(this.selectedIndex);" runat="server" />
Use Cursor Arrows up and down<br>
Books:<br>
<ASP:dropdownlist id="dropBooks" name="dropBooks" runat="server" /><br>
</Form>
</Body></Html>
 |  |  |
|
There are many worthy charities!!. But perhaps help starving children in Africa or South America AND help Charles too.
a $5 tip buys him lunch at McDonalds,
a $20 tip buys his kid Hitoshi a new computer game,
a $39 tip buys his daughter Michiko a few nice outfits.
See our donor list.
| |
 | |  |
|
|
|
|