Dynamic Multi-Select Combo Box

[arpan_de]

I have 2 combo boxes in an HTML Form where in a user can SELECT MORE THAN ONE OPTION from either of the 2 combo boxes. The 1st combo box shows the names of countries which are being retrieved from a database. Now if a user selects England from the 1st combo box, the 2nd combo box should display cities that are in England i.e. the contents of the 2nd combo box should change depending upon which country has been selected in the 1st combo box. These city names are also being retrieved from a database. This is how I am doing it (suppose this file is named Country.asp):

<script language="JavaScript">
function gotoURL(object){
window.location.href=object.options[object.selectedIndex].value;
}
</script>
<%
Dim objConn,strSQL,objRS
strSQL="SELECT DISTINCT(Countries) FROM tblCountry"
Set objRS=objConn.Execute(strSQL)
%>
<form>
<select name="country" onChange="gotoURL(this.form.country)" size=5
multiple>
<%
Do Until(objRS.EOF)
%>
<option value='Country.asp?country=<%= objRS("Countries") %>'><%=
objRS("Countries") %>
<%
objRS.MoveNext
Loop
%>
</select>
<%
objRS.Close

Dim sCountry
sCountry=Request.QueryString("country")

strSQL="SELECT Countries,Cities FROM tblCity WHERE Countries='" &
sCountry & "'"
Set objRS=objConn.Execute(strSQL)
%>
<select name="cities" size=5 multiple>
<%
Do Until(objRS.EOF)
%>
<option value='<%= objRS("Cities") %>'> <%= objRS("Cities") %>
<%
objRS.MoveNext
Loop
%>
</select>
</form>

The above code works perfectly when a user selects only one option at a time but does not work if more than one option is selected. Suppose if a user selects England & Australia from the 1st combo box, the 2nd combo box should display the names of those cities that are in England as well as in Australia. Could somebody guide me in this matter i.e. when a user selects more than one country name from the 1st combo box, the 2nd combo
box should show the corresponding city names of all those countries that have been selected in the first combo box?

Thanks,

Arpan

[whammy]

When using select multiple, the IN statement in SQL works very well; i.e.:

strSQL="SELECT Countries,Cities FROM tblCity WHERE Countries IN('" &
sCountry & "')"

IN works like:

WHERE var IN('one','two','three')

This works really great because select multiples are stored as a comma delimited array. So what you can do is just use a replace function, and be a little creative, i.e.:

<select>
<option value="|blah|">blah</option>
<option value="|blah2|">blah2</option>
</select>

sCountry = Replace(Request.Form("sCountry"),"|","'")

Which will go ahead and put a single quote around the values, ready to be inserted into the database...

I think you will like this. I know it made what could have been a difficult report extremely easy for me. :)

[arpan_de]

Hi Whammy,

Thank you for your reply. I don't have any problems in framing the SQL query. The biggest hurdle in my path is in collecting the country names that a user has selected. What I want is as soon as a user selects one country, immediately the 2nd combo box should get populated with those cities which are in that country. Since I want to change the contents of the 2nd combo box immediately as soon as a country is selected, I am using the onChange JavaScript event function. Had the user been compelled to select only one country, I can collect this country name from the querystring which gets generated as soon as a country is selected. That's the reason why I have the option value as

<%
Do Until(objRS.EOF)
%>
<option value="Country.asp?country=<%= objRS("Countries") %>"><%= objRS("Countries") %>
<%
objRS.MoveNext
Loop
%>

So the option value for England will be

<option value="Country.asp?country=England">England

Similarly for Nepal, the option value will be

<option value="Country.asp?country=Nepal">Nepal

Now if a user selects England, the onChange event function gets fired which appends a querystring having a value of England to the URL. Using this querystring, I can filter out city names from the backend.

