synchronized combobox example??

longmatch · Jul 10, 2002

Dear All:
Anybody has synchronized comboboxes example? I need both comboboxes connected to database and when the item is selected in the first combobox, the contents in the second combobox will change accordingly based upon the selection .

Thanks in advance

Haijun

nenuknifer · Jul 11, 2002

I hope the following code will be helpful. Hmmm, it is a problem of javascrits not asp I think.

If it is not suitalbe, feel free to tell me.

<html>
<head>
<script language = "javascript">
function set()
{
var option;
option = document.forms[0].select1.selectedIndex;
document.forms[0].select2.selectedIndex = option;
}
</script>
</head>
<body>
<form>
<select name = "select1" onChange = "set()">
<option>option1</option>
<option>option2</option>
</select>
<br>
<select name = "select2">
<option>option1</option>
<option>option2</option>
</select>
</form>
</body>
</html>

longmatch · Jul 11, 2002

Thank you for your help. If I want to fill the combobox with items in a database, how can I do it? I know how to fill the first combobox based upon a table in database, how to synchronize the second one when first combobox is clicked?

big thanks

Haijun

CatManDave · Jul 11, 2002

I pulled this ASP example from the Internet when I was attempting to do the same thing you are wanting to do. It has three dropdown boxes that are using ADO to populate each succeeding box. I was able to alter this example for my particular use to succesfully do just what you are wanting to do.







<%
Set objDC = Server.CreateObject("ADODB.Connection&quot

objDC.Open Application("sample_connectionstring&quot

Set objRS = objDC.Execute("Select Distinct Country FROM Suppliers&quot

%>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber1">
<tr>
<td width="33%"> </td>
<td width="47%">
<FORM METHOD="POST" NAME="Form1" ACTION="our_supplier_in.asp">
<p align="left">
<SELECT NAME="Country" SIZE="1" ONCHANGE=Form1.submit()>
<option selected><% = Request.Form("Country&quot

%></option>
<%
' Continue until we get to the end of the recordset.
Do Until objRS.EOF
' For each record we create a option tag and set it's value to the countr
%>
<OPTION><%= objRS("Country&quot

%></OPTION>
<%
' Get next record
objRS.MoveNext
Loop
%>
</SELECT> <b><font face="Arial" size="2" color="#008080">Choose a Country</font></b></p>
</FORM>
<%

' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
%>




<%
'Some code to hide the second drop down until we make a selection from the first
IF Request.Form("Country&quot

= "" Then
Else
'If Country has a value then we get a list of cities for the second drop down
Set objDC = Server.CreateObject("ADODB.Connection&quot

objDC.Open Application("sample_connectionstring&quot

Set objRS = objDC.Execute("Select City FROM Suppliers WHERE Country = '" & Request.Form("Country&quot

& "'&quot

%>
<FORM METHOD="POST" NAME="Form2" ACTION="our_supplier_in.asp">
<p align="left">
<font face="Arial"><font color="#008080"><b>
<SELECT NAME="City" SIZE="1" ONCHANGE=Form2.submit()>
<option selected><% = Request.Form("City&quot

%></option>
<%
' Continue until we get to the end of the recordset.
Do Until objRS.EOF
' For each record we create a option tag and set it's value to the city
%>
<OPTION><%= objRS("City&quot

%></OPTION>
<%
' Get next record
objRS.MoveNext
Loop
%>
<%
'Set a hidden value in the second form for the Country
'so we can pass it along with the city to the next query
%>
</SELECT></b></font><b><font size="2" color="#008080"> Choose a City</font></b><font color="#008080"><b><input type="hidden" name="Country" value="<% = Request.Form("Country&quot

%>"></b></font></font></p>
</FORM>

<%
' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
End IF
%>



</td>
<td width="20%"></td>
</tr>
</table>
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse" bordercolor="#111111" width="100%" id="AutoNumber2">
<tr>
<td width="33%"> </td>
<td width="47%">
<%
'Make sure we have submitted a city and don't show results until we do
IF Request.Form("city&quot

= "" Then
Else
Set objDC = Server.CreateObject("ADODB.Connection&quot

objDC.Open Application("sample_connectionstring&quot

Set objRS = objDC.Execute("Select * FROM Suppliers WHERE Country = '" & Request.Form("Country&quot

& "' AND City = '" & Request.Form("city&quot

& "'&quot

'Loop through the database and assign the appropriate values to variables
'that we will use later

Do Until objRS.EOF
CompanyName = objRS("CompanyName&quot

ContactName = objRS("ContactName&quot

Address = objRS("Address&quot

City = objRS("City&quot

Region = objRS("Region&quot

Zip = objRS("PostalCode&quot

Phone1 = objRS("Phone&quot

Fax1 = objRS("Fax&quot

Country = objRS("country&quot

objRS.MoveNext
Loop

objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
%>
</div>
<p align="left"><b><font face="Arial" size="2" color="#008080">Our Supplier in: </font>
<font face="Arial" size="2" color="#800000"> <% Response.Write City & ", " & Country %> </font></b>
<br>
<font face="Arial" size="1" color="#008080">
<%
'Set up the display of the record
Response.Write CompanyName & "<br>"
Response.Write ContactName & "<br>"
Response.Write Address & "<br>"
Response.Write City & ", " & Region & " " & Zip & "<br>"
Response.Write "Phone: " & Phone1 & "<br>"
IF Fax1 <> "" Then
Response.Write "Fax: " & Fax1 & "<br>"
End IF
End IF
%>

</font>
</p>

</td>
<td width="20%"> </td>
</tr>
</table>

Hope this helps!
Dave

hblackorby · Jul 11, 2002

Note that CatManDave's example will require you to reload the page and display the data in the different combo boxes. The only way to accomplish what you want without reloading the page is to response.write all the data from the second table in JavaScript arrays. Then when you click an ID in the first combo box, you can use JavaScript to unload all the values in the second and reload the combo box with a different array.

This does require you to response.write ALL possible values for the second combo box into a JavaScript SCRIPT block and send to the browser. If you have a large table, just stick with letting the page reload. Otherwise:

Table1
CatID
CategoryName

Table2
ProductID
ProductName
CatID - as foreign key

<script language="JavaScript">
<%
rs.Open "SELECT * FROM Table1", conn
while not rs.EOF
response.write "var Cat" & rs("CatID&quot

& "();" & vbcrlf
SQL = "SELECT * FROM Table2 WHERE CatID=" & CatID
rs2.Open SQL, conn
j = 1
while not rs2.EOF
response.write "Cat" & CatID & "(" & J & &quot

" = '" & rs2("ProductName&quot

& "';" & vbcrlf
rs2.movenext
wend
rs.movenext
wend %>
</script>

This should give you something like so:
<script language="JavaScript">
var Cat1();
Cat1(1) = "Red Stamps";
Cat1(2) = "Blue Stamps";
var Cat2();
Cat2(1) = "Water";
Cat2(2) = "Soda";
</script>

Then whenever someone selects a new CatID from the first combo box, use JavaScript to unload all the values from the second box, and then load all the new ones from eval("Cat" + CatID + "(0)&quot

etc into the second combo box.

Again, if you don't mind the page refreshing, then use that way. It makes your page size smaller. I just wanted to make you aware there's a different way to do it.
Harold Blackorby
hblackorby@scoreinteractive.com
St. Louis, MO

JuanitaC · Jul 11, 2002

In the FAQs there are some resources for coding linked combo boxes:

faq333-1507

longmatch · Jul 11, 2002

Dear Catmandave:

I would like to use the strategy your provided to solve this problem. But what des the our_supplier_in.asp do?

Thanks

Haijun

CatManDave · Jul 11, 2002

Ignore it

Substitute ACTION="<your current ASP page name>"
for
ACTION="our_supplier_in.asp"

in your first form. This will simply force a redisplay of the form again.

For your second form that has your dynamic drop down box built from your first form, use what would normally be the "ACTION=" function you would use if you had only one form and no dynamic drop down boxes.

In other words, if you are going to update a SQL table from your ASP you could use ACTION="UpdateSql.ASP" here.

Hope this helps,
CatManDave

longmatch · Jul 11, 2002

Dear CatmanDave:
In my application, there is only two combo boxes needed. I am planning to use only one form to solve this problem. My strategy is to load the first combo box items directly from database. I use a function to fill the second combo box based on the first combo box selection. The onchange event will be fired when item in the first combo box is selected, which will submit this form. When this form is submited, one function needs to be called to fill the second combo. I am not sure how to call a function in the same page. Give me your suggestions.

Thanks

Haijun

CatManDave · Jul 11, 2002

There is a way to do that. It's not pretty and the example below is how to do it. I was having a rough time with it asthetically, which is why I decided (since I have the design leeway to do so) to use the two form method instead.

Good Luck!

--------------Dynamically fill combo boxes-------------------------------------
<%
Set rsX = Server.CreateObject("ADODB.Recordset&quot

sQuery = "SELECT Name, ID FROM Manufacturers ORDER BY Name"
rsX.Open sQuery, objConn, adOpenForwardOnly, _
adLockReadOnly, adCmdText
If rsX.EOF Then
Response.Write "No manufacturers.<BR>"
Else
Response.Write "<SELECT NAME=""manufacturer"" SIZE=15>"
Do Until rsX.EOF
Response.Write "<OPTION VALUE=""" & rsX("ID&quot

& _
""">" & rsX("Name&quot

& "</OPTION>"
rsX.MoveNext
Loop
Response.Write "</SELECT>"
End If
rsX.Close
Set rsX = Nothing
%>

That puts the manufacturers' names into a 15-row listbox. The trick in this case is that we need to do something when the user selects an element in the listbox. That's done with the "ONCHANGE" event. Although you could write the whole ONCHANGE handler instream in the tag definition, I prefer the idea of pulling it out into its own <SCRIPT> block.

First you have to change the <SELECT> statement to something like this:

<SELECT NAME="manufacturer" SIZE=15
ONCHANGE="manuselected();">

That tells your browser to invoke a client-side script named "manuselected" whenever someone changes the selection in this listbox. What is it you want to do when the user makes a selection? Ah, there's the rub.

Filling The Second Box (JavaScript Background)

JavaScript lets you manipulate the contents of listboxes and combo boxes at run time. Let's take a simple example. Suppose you have a listbox that you want to clear:

First Entry Second Entry Third Entry Fourth Entry Fifth Entry Sixth Entry Seventh Entry Eighth Entry Ninth Entry Tenth Entry Click here to clear the box.

Assuming that the form was named "f1" and the listbox was named "list1", the script to clear the listbox would look like this:

<SCRIPT LANGUAGE="JavaScript">
function clearcombo(){
for (var i=document.f1.list1.options.length-1; i>=0; i--){
document.f1.list1.options = null;
}
document.f1.list1.selectedIndex = -1;
}
</SCRIPT>

That is, one entry at a time, from the highest-numbered element in the list to the lowest-numbered element in the list, each element of the box is deleted by being set to null. The options[] array is zero based, the highest-numbered element can be found from elementname.options.length - 1. We traverse from the highest index to the lowest index because if we went from lowest to highest, each element we removed would change the indexes of the remaining elements. At the end of the loop, the "selected" element of the list is reset to "-1", which is the JavaScript list/combo convention for "nothing selected."

Suppose you wanted to fill the box back up?

[this is a filler entry] Click here to fill the box.

Here's the script that fills in the box...

<SCRIPT LANGUAGE="JavaScript">
function fillcombo(){
for (var i = 0; i < 10; i++){
document.f1.list1.options[document.f1.list1.options.length]=
new Option("display string " + i, "value " + i);
}
}
</SCRIPT>

The JavaScript "new Option()" command lets you give the contents of the listbox or combo box both a displayable text string (the first parm) and a "value" that gets transmitted back up to the server when the form is submitted (the second parm).

Putting It All Back Together

You can see where we're going with this, right?

At this point our listbox-populating loop looks like this... notice how we've added the "ONCHANGE" handler...

<%
Set rsX = Server.CreateObject("ADODB.Recordset&quot
sQuery = "SELECT Name, ID FROM Manufacturers ORDER BY Name"
rsX.Open sQuery, objConn, adOpenForwardOnly, _
adLockReadOnly, adCmdText
If rsX.EOF Then
Response.Write "No manufacturers.<BR>"
Else
Response.Write "<SELECT NAME=""manufacturer"" SIZE=15 " & _
ONCHANGE=""manuselected();"" >"
Do Until rsX.EOF
Response.Write "<OPTION VALUE=""" & rsX("ID&quot & _
""">" & rsX("Name&quot & "</OPTION>"
rsX.MoveNext
Loop
Response.Write "</SELECT>"
End If
rsX.Close
Set rsX = Nothing
%>

Well, first we have to change our SQL query to something that will pull up not just manufacturers, but car models as well. A comprehensive tutorial in SQL is beyond our current scope (though you could find some good info in this article from LearnASP.com), but in general the "JOIN" clause lets you combine values from multiple tables. In the case of our car models example, the following query would work

SELECT Manufacturers.Name, Cars.Model,
Manufacturers.ID AS ManuID, Cars.ID AS ModelID
FROM Cars INNER JOIN Manufacturers ON
Cars.Manufacturer = Manufacturers.ID
ORDER BY Manufacturers.Name, Cars.Model;

This query would pull up a recordset where all of the models from a manufacturer were listed together, and all of the manufacturers were listed in order. It would be something like shown in this table of our first few car models:

Manufacturer Manufacturer ID Model Model ID
Acura 105 3.2 TL 3024
Acura 105 3.2 RL 3027
Acura 105 Integra 3035
Audi 106 A4 Quattro 3046
Audi 106 A6 Quattro 3049
Audi 106 Fox 4067
BMW 108 318 4101
BMW 108 320 4055
BMW 108 M3 4097
BMW 108 Z3 4105

Notice that in our example we are working with the "ID" fields for both manufacturer and model, which (in our database) are numeric data types. If you are trying to duplicate this exercise against a database with non-numeric keys you will probably have to modify the script to enclose the "ID" fields in quotes.

The trick will be to loop through the recordset, filling in the first listbox with the names and IDs of each of the manufacturers, while at the same time filling in our client-side javascript with the commands that empty and fill in the second listbox. Are you ready?

<%
Set rsX = Server.CreateObject("ADODB.Recordset&quot
sQuery = "SELECT Manufacturers.Name, Cars.Model, " & _
"Manufacturers.ID AS ManuID, Cars.ID AS ModelID " & _
"FROM Cars INNER JOIN Manufacturers " & _
"ON Cars.Manufacturer = Manufacturers.ID " & _
"ORDER BY Manufacturers.Name, Cars.Model"
rsX.Open sQuery, objConn, adOpenForwardOnly, _
adLockReadOnly, adCmdText
If rsX.EOF Then
Response.Write "No manufacturers.<BR>"
Else
' write the manufacturers listbox...
Response.Write "<SELECT NAME=""manufacturer"" SIZE=15" & _
" ONCHANGE=""manuselected(this);"" >"
' write the entry code for the javascript...
sJavaScript = "function manuselected(elem){" & vbCrlf & _
"for (var i = document.f1.model." & _
"options.length; i >= 0; i--){" & vbCrlf & _
"document.f1.model.options = null;" & _
vbCrlf
' loop through the recordset...
Do Until rsX.EOF
' is this a new manufacturer?
If sLastManufacturer <> rsX("Name&quot Then
' if so, add an entry to the first listbox
sLastManufacturer = rsX("Name&quot
Response.Write "<OPTION VALUE=" & rsX("ManuID&quot & _
">" & sLastManufacturer & "</OPTION>"
' and add a new section to the javascript...
sJavaScript = sJavaScript & "}" & vbCrlf & _
"if (elem.options[elem.selectedIndex].value==" & _
rsX("ManuID&quot & &quot{" & vbCrlf
End If
' and add a new model line to the javascript...
sJavaScript = sJavaScript & _
"document.f1.model.options[document." & _
"f1.model.options.length] = new Option('" & _
rsX("Model&quot & "','" & rsX("ModelID&quot & _
"');" & _
vbCrlf
rsX.MoveNext
Loop
' finish the manufacturer listbox...
Response.Write "</SELECT>"
' create the models listbox...
Response.Write "<SELECT NAME=""model"" SIZE=15>"
Response.Write "<OPTION>[none currently selected]</OPTION>"
Response.Write "</SELECT>"
' put the last line on the javascript...
' and write it out...
sJavaScript = sJavaScript & vbCrlf & "}" & vbCrlf & _
"}" & vbCrlf
Response.Write "<SCR" & _
"IPT LANGUAGE=""JavaScript"">" & vbCrlf
Response.Write sJavaScript & vbCrlf & "</SCR" & _
"IPT>" & vbCrlf
End If
rsX.Close
Set rsX = Nothing
%>

Let me know how it turns out for you,
CatManDave

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

synchronized combobox example??

longmatch

Programmer

nenuknifer

Programmer

longmatch

Programmer

CatManDave

MIS

hblackorby

Programmer

JuanitaC

Programmer

longmatch

Programmer

CatManDave

MIS

longmatch

Programmer

CatManDave

MIS

Similar threads

Part and Inventory Search

Sponsor