Server side Dinamic dependent list boxes

LarryLS
LarryLS used Ask the Experts™
on
Hi all Experts!

I need URGENTLY to build a search/select page using 4 dependent list boxes or drop down menus. Client-side is not an option because the size of the data (up to 6,000 records and almost 1 MB).  

The idea is to have this 4 drop down menus displaying a "Make your selection..." message and refresh (requery) the 2nd list box when a selection is made in the first list. Then the values in the 3rd list box are refresh depending on the selection made in the 2nd list box and so on. Finally, when a selection is made on the 4rd list box diferent values will be shown (lets say on a table) for the user to choose (click) one of them. Upon selection a "detail" or "process" page is shown.

For exmple:

First list box.- Manufacturer (Chrysler, Ford, Honda, etc). This can be static.

Second list box.- Year (From 1998 to 2004. However not all Manifacturers have cars for all the years and the idea is to display ONLY the years available for the selected manufacturer).
 
Third list box.- Model (Displays the diferents models available forthe selected manufacturer/year).

Fourth lis box.- Submodel (Displays the diferents models available forthe selected manufacturer/year
Values to show on table.- Up to 5 diferent Insurance plans cost

When a plan is selected (clicked) a new page shows up displaying detailed information for that plan and a  form for the user to type his/her personal and car information.

Now, the data is coming from one single table, and it look something like this:

ID      Manufacturer   Year     Model       Submodel      PlanA-Price     PriceB-Price ... PlanE-Price
0001    Chrysler        1998    ModelA    SubmodelA1         0.00           0.00              0.00
0002    Chrysler        1998    ModelA    SubmodelA2         0.00           0.00              0.00
0003    Chrysler        1998    ModelA    SubmodelA3         0.00           0.00              0.00
0004    Chrysler        1998    ModelB    SubmodelB1         0.00           0.00              0.00
0005    Chrysler        1998    ModelB    SubmodelB2         0.00           0.00              0.00
0006    Chrysler        1998    ModelC    SubmodelC1         0.00           0.00              0.00
0007    Chrysler        1998    ModelC    SubmodelC2         0.00           0.00              0.00
0008    Chrysler        1998    ModelC    SubmodelC3         0.00           0.00              0.00
0009    Chrysler        1998    ModelC    SubmodelC4         0.00           0.00              0.00
0010    Ford              1998    ModelD    SubmodelD1         0.00           0.00              0.00
0011    Ford              1998    ModelD    SubmodelD2         0.00           0.00              0.00
0012    Ford              1998    ModelE    SubmodelE1         0.00           0.00              0.00
0012    Ford              1998    ModelF    SubmodelF1         0.00           0.00              0.00
0012    Ford              1998    ModelF    SubmodelF2         0.00           0.00              0.00

And so on for each Manufacturer from 1998 to 2004.

My intention is to have a html page looking something like this:
----------------------------------------------------------------------------------------------------
Drop-down Menu1    Dp-dwn Menu2   Dp-dwn Menu3    Dp-dwn Menu4
   Manufacturer               Year                 Model                Submodel
----------------------------------------------------------------------------------------------------
                       DISPLAY SUBMODEL SELECTED
Plan A.-      Display price A for Selected submodel                 A dateiled page is shown
Plan B.-      Display price B for Selected submodel               <== for each price   when cliked.                                                          
Plan C.-      Display price C for Selected submodel
----------------------------------------------------------------------------------------------------

Could you PLEASE help me with this?

In advance thanks a lot

Best regards
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2005

Commented:
This should help:

http://www.4guysfromrolla.com/webtech/061399-1.shtml

Fritz the Blank
Top Expert 2006

Commented:
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>

Author

Commented:
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.
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Author

Commented:
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.

Commented:
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

Author

Commented:
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.
Top Expert 2006
Commented:
Try this ...

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

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

sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Temp\FullPathToYourDB.mdb;User Id=admin;Password=;"

sManufacturer = Trim(Request("Manufacturer"))
sYear = Trim(Request("Year"))
sModel = Trim(Request("Model"))
sSubModel = Trim(Request("SubModel"))
%>

<html>

<body>
<form method="post">

<select name="Manufacturer">
<%
sSQL = "select distinct Manufacturer from YourTableName"
oRs.Open sSQL, sConn
sTextFieldName = "Manufacturer"
sValueFieldName = "Manufacturer"
sSelectedValue = sManufacturer
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
</select>
<select name="Year">
<%
sSQL = "select distinct Year from YourTableName where Manufacturer = '" & sManufacturer & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Year"
sValueFieldName = "Year"
sSelectedValue = sYear
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
</select>
<select name="Model">
<%
sSQL = "select distinct Model from YourTableName where Manufacturer = '" & sManufacturer & "' and Year = '" & sYear & "'"
oRs.Open sSQL, sConn
sTextFieldName = "Model"
sValueFieldName = "Model"
sSelectedValue = sModel
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
</select>
<select name="SubModel">
<%
sSQL = "select distinct SubModel from YourTableName where Manufacturer = '" & sManufacturer & "' and Year = '" & sYear & "' and Model = '" & sModel & "'"
oRs.Open sSQL, sConn
sTextFieldName = "SubModel"
sValueFieldName = "SubModel"
sSelectedValue = sSubModel
Call MakeSelect(oRs, sTextFieldName, sValueFieldName, sSelectedValue)
oRs.Close
%>
</select>

<br>

<%
sSQL = "select distinct * from YourTableName where Manufacturer = '" & sManufacturer & "' and Year = '" & sYear & "' and Model = '" & sModel & "' and SubModel = '" & sSubModel & "'"
oRs.Open sSQL, sConn
sTextFieldName = "SubModel"
sValueFieldName = "SubModel"
sSelectedValue = sSubModel

if NOT oRs.EOF then
%>

Plan-A = <%=oRs("PlanA-Price")%><br>
Plan-B = <%=oRs("PlanB-Price")%><br>
Plan-C = <%=oRs("PlanC-Price")%><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
%>

Author

Commented:
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?
Top Expert 2006

Commented:
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.

Author

Commented:
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.

Top Expert 2006

Commented:
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()">

Author

Commented:
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.
Top Expert 2006

Commented:
Probably incorect queries. Add

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

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

Commented:
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"

Author

Commented:
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?

Author

Commented:
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.

Author

Commented:
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.  

Author

Commented:
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.  

Author

Commented:
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.




Expert of the Year 2014
Top Expert 2014

Commented:
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
Top Expert 2006

Commented:
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.

Author

Commented:
Thanks for your help Gary.

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

Regards.

Author

Commented:
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

Author

Commented:
Hey RootDir,

I noticed on your profile that you love to work on FLASH projects.

My original idea was to have this working on flash (and still is). So what do you say? Should we give it a try?

PLEASE let me know. Im very interested.

Regards.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial