Server side Dinamic dependent list boxes

This is from another question where I participated and the requirement was similar to yours. In this the first drop down (Company) is populated from the company table. When the company is selected the second drop down (Company Address) is populated from another table. You have similar but 4 level of exact requirement. Add 2 more recordsets and you will have something that you need. Hope this will get you started.

<%
sCompanyId = Request("CompanyId")

sSQL = "select * from Company"
oRs.Open sSQL, oConn
%>
<form name="MyForm" method="post">
<select name="CompanyId" onchange="this.form.submit()">
<%
while Not oRs.EOF
   if (sCompanyId = oRs("CompanyId")) then
       sSelected = "selected"
   else
       sSelected = ""
   end if
%>
<option value="<%=oRs("CompanyId")%>" <%=sSelected%>><%=oRs("CompanyName")%></option>
<%
   oRs.MoveNext
wend
%>
</select>

<%
sCompanyAddress = Request("CompanyAddress")

if (sCompanyId = "") then
  sCompanyId = -1
end if

sSQL = "select * from CompanyAddress where CompanyId = " & sCompanyId
oRs.Close
oRs.Open sSQL, oConn
%>

<select name="CompanyAddress" onchange="this.form.submit()">
<%
while Not oRs.EOF
   if (sCompanyAddress = oRs("CompanyAddress")) then
       sSelected = "selected"
   else
       sSelected = ""
   end if
%>
<option value="<%=oRs("CompanyAddress")%>" <%=sSelected%>><%=oRs("CompanyAddress")%></option>
<%
   oRs.MoveNext
wend
%>
</select>

</form>

Thanks for your help Fritz.

However, as it is mentioned at the end of the article form the link you gave me: No detailed solutions provided. Also, I was already positive sure that the way to go this time is Server-Side.

In fact Im expecting a more detailed example-code.

Thanks any way.

Hi Amit-g.

Thank you very much for your help.

Im sure that is a start. Unfortunally Im a less than a NEWBIE to ASP and javascript (Sorry for not warning you before) and im not quite sure on how to go on from there. Im afraid I need more than a simple push. Even more, as I said Im in a hurry thus, I dont have to much time to try to figure it out playing with the code.

Could you please help me at least with the 3rd pull down menu, and parsing the Plan Prices info to the last table?

Do you know if your example could work having ALL the data in a single table?

Once again, thanks a lot. I do apreciate your help.

if u wanted the list boxes refreshed as per select without posting use xml/xmldom
trust me this is fasted way to fill list boxes without refresh.

function getYear(){
    var strManu = Trim(Form1.selManu.options[Form1.selManu.selectedIndex].value);
    var xmlHTTP = new ActiveXObject("Microsoft.XMLHTTP");
    xmlHTTP.open("POST", "GetYear.asp", false);
    xmlHTTP.send("<REQUEST><MANU>" + strManu + "</MANU></REQUEST>");
    // pass the filter parameter here
    var xmlDOM = new ActiveXObject("Microsoft.XMLDOM");
    xmlDOM.loadXML(xmlHTTP.ResponseText);
    if (xmlDOM.parseError.errorCode != 0)
    {
            alert("Error occurred: " + xmlDOM.parseError.reason);
            return;
    }
    var objS = xmlDOM.documentElement.firstChild;
    var n=1;
    removeRest(document.Form1.selYear);
    while (objS != null)
    {
         // fill your year listbox here
        document.Form1.selYear[n] = new Option(objS.text, objS.attributes(0).value);
        n++;
        objS = objS.nextSibling;
    }
}
function removeRest(box)
{
      for(var i=box.options.length; i > 0; i--)
      {
            box.options[i]= null;
      }
      box.selectedIndex=0;
}
/****************************** inside GetYear.asp *********************************/
<%@ language = Javascript %>
<%
var xmlDOM = Server.CreateObject("Microsoft.XMLDOM");
xmlDOM.load(Request);
if (xmlDOM.parseError != 0)
{
      Response.Write("Error in Request: " + xmlDOM.parseError.reason);
      Response.End();
}
var strManu = xmlDOM.getElementsByTagName("MANU")[0].text;
var objConn = Server.CreateObject("ADODB.Connection");

// connect your database here and get year from table like this

var sSQL = "SELECT year FROM yourtable WHERE Manufacturer  LIKE '" + strManu + "'"

// get data into record set and pass back like this

var strXML = "<RESPONSE>";
while (!rs.EOF)
{
      strXML += "<YEAR id='" + rs.Fields("Year") + "'>" + rs.Fields("Year") + "</YEAR>";
      rs.MoveNext();
}
rs.Close();
strXML += "</RESPONSE>";
Response.Write(strXML);
%>

