ASP/Javascript 3-4 tier dynamic select boxes via db - Need Model

Hey All,

Using ASP and JSP for development and am looking for a good code model to handle.a 4 tier select boxes that feed each other and are populated from SQL Server db.

Issue - these are in a huge data entry form and posting poses a problem.
Pre-built populate functions are out because the amount of data has increased from 50-100 to over 3000 recs.

Thought about loading in frame, posting there and access from there.

e.g.
Distribution Source(Primary Key)>Categories>Sub-Cats>Models

Any URL of a good example would be helpful!

TIA

ironboyzzAsked:
Who is Participating?
 
hc0904pcdConnect With a Mentor Commented:
The second field is

<select name="Branch">
</select>


You'll note I've done this with two dropdown fields so far.
That's because the principle doesn't change.
To go from 2 dropdowns to 4 you add additional javascript - dupe jsCatT1 to jsCatT2,jsCatT3 & jsCatT4. And for each, modify the first & second fields. Then add 2 further loops to the array writing. And then in the body html, extend the fields themselves,

ie

<select name="Categories" onChange="jsCatT2(document.form,document.form.Categories.options[document.form.Categories.selectedIndex].value)"
</select>

<select name="Sub-Cats" onChange="jsCatT2(document.form,document.form.Sub-Cats.options[document.form.Sub-Cats.selectedIndex].value)"
</select>

<select name="Models" onChange="jsCatT2(document.form,document.form.Models.options[document.form.Models.selectedIndex].value)"
</select>


0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
have you thought about using AJAX for this? i think it would be the easiest way to go.

http://digg.com/programming/Simple_Ajax_Drop-Down_Boxes
0
 
hc0904pcdCommented:
I agree that Ajax is probably the way to go, but if you'd like an old asp version, here's one I setup several years ago that has been tested to 5 tiers and is in use on 7 websites that I can think of.
It's quite likely I got the original code from here on EE.
Feel free to update it - this one you have to preset the code for each tier, and I'm sure it could be done better in a loop but every time I think of updating it, I'm more inclined to do it in Ajax if large lists are involved.

Start with this in an external js file

function jsCatT1(inForm,selected) {
var selectedArray = eval(selected + "Array");
while (selectedArray.length < inForm.Branch.options.length) {
inForm.Branch.options[(inForm.Branch.options.length - 1)] = null;
}
for (var i=0; i < selectedArray.length; i++) {
eval("inForm.Branch.options[i]=" + "new Option" + selectedArray[i]);
}
if (inForm.Division.options[0].value == '') {
inForm.Division.options[0]= null;
if ( navigator.appName == 'Netscape') {
if (parseInt(navigator.appVersion) < 4) {
window.history.go(0);
}
else {         
if (navigator.platform == 'Win32' || navigator.platform == 'Win16') {
window.history.go(0);
            }
         }
      }
   }
}

jsCat1 meaning for categories, and tier 1 - feel free to rename that.
The two field names involved here are division and branch - replace them respectively with your own first and second fields.


0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
hc0904pcdCommented:
next, put this in the head of your page, below the above js

<%=writeArrays1%>
0
 
hc0904pcdCommented:
that calls this

