I want to have 2 list boxes on a web page. The first box will have the names of the states. When a user selects a state in the first list box I want to populate the second list box with the 10 biggest cities in that state without going back to the server. This would be easy using JavaScript and the DOM but I want to use VBScript. Anyone know how to do this?
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!
-
Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
SYNCHRONIZED LIST BOXES 5
- Thread starter fspeckman
- Start date
-
1
- #3
you could use rds to achieve this with vbscript.
-1 hit on the server
-dynamic 2nd listbox
basically you'd have 2 instances of the rds control class set to hidden / 1 for states and and the other for cities. the underlying sql statement for the 2nd control would have a state variable which would determine what cities to retrieve (this would be trapped from clicking the 1rst combobox).
the State combobox is based on the 1rst rds control, the City combobox is based on the second.
-1 hit on the server
-dynamic 2nd listbox
basically you'd have 2 instances of the rds control class set to hidden / 1 for states and and the other for cities. the underlying sql statement for the 2nd control would have a state variable which would determine what cities to retrieve (this would be trapped from clicking the 1rst combobox).
the State combobox is based on the 1rst rds control, the City combobox is based on the second.
-
4
- Thread starter
- #5
Here's how I finally did it. NO hits to the server. Works OK as long as the total number of cities isn't very large. All the necessary data is stored on the page itself. Could be made more efficient by using smaller variable names and delimited strings instead of arrays:
<%
'GET ALL THE STATE AND CITY INFO INTO ARRAYS
Set Conn = Server.CreateObject("ADODB.Connection"
OpenString = Session("OpenString"
Conn.Open OpenString
Query = "SELECT * FROM State ORDER BY StateName"
Set RS = Conn.Execute(Query)
Dim StateIDs()
Dim StateNames()
i = 0
Do Until RS.EOF
ReDim Preserve StateIDs(i)
StateIDs(i) = RS("StateID"
StateNames(i) = RS("StateName"
i = i + 1
RS.MoveNext
Loop
NumberOfStates = i - 1
SelectedStateID = StateIDs(0)
Query = "SELECT * FROM City ORDER BY CityName"
Set RS = Conn.Execute(Query)
i = 0
Dim CityIDs()
Dim CityNames()
Dim CityStateIDs() ' STATE ID OF STATE THAT THIS CITY IS IN
Do Until RS.EOF
ReDim Preserve CityIDs(i)
ReDim Preserve CityNames(i)
ReDim Preserve CityStateIDs(i)
CityIDs(i) = RS("CityID"
CityNames(i) = RS("CityName"
CityStateIDs(i) = RS("State_ID"
i = i + 1
RS.MoveNext
Loop
NumberOfCitys = i - 1
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Edit City Name</title>
<%
'CREATE A SCRIPT TO POPULATE THE CITY BOX CORRECTLY WHEN THE STATE BOX CHANGES
Response.Write("<script LANGUAGE=""VBScript"">" & vbCRLF)
Response.Write("Sub StateName_onChange" & vbCRLF)
Response.Write("Dim StateIDs(" & NumberOfStates & "" & vbCRLF)
Response.Write("Dim StateNames(" & NumberOfStates & "" & vbCRLF)
For i = 0 To NumberOfStates
Response.Write("StateIDs(" & i & " = " & StateIDs(i) & vbCRLF)
Response.Write("StateNames(" & i & " = """ & StateNames(i) & """" & vbCRLF)
Next
Response.Write("Dim CityIDs(" & NumberOfCitys & "" & vbCRLF)
Response.Write("Dim CityNames(" & NumberOfCitys & "" & vbCRLF)
Response.Write("Dim CityStateIDs(" & NumberOfCitys & "" & vbCRLF)
For i = 0 To NumberOfCitys
Response.Write("CityIDs(" & i & " = " & CityIDs(i) & vbCRLF)
Response.Write("CityNames(" & i & " = """ & CityNames(i) & """" & vbCRLF)
Response.Write("CityStateIDs(" & i & " = " & CityStateIDs(i) & vbCRLF)
Next
Response.Write("document.EditCity.City.Length = 0" & vbCRLF)
Response.Write("SelectedStateID = StateIDs(document.EditCity.StateName.SelectedIndex)" & vbCRLF)
Response.Write("Dim i" & vbCRLF)
Response.Write("Dim objElement" & vbCRLF)
Response.Write("For i = 0 to Ubound(CityStateIDs)" & vbCRLF)
Response.Write(" If SelectedStateID = CityStateIDs(i) Then" & vbCRLF)
Response.Write(" Set objElement = document.createElement(""OPTION""" & vbCRLF)
Response.Write(" ObjElement.text = CityNames(i)" & vbCRLF)
Response.Write(" ObjElement.value = CityNames(i)" & vbCRLF)
Response.Write(" document.EditCity.City.add(objElement)" & vbCRLF)
Response.Write(" End If" & vbCRLF)
Response.Write("Next" & vbCRLF)
Response.Write("End Sub" & vbCRLF)
Response.Write("</script>" & vbCRLF)
%>
</head>
<body>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td><font face="Tahoma" size="+3" color="#9999CC"> Edit A City Name</font></td>
</tr>
<form name="EditCity" action="edit_city_submit.asp" method="POST">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="RIGHT">
<font face="Arial"> State Name:</font>
</td>
<td>
<select name="StateName">
<%
For i = 0 to NumberOfStates
StateName = StateNames(i)
StateID = StateIDs(i)
If SelectedStateID = StateID Then
%>
<option selected value='<%= StateName %>'><%= StateName %></option>"
<% Else%>
<option value='<%= StateName %>'><%= StateName %></option>"
<%
End If
Next
%>
</select>
</td>
</tr>
<tr>
<td align="RIGHT">
<font face="Arial"> City Name:</font>
</td>
<td>
<select name="City">
<%
For i = 0 to NumberOfCitys
CityName = CityNames(i)
CityID = CityIDs(i)
CityStateID = CityStateIDs(i)
If SelectedStateID = CityStateID Then
%>
<option value='<%= CityName %>'><%= CityName %></option>"
<%
End If
Next
%>
</select>
</td>
</tr>
<tr>
<td align="RIGHT">
<font face="Arial"> New City Name:</font>
</td>
<td>
<input type="Text" name="NewCity" size="30" maxlength="30">
</td>
</tr>
</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
<td align="CENTER">
<input type="Submit" value="Update">
</td>
</tr>
</table>
</form>
</table>
</body>
</html>
<%
'GET ALL THE STATE AND CITY INFO INTO ARRAYS
Set Conn = Server.CreateObject("ADODB.Connection"
OpenString = Session("OpenString"
Conn.Open OpenString
Query = "SELECT * FROM State ORDER BY StateName"
Set RS = Conn.Execute(Query)
Dim StateIDs()
Dim StateNames()
i = 0
Do Until RS.EOF
ReDim Preserve StateIDs(i)
StateIDs(i) = RS("StateID"
StateNames(i) = RS("StateName"
i = i + 1
RS.MoveNext
Loop
NumberOfStates = i - 1
SelectedStateID = StateIDs(0)
Query = "SELECT * FROM City ORDER BY CityName"
Set RS = Conn.Execute(Query)
i = 0
Dim CityIDs()
Dim CityNames()
Dim CityStateIDs() ' STATE ID OF STATE THAT THIS CITY IS IN
Do Until RS.EOF
ReDim Preserve CityIDs(i)
ReDim Preserve CityNames(i)
ReDim Preserve CityStateIDs(i)
CityIDs(i) = RS("CityID"
CityNames(i) = RS("CityName"
CityStateIDs(i) = RS("State_ID"
i = i + 1
RS.MoveNext
Loop
NumberOfCitys = i - 1
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Edit City Name</title>
<%
'CREATE A SCRIPT TO POPULATE THE CITY BOX CORRECTLY WHEN THE STATE BOX CHANGES
Response.Write("<script LANGUAGE=""VBScript"">" & vbCRLF)
Response.Write("Sub StateName_onChange" & vbCRLF)
Response.Write("Dim StateIDs(" & NumberOfStates & "
" & vbCRLF)Response.Write("Dim StateNames(" & NumberOfStates & "
" & vbCRLF)For i = 0 To NumberOfStates
Response.Write("StateIDs(" & i & "
= " & StateIDs(i) & vbCRLF)Response.Write("StateNames(" & i & "
= """ & StateNames(i) & """" & vbCRLF)Next
Response.Write("Dim CityIDs(" & NumberOfCitys & "
" & vbCRLF)Response.Write("Dim CityNames(" & NumberOfCitys & "
" & vbCRLF)Response.Write("Dim CityStateIDs(" & NumberOfCitys & "
" & vbCRLF)For i = 0 To NumberOfCitys
Response.Write("CityIDs(" & i & "
= " & CityIDs(i) & vbCRLF)Response.Write("CityNames(" & i & "
= """ & CityNames(i) & """" & vbCRLF)Response.Write("CityStateIDs(" & i & "
= " & CityStateIDs(i) & vbCRLF)Next
Response.Write("document.EditCity.City.Length = 0" & vbCRLF)
Response.Write("SelectedStateID = StateIDs(document.EditCity.StateName.SelectedIndex)" & vbCRLF)
Response.Write("Dim i" & vbCRLF)
Response.Write("Dim objElement" & vbCRLF)
Response.Write("For i = 0 to Ubound(CityStateIDs)" & vbCRLF)
Response.Write(" If SelectedStateID = CityStateIDs(i) Then" & vbCRLF)
Response.Write(" Set objElement = document.createElement(""OPTION""
" & vbCRLF)Response.Write(" ObjElement.text = CityNames(i)" & vbCRLF)
Response.Write(" ObjElement.value = CityNames(i)" & vbCRLF)
Response.Write(" document.EditCity.City.add(objElement)" & vbCRLF)
Response.Write(" End If" & vbCRLF)
Response.Write("Next" & vbCRLF)
Response.Write("End Sub" & vbCRLF)
Response.Write("</script>" & vbCRLF)
%>
</head>
<body>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td><font face="Tahoma" size="+3" color="#9999CC"> Edit A City Name</font></td>
</tr>
<form name="EditCity" action="edit_city_submit.asp" method="POST">
<tr>
<td>
<table border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="RIGHT">
<font face="Arial"> State Name:</font>
</td>
<td>
<select name="StateName">
<%
For i = 0 to NumberOfStates
StateName = StateNames(i)
StateID = StateIDs(i)
If SelectedStateID = StateID Then
%>
<option selected value='<%= StateName %>'><%= StateName %></option>"
<% Else%>
<option value='<%= StateName %>'><%= StateName %></option>"
<%
End If
Next
%>
</select>
</td>
</tr>
<tr>
<td align="RIGHT">
<font face="Arial"> City Name:</font>
</td>
<td>
<select name="City">
<%
For i = 0 to NumberOfCitys
CityName = CityNames(i)
CityID = CityIDs(i)
CityStateID = CityStateIDs(i)
If SelectedStateID = CityStateID Then
%>
<option value='<%= CityName %>'><%= CityName %></option>"
<%
End If
Next
%>
</select>
</td>
</tr>
<tr>
<td align="RIGHT">
<font face="Arial"> New City Name:</font>
</td>
<td>
<input type="Text" name="NewCity" size="30" maxlength="30">
</td>
</tr>
</td>
</tr>
<tr>
<td> </td>
</tr>
<tr>
<td> </td>
<td align="CENTER">
<input type="Submit" value="Update">
</td>
</tr>
</table>
</form>
</table>
</body>
</html>
Just for anyone else out there, I simplified your script and commented it so that I could get my head around what you had achieved using the document.object manipulation.
Just out of interest does anyone have a help file or something that really explains the Document Object Model and how you can use it to dynamically manpulate stuff? It would be really appreciated as I keep reading stuff about document.object. etc and inner.html and other similar manipulations -> but need to know much more about it !
Here's my edited version for my own understanding:
<html>
<head>
<title>Edit City Name</title>
<%' All this needs to be made server side (not as below)
'CREATE A SCRIPT TO POPULATE THE CITY BOX CORRECTLY WHEN THE STATE BOX CHANGES
<script LANGUAGE="VBScript">
Sub StateName_onChange
Dim StateIDs(NumberOfStates)
Dim StateNames(NumberOfStates)
For i = 0 To NumberOfStates
' Dynamically list all the stateids and statenames until end of array
StateIDs(i)=StateIDs(i)
StateNames(i)=StateNames(i)
Next
Dim CityIDs(NumberOfCitys)
Dim CityNames(NumberOfCitys)
Dim CityStateIDs(NumberOfCitys)
For i = 0 To NumberOfCitys
' Dynamically list all the cities, inc. id's names, and the stateid they belong to
CityIDs(i)=CityIDs(i)
CityNames(i)=CityNames(i)
CityStateIDs(i)=CityStateIDs(i)
Next
' empty the city dropdown box of existing data
document.EditCity.City.Length = 0
' Find out what state is selected
SelectedStateID = StateIDs(document.EditCity.StateName.SelectedIndex)
Dim i
Dim objElement
For i = 0 to Ubound(CityStateIDs)
If SelectedStateID = CityStateIDs(i) Then
' Process the following lines if the selectedstateid=citystateid
Set objElement = document.createElement("OPTION"
' Creates an <OPTION VALUE="cityname">cityname >> for each OPTION NAME
ObjElement.text = CityNames(i)
ObjElement.value = CityNames(i)
' This finally adds the OPTIONs above to the city select box
document.EditCity.City.add(objElement)
End If
Next
End Sub
</script>
' end of server side source
%>
</head>
<body>
Just out of interest does anyone have a help file or something that really explains the Document Object Model and how you can use it to dynamically manpulate stuff? It would be really appreciated as I keep reading stuff about document.object. etc and inner.html and other similar manipulations -> but need to know much more about it !
Here's my edited version for my own understanding:
<html>
<head>
<title>Edit City Name</title>
<%' All this needs to be made server side (not as below)
'CREATE A SCRIPT TO POPULATE THE CITY BOX CORRECTLY WHEN THE STATE BOX CHANGES
<script LANGUAGE="VBScript">
Sub StateName_onChange
Dim StateIDs(NumberOfStates)
Dim StateNames(NumberOfStates)
For i = 0 To NumberOfStates
' Dynamically list all the stateids and statenames until end of array
StateIDs(i)=StateIDs(i)
StateNames(i)=StateNames(i)
Next
Dim CityIDs(NumberOfCitys)
Dim CityNames(NumberOfCitys)
Dim CityStateIDs(NumberOfCitys)
For i = 0 To NumberOfCitys
' Dynamically list all the cities, inc. id's names, and the stateid they belong to
CityIDs(i)=CityIDs(i)
CityNames(i)=CityNames(i)
CityStateIDs(i)=CityStateIDs(i)
Next
' empty the city dropdown box of existing data
document.EditCity.City.Length = 0
' Find out what state is selected
SelectedStateID = StateIDs(document.EditCity.StateName.SelectedIndex)
Dim i
Dim objElement
For i = 0 to Ubound(CityStateIDs)
If SelectedStateID = CityStateIDs(i) Then
' Process the following lines if the selectedstateid=citystateid
Set objElement = document.createElement("OPTION"
' Creates an <OPTION VALUE="cityname">cityname >> for each OPTION NAME
ObjElement.text = CityNames(i)
ObjElement.value = CityNames(i)
' This finally adds the OPTIONs above to the city select box
document.EditCity.City.add(objElement)
End If
Next
End Sub
</script>
' end of server side source
%>
</head>
<body>
- Thread starter
- #7
1. The O'Reilly book: "Dynamic HTML, The Definitive Reference" has the best documentation on the DOM (Document Object Model) that I've found so far. You can preview this book at
2. Regarding your comment "All this needs to be made server side": The whole point of this exercise for me was to have the City list box change when the user changed the State list box WITHOUT going back to the server. If you don't mind going back to the server every time State changes then there are much easier ways to do this without using the DOM. In the code I provided the City list box changes dynamically on the CLIENT side.
2. Regarding your comment "All this needs to be made server side": The whole point of this exercise for me was to have the City list box change when the user changed the State list box WITHOUT going back to the server. If you don't mind going back to the server every time State changes then there are much easier ways to do this without using the DOM. In the code I provided the City list box changes dynamically on the CLIENT side.
Thanks.
Yes your right - I just posted it as it helped me get my head around this particular section, as it was the critical bit. eg. the fact that you can change objects, values, contents, etc on the fly !
Your code led me to Microsoft's MSDN dhtml authoring site which was a good site at least. If anyones interested just goto msdn.microsoft.com and search for 'document object model'. Obvious really !
I'll try the book as well though.
Cheers ;-)
Yes your right - I just posted it as it helped me get my head around this particular section, as it was the critical bit. eg. the fact that you can change objects, values, contents, etc on the fly !
Your code led me to Microsoft's MSDN dhtml authoring site which was a good site at least. If anyones interested just goto msdn.microsoft.com and search for 'document object model'. Obvious really !
I'll try the book as well though.
Cheers ;-)
- Status
Similar threads
- Locked
- Question