this will fill year listbox depending upon your selected Manufacturer without refresh. Similarly do for others

Cheers
RootDir

Thank you Rootdir. That is certanlly very interesting. I really like the idea of refreshing the pulldown boxeswithout posting.

Could you please explain me a little bit more about how to put this all togheter to work?  Im new to ASP, so dont even think about xml/xmldom, but I would like to know more about it.  

How do I "link" the getYear function to my dropdown list? Would it be too much to ask for a more complete example, please?

For the first pull down menu (Year using getYear function) there is only one parameter to consider (Manufacturer). However in the third pulldown menu (Models) its necesary to consider 2 parameters (Manufacturer and year). How do I include the second and subsecuents?

I would really love to put this to work your way.

In advance, thank you very much.

Thank you amit.

I tried your code but for some reason im only getting a single empty drop down list box.

In order to simplify it, I left out the 4th dropdown converting  the 3th one to Submodel (intead of model). Here is my code exactly as it is. As you can see, im using a MySQL database.

<%option explicit%>
<%
Dim oRs, sConn, sSQL
Dim sTextFieldName, sValueFieldName, sSelectedValue
Dim sManufacturer, sYear, sModel, sSubModel

set oRs = Server.CreateObject("ADODB.RecordSet")

sConn = "server=localhost;driver=MySQL;db=MyDataBaseName;uid=MyUser;Password=MyUserPassword"

sManufacturer = Trim(Request("Armadora"))
sYear = Trim(Request("Modelo"))
sModel = Trim(Request("Descripcion"))

%>
<html>
<body>
<form method="post">
  <select name="Manufacturer">
    <%
sSQL = "select distinct Armadora from Tarifas"
oRs.Open sSQL, sConn
sTextFieldName = "Armadora"
sValueFieldName = "Armadora"
sSelectedValue = sManufacturer
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>
  <select name="Year">
    <%
sSQL = "select distinct Modelo from Tarifas where Armadora = '" & sManufacturer & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Modelo"
sValueFieldName = "Modelo"
sSelectedValue = sYear
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>
  <select name="Model">
    <%
sSQL = "select distinct Descripcion from Tarifas where Armadora = '" & sManufacturer & "' and Modelo = '" & sYear & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Descripcion"
sValueFieldName = "Descripcion"
sSelectedValue = sModel
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>

  <br>
  <%
sSQL = "select distinct * from Tarifas where Armadora = '" & sManufacturer & "' and Modelo = '" & sYear & "' and Descripcion = '" & sModel & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Descripcion"
sValueFieldName = "Descripcion"
sSelectedValue = sSubModel

if NOT oRs.EOF then
%>
  Plan-A = <%=oRs("TarifaA")%><br>
  Plan-B = <%=oRs("TarifaB")%><br>
  Plan-C = <%=oRs("TarifaC")%><br>
  <%
end if
oRs.Close
%>
  <%
set oRs = nothing
%>
  <input type="submit" name="GetDate" value="Get Data">
</form>
</body>
</html>
<%
Recordset1.Close()
%>
<%
sub MakeSelect(ByRef oRs, ByVal sTextFieldName, ByVal sValueFieldName, ByVal sSelectedValue)
    while not oRs.EOF
         Response.Write "<option value='" & oRs(sValueFieldName) & "'"
         if (sSelectedValue = oRs(sValueFieldName)) then
              Response.Write " selected"
         end if
         Response.Write ">"
         Response.Write oRs(sTextFieldName)
         Response.Write "</option>"
         oRs.MoveNext
    wend
end sub
%>    

Any idea whats going wrong?

The code seems correct. Are you sure all fieldname and table names are correct and you have data in the table? To confirm that add

Response.Write "sSQL = " & sSQL & "<br>"

after each SQL assignment. You will get the SQL on the page. Run that SQL directly in MySQL and see the results.

You might also be getting some error. Since the error message will be embedded in select tag, you might not see it. Do a view source on the browser and scroll down all the way. Look for html tag completion. You might see the exact error if it is what I am suspecting.

Sorry amit_g

It was a problem the way I was trying to connect to MySQL.  

I solved it, and now it shows the 3 drop down list and the get data button. However, only the first one is populated and nothing happens when selecting a Manufacturer. The other 2 are ampty.  Why could this be happening?

Im positive sure about the fields and table names. Anyway Ill double check it once again.

I did not include the code for submitting the form when you choose an option in the select. Currently you will have to select an option and then click on "Get Data" button everytime.

To start the submit on change of the drop down add onchange handler to each select

<select ..... onclick="this.form.submit()">

Well, actually nothing happens even after clicking on "Get Data" button.

Then I added:

 <select ..... onclick="this.form.submit()">

to each dropdown and still nothing happens. Here is the code:

<%@LANGUAGE="VBSCRIPT"%>
<%option explicit%>
<%
Dim oRs, sConn, sSQL
Dim sTextFieldName, sValueFieldName, sSelectedValue
Dim sManufacturer, sYear, sModel

set oRs = Server.CreateObject("ADODB.RecordSet")

sConn = "server=myserver.com;driver=MySQL;db=MyDataBaseName;uid=MyUser;Password=MyUserPassword"

sManufacturer = Trim(Request("Armadora"))
sYear = Trim(Request("Modelo"))
sModel = Trim(Request("Descripcion"))

%>
<html>
<body>
<form method="post">
  <select name="Manufacturer" onclick="this.form.submit()">
    <%
sSQL = "SELECT DISTINCT Armadora  FROM Tarifas  ORDER BY Armadora"
oRs.Open sSQL, sConn
sTextFieldName = "Armadora"
sValueFieldName = "Armadora"
sSelectedValue = sManufacturer
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>
  <select name="Year" onclick="this.form.submit()">
    <%
sSQL = "select distinct Modelo from Tarifas where Armadora = '" & sManufacturer & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Modelo"
sValueFieldName = "Modelo"
sSelectedValue = sYear
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>
  <select name="Model" onclick="this.form.submit()">
    <%
sSQL = "select distinct Descripcion from Tarifas where Armadora = '" & sManufacturer & "' and Modelo = '" & sYear & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Descripcion"
sValueFieldName = "Descripcion"
sSelectedValue = sModel
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>

  <br>
  <%
sSQL = "select distinct * from Tarifas where Armadora = '" & sManufacturer & "' and Modelo = '" & sYear & "' and Descripcion = '" & sModel & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Descripcion"
sValueFieldName = "Descripcion"
sSelectedValue = sModel

if NOT oRs.EOF then
%>
  Plan-A = <%=oRs("AmpliaA")%><br>
  Plan-B = <%=oRs("AmpliaB")%><br>
  Plan-C = <%=oRs("Amplia10")%><br>
  <%
end if
oRs.Close
%>
  <%
set oRs = nothing
%>
  <input type="submit" name="GetDate" value="Get Data">
</form>
</body>
</html>

<%
sub MakeSelect(ByRef oRs, ByVal sTextFieldName, ByVal sValueFieldName, ByVal sSelectedValue)
    while not oRs.EOF
         Response.Write "<option value='" & oRs(sValueFieldName) & "'"
         if (sSelectedValue = oRs(sValueFieldName)) then
              Response.Write " selected"
         end if
         Response.Write ">"
         Response.Write oRs(sTextFieldName)
         Response.Write "</option>"
         oRs.MoveNext
    wend
end sub
%>

Any Idea what is going wrong?

In advance, thanks a lot.

Probably incorect queries. Add

Response.Write "sSQL = " & sSQL & "<br>"

after each SQL assignment and verify those querys (TableName, ColumnName and where conditon).

I just now noticed that you have renamed the form fileds when you extract those but in the tag you have not.

sManufacturer = Trim(Request("Armadora"))
sYear = Trim(Request("Modelo"))
sModel = Trim(Request("Descripcion"))

 <select name="Manufacturer"         =========> should be =======>  <select name="Armadora"
 <select name="Year"         =========> should be =======>  <select name="Modelo"
 <select name="Model"         =========> should be =======>  <select name="Descripcion"

Amit_g,

The only populated drop-down list was showing an odd behaivor, so I changed:

<select ..... onclick="this.form.submit()">

to:

<select ..... onChange="this.form.submit()">

for each select. Just wanted to let you know. I think its the idea if this forums... in case someone else read this inthe future.

In the other hand, this are resesults im getting:

From the first select: sSQL = SELECT DISTINCT Armadora  FROM Tarifas  ORDER BY Armadora<br>
From the 2nd select: sSQL = select distinct Modelo from Tarifas where Armadora = ''<br>
And obviosly, for the 3rd select: sSQL = select distinct Modelo from Tarifas where Armadora = ''<br>

I guess the problem is that the value selected in the first dropdown lists is not being passed/recived to/by the second query. In consecuence there is no way to pass a value to the 3rd one, and that why they are blank all the time.