Bur the problem is how do I collect the names of countries when a user selects more than one country? Because if a user first selects England, immediately the querystring value becomes England. Now if a user presses the Ctrl key & selects Nepal, it doesn't change the querystring value in the URL to England and Nepal. It considers only that country name which was selected first. I want a solution where in I can somehow collect as many countries as a user selects & then using the IN operator in the SQL query retrieve the corresponding city names from the backend.

I hope I am clear enough in expressing my requirements.

Regards,

Arpan

[whammy]

You can do the same thing... just append the multiple select to the querystring... since it will look like:

http://whatever.com/test.htm?blah=1&blah=2&blah=3&blah=4

Then if you just do Request.QueryString("blah") it will still store the data in a comma delimited array, since the variable has more than one value. Get it? :)

Keep in mind you aren't going to be able to do this with javascript, and read from the database. In order to read from the database, you must send a request to the server.

[arpan_de]

That's exactly what I had mentioned in my follow-up question that if a user first selects England, the querystring will have the value as England. That's fine. Next keeping England selected, he presses the Ctrl key & selects Nepal, the querystring will still have England only as the value & not England and Nepal. The querystring will be

www.somesite.com?country=England

It won't be

www.somesite.com?country=England&country=Nepal

It will take only that country into consideration which has been selected first.

Arpan

[whammy]

No, you are wrong:

<head>
<title>test.htm</title>
<script language="JavaScript" type="text/javascript">
<!--
function getURL(x){
window.location = "test.htm" + x
}
// -->
</script>

</head>
<body>

<form name="form1" method="post" action="getURL(this.blah.options[this.blah.selectedIndex].value)">
<select multiple name="blah">
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
<option value="4">4</option>
</select>
<input type="submit" />

</body>
</html>

This example uses javascript (which is irrelevant regarding the querystring in this example), but I hope you see the point - using ASP, the variable retrieved would be a comma delimited array, like I pointed out in my second post, if you use Request.QueryString("blah").

Your problem is with your SQL select statement, in not allowing more than one value(variable = Request.Form("variable") in the SQL statement).

Try out the ideas I have thrown at you, using SQL server's IN statement in combination with passing a multiple select value using the POST method. It works ;)

Just make a basic test page to test it out! You will be pleasantly surprised, I think.

[arpan_de]

Hi Whammy,

I must real appreciate for the way you have been trying to help me out. You haven't got my question correctly. What you have shown is perfectly OK......there is no doubt. But I want that AS SOON AS THE USER SELECTS ONE COUNTRY, THE 2ND COMBO BOX SHOULD DISPLAY THE CITIES OF THAT COUNTRY which means that I am NOT PROVIDING ANY SUBMIT BUTTON to the user for this to happen. And that's exactly the reason why I am using the onChange event so that I get the records IMMEDIATELY AS SOON AS A COUNTRY IS SELECTED FROM THE 1ST COMBO BOX. And that is also the reason why I am setting the values of the options as a URL with a querystring of that particular country. Also note that the URL points to this page only i.e. after a country is selected, the user comes back to this page only. The one & only difference in the URL after a country is selected will be the value of the querystring which will change depending upon which country the user has selected. If he has selected England, the querystring value will be England. If he has selected India, the querystring value will be India.

Arpan

[whammy]

Ok.. you can't do that... it's not possible. Actually I did get your question correct, but I was wrongly assuming you knew the difference between server-side and client-side scripting.

In order to do what you want without refreshing the page, you will have to rely on javascript, and client-side scripts that you have already downloaded to the client's computer.

If you want to get information from a database, you have to send a request to the server that hosts your data.

It's much easier to give up on the "no refresh" idea and do it right with ASP (as I've shown above)- but if you want I will transfer you over to the javascript forum and see what other ideas the gurus there have. :D

[arpan_de]

Well that's not a bad idea. You can transfer me to the JavaScript forum:cool:

[whammy]

done... but what you want to do is not possible.

You really need to live with the solution I gave you, and do it right (not to mention it's about 50 times easier!).