function writeArrays1()
            strARR1 = ""
            strARR2 = ""
            response.write "<SCRIPT LANGUAGE='JavaScript'>" & vbCrLf
            response.write "<!-- Begin" & vbCrLf
            sqlstmt1 = "SELECT * from yourTableName WHERE .... fieldName = 'Division' ORDER BY... etc
            rs.open sqlstmt1 ,Conn
            if not rs.eof then
                  do while not rs.eof
                        x = 1
                        t1VarName = rs("fieldValue")
                        t1VarNum  = rs("ID")            
                        t1VarAbbr = rs("fieldValueAbbr")
                        strARR1   = strARR1 & "var " & t1VarAbbr & "Array =  new Array(""('---------','',true,true)"","
                                                
                        sqlstmt2 = "SELECT * from yourTableName WHERE xxx = " & t1VarNum & " AND fieldName = 'Branch' ORDER BY xxx ASC; "
                        rs2.open sqlstmt2 ,Conn
                        if not rs2.eof then            
                              do while not rs2.eof
                                    y = 1
                                    t2VarName = rs2("fieldValue")                                    
                                    t2VarNum  = rs2("fieldValue")
                                    if x > 1 then strARR1 = strARR1 & ""","
                                    strARR1 = strARR1 & """('" & t2VarName & "','" & t2VarNum & "')"                        
                                    x = x + 1
                              rs2.MoveNext
                              loop
                        end if
                        rs2.close()
                        strARR1 = strARR1 &  """);" & vbCrLf
                  rs.MoveNext
                  loop
            end if
            rs.close()                        
            response.write strARR1 & vbCrLf            
            response.write "//End -->" & vbCrLf
            response.write "</script>" & vbCrLf
      end function
0
 
hc0904pcdCommented:
So at this stage, when you load the page, the above code writes a js array using data from your specified tables.
It's a loop within a loop- you loop through Distribution Source first, and within each record you check for Categories that relate to that Distribution Source.


In the html, the first category field looks like this

<select name="Division" onChange="jsCatT1(document.form,document.form.Division.options[document.form.Division.selectedIndex].value)"
<%
  sqlstmt = "SELECT * from yourTableName WHERE fieldName = 'Division' ORDER BY fieldName ASC; "
  rs.open sqlstmt, Conn
   if not rs.eof then
      do while not rs.eof
       optionValue = rs("ID")
        optionLabel = rs("Division")                                                                                                      
         response.write "<option value='" & optionValue & "'"
        'if cstr(optionValue) = cstr(xxx) then response.write " selected"
            response.write ">" & optionLabel & "</option>" & vbCrLf
        rs.MoveNext
      loop
    end if
  rs.close      
%>                        
</select>
0
 
ironboyzzAuthor Commented:
WOW hc0904pcd!

First thanks for the DETAILED description...I was just hoping for a URL.

re: AJAX...not too savvy with that, difficult given scenario? A sample "URL" (lol)of same?

Thanks so much
0
 
ironboyzzAuthor Commented:
Thx for your detailed help with this!  iron
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
AJAX has a reputation as being complicated but its really not. The URL I provided provides links to ajax info in general and some dropdown specific stuff.

B.D.
0
 
hc0904pcdCommented:
hey ironboyzz & big daddy,
Something I'm curious about - I think there's no question that once your lists become big then the resulting arrays get big, and you would be better off using ajax. What's the cutoff line?

If you have just two linked dropdowns, say category and subcat, with 10 items in each - that's a possible 100 options - is that better done with a javascript array or with ajax?

I haven't worked much with Ajax yet. I know it's fast. Is there any downside?
0
 
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
it all depends on how many users use that web page. if you have 10 concurrent users, thats 1000 options. also depends on your server and how fast it is. my point is that its hard to figure out the cut off line, theres too many variables involved.

with ajax, theres really no downfall that i know of. its faster, more efficient, and allows for more flexible functionality in your web app.
0
 
ironboyzzAuthor Commented:
Hey BigD. thanks for the URL.

Actually the data keyed down is not alot,  just a lot keys.  This is a recent breakout sub key drilldown change that is being implemented.  Prior scenario was pre-pop case build but given change not efficient.  Once I hit db and "key" through, max recs is 70-100+- across all ddls.  First list is static after initial build, rest dynamic.

re: Concurrent Users - approx.  peak 4-600.  avg 250 over 24hr period.
My issue is that this is used across entire web app.  Display filters, edits, searches.  Just need dynamic and efficient as possible.

I will research the AJAX for this and see what it will take.  Any further related URLs would be helpful.

Thx all for feedback and scope!

iron
0
 
ironboyzzAuthor Commented:
Found an excellent AJAX DB example that returns an HTML table and XML here:
http://www.w3schools.com/ajax/ajax_database.asp
http://www.w3schools.com/ajax/ajax_xmlfile.asp

Thx all for your help!
0
All Courses

From novice to tech pro — start learning today.