I verified those queries, of course adding the missing value, and they are ok. Heres what I got:

mysql> select distinct Modelo from Tarifas where Armadora = "BMW";
+--------+
| Modelo |
+--------+
|   2001 |
|   2002 |
|   2003 |
+--------+
3 rows in set (0.02 sec)

Then for the 3rd select query:

mysql> select distinct Descripcion from Tarifas where Armadora = 'BMW' and Modelo = '2002';
+---------------------------------------+
| Descripcion                           |
+---------------------------------------+
| 540 I AUT. FACE LIFT AUT              |
| SERIE 3 323 STD/AUT                   |
| SERIE 5 528 STD/AUT                   |
| SERIE 5 540 STD/AUT                   |
| SERIE 7 740 I CORTO AUT               |
| SERIE Z Z3 1.9 L STD/AUT              |
| SERIE Z Z3 2.8 L STD/AUT              |
| SERIE Z Z3 M COUPE / ROADSTER STD/AUT |
+---------------------------------------+
8 rows in set (0.03 sec)

Do you think the problem is that? How can it be solved?

Once again, you are right. That was the problem.

Somethimes I can be very dummy (On my defense, im newbie).

ITS WORKING NOW.... THANK YOU!!!!

Now I only have to figure out how to make a link to its coresponding detail page for each price.... Any sugestions? If tis not much to ask.

Hey ROOTDIR,

Even when its working now, Im really very interested on the solution you mentioned. I have seen those needles to repost refreshing boxes an I found them very cool.  So PLEASE let me know if you are determined to make it work your way, and I can post it as a new question. I promise as much points as possible.

You can leave me a note in here, or reach me at larrylaffer@hotmail.com.

Just remember my warning: Im a newbie, but wanting to learn....  (For the history of this post I guess you guys already know)

Thank you.  

Amit,

Ive found an odd behaivor. After selecting the manufaturer and year, the models for that combination are displayed as expected. However, at the same time, the year listbox display the smaller value from the previous query and when selecting the model, sometimes it doesn show any prices at all (when that model doesnt exist for the oldest year) or if show the prices for that model but for the oldest one (when it exists).

For example, if I selected Chrysler as Manufacturer, It show in the second box a list from 1990 to 2003. If I select 1998, the 3rd select shows the models existing for that Manufacturer year combination (Chrysler/1998), however, at the same time, the year list box displays the oldest year, in this case, 1990.  The problem is that even when the 3rd list box show the the query for Chrysler/1998/Models, when I select a model from that query the prices shown are those corresponding to that Model but for Chrysler/1990 (when that model exists for that year) or no prices are shown at all.

Could you please help me correcting this problem?

Another problem that I guess will be present is when the user has selected the 3 options and then changing the 2nd or 1ts list-box. Is there a way to reset the form once prices are shown and a change ocurres on any of the 3 list boxes?

In advance, thanks for your help and promp reply.  

Its curios, but I it works ok when using the get data button instead of the Onchange handler. However its unconfortable and not very user friendly.

I almost forget.  Since the first value displayed in the year and model lists are "preselected", the onchange handler doesnt work on first "item" listed, and only the 2nd and subsecuent values of those lists can be choosen.

Thanks.

Try this to take care of your Select boxes.

<form name=myform method="POST">
<%
conn= "driver={MySQL ODBC 3.51 Driver};server=localhost;uid=root;pwd=;database=emaps;"
set rs = server.CreateObject("ADODB.Recordset")

tempSQL=""
call createselect("manufacturer",tempsql)
tempSQL="where manufacturer='" & request("manufacturer") & "'"
call createselect("yeardate",tempsql)
tempSQL="where manufacturer='" & request("manufacturer") & "' and yeardate='" & request("yeardate") & "'"
call createselect("model",tempsql)
tempSQL="where manufacturer='" & request("manufacturer") & "' and yeardate='" & request("yeardate") & "' and model='" & request("model") & "'"
call createselect("submodel",tempsql)

function createselect(selectname,sqlwhere)
   sql="select distinct(" & selectname & ") from cars " & sqlwhere
   rs.open sql,conn
   response.write "<select name=" & selectname & " onchange='myform.submit()'><option>Select a " & selectname & " from the list"
   do until rs.eof
      response.write "<option"
      if request(selectname)=rs(selectname) then response.write " selected"
      response.write " >" & rs(selectname)
      rs.movenext
   loop
   response.write "</select>"
   rs.close
end function
%>
</FORM>

Note I've changed Year to Yeardate since its a reserved word

I meant to write onchange but somehow wrote onclick. You got that already.

The problems you mentioned are because of the fact that this is the very basic functionality. This will work as long as all the things are selected in correct order i.e. select  Manufacturer, then Year, then Model and so on. There is another porblem of onchange not begin fired when the any seleted values is the one user wanted to select for example Chrysler as Manufacturer because of onchange event not begin fired. Solutions to these problems is the next step and will also depend on what you want as far as functionality is concerned.

Problem of first value being the one that user wanted to select not triggering the onchange event -

This can be solved in more than one way. The easier one being to add another option in each select "Select Manufaturer...", "Select Year..." and so on. this can be done by adding a parameter to MakeSelect ...

<%
sub MakeSelect(ByRef oRs, ByVal sTextFieldName, ByVal sValueFieldName, ByVal sSelectedValue, ByVal sSelectLabel)
   Response.Write "<option value='-1'>Please select " & sSelectLabel & " ..."
   Response.Write "</option>"

   while not oRs.EOF
        Response.Write "<option value='" & oRs(sValueFieldName) & "'"
        if (sSelectedValue = oRs(sValueFieldName)) then
             Response.Write " selected"
        end if
        Response.Write ">"
        Response.Write oRs(sTextFieldName)
        Response.Write "</option>"
        oRs.MoveNext
   wend
end sub
%>

and call this function as

Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue, "Manufacturer")
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue, "Year")
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue, "Model")

and so on.

Thanks for your help Gary.

I'll try to put that to work and post how in here how it goes.

Regards.

Amit_g,

I wouldnt like to say this, however the problem described actually is selecting things in correct order.  I even test it on first time visits, first time try (cache cleared and stuff).

Heres my code:

<%@LANGUAGE="VBSCRIPT"%>
<%option explicit%>
<%
Dim oRs, sConn, sSQL
Dim sTextFieldName, sValueFieldName, sSelectedValue
Dim sManufacturer, sYear, sModel

set oRs = Server.CreateObject("ADODB.RecordSet")

sConn = "server=server.com;driver=MySQL;db=MyDBName;uid=MyUser;Password=MyUsersPassword"

sManufacturer = Trim(Request("Armadora"))
sYear = Trim(Request("Modelo"))
sModel = Trim(Request("Descripcion"))

%>
<html>
<body>
<form method="post">
  <select name="Armadora" onChange="this.form.submit()">
    <%
sSQL = "SELECT DISTINCT Armadora  FROM Tarifas  ORDER BY Armadora"
oRs.Open sSQL, sConn
sTextFieldName = "Armadora"
sValueFieldName = "Armadora"
sSelectedValue = sManufacturer
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>
  <select name="Modelo" onChange="this.form.submit()">
    <%
sSQL = "select distinct Modelo from Tarifas where Armadora = '" & sManufacturer & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Modelo"
sValueFieldName = "Modelo"
sSelectedValue = sYear
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>
  <select name="Descripcion" onChange="this.form.submit()">
    <%
sSQL = "select distinct Descripcion from Tarifas where Armadora = '" & sManufacturer & "' and Modelo = '" & sYear & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Descripcion"
sValueFieldName = "Descripcion"
sSelectedValue = sModel
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
  </select>

  <br>
  <%
sSQL = "select distinct * from Tarifas where Armadora = '" & sManufacturer & "' and Modelo = '" & sYear & "' and Descripcion = '" & sModel & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Descripcion"
sValueFieldName = "Descripcion"
sSelectedValue = sModel

if NOT oRs.EOF then
%>
<%Response.Write "sSQL = " & sSQL & "<br>" %>
  Plan-A = <%=oRs("Precio1")%><br>
  Plan-B = <%=oRs("Precio2")%><br>
  Plan-C = <%=oRs("Precio3")%><br>
  <%
end if
oRs.Close
%>
  <%
set oRs = nothing
%>
  <input type="submit" name="GetDate" value="Get Data">
</form>
</body>
</html>

<%
sub MakeSelect(ByRef oRs, ByVal sTextFieldName, ByVal sValueFieldName, ByVal sSelectedValue)
    while not oRs.EOF
         Response.Write "<option value='" & oRs(sValueFieldName) & "'"
         if (sSelectedValue = oRs(sValueFieldName)) then
              Response.Write " selected"
         end if
         Response.Write ">"
         Response.Write oRs(sTextFieldName)
         Response.Write "</option>"
         oRs.MoveNext
    wend
end sub
%>

Have any idea whats wrong in there?

About the first list values solution, I havent tried it yet, but I'll let you know as soon as I have something.

Thanks