1. Home
  2. Forums Index
  3. Server Side / Microsoft IIS Web Server and ASP.NET 4:00 am Apr 28, 2026

Forum Moderators: open

Message Too Old, No Replies

onChange with a dynamic SELECT from RecordSet

make multiple queries to db by using onChange on a select

         

macrost

4:32 pm on Mar 4, 2003 (gmt 0)

10+ Year Member



Ok forum, another great one... :)
I have a page that self posts and runs a simple sql query to a db. Once I have the results I populate a select with each distinct city that is returned. Now I need to have an onChange when they select the city, then it will run a different query and re-populate the select the the names of the hotels in that specific city. I'm not even sure on where to start. Code is below: 

<%
Response.Buffer = True
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>hotnamesearch</title>
</head>
<script language="javascript" type="text/javascript">
function showhotels(){
/*
thinking this could be some js that will capture the onChange and post back to the page itself to run the second query
*/
}
</script>
<body>
<form name="hotelsearch" method="get" action="#">
<input type="text" name="hotelname">
<input type="submit" value="Search by hotel name">
</form>
<%
If Request("hotelname") = "" then
Response.Flush
Response.End
end If
%>
<%
On Error Resume Next
Dim con,rs, strsearch
strsearch = Request("hotelname")
Set con = Server.CreateObject("ADODB.Connection")
con.ConnectionTimeout=60
con.Open "allhotels"
Set rs = Server.CreateObject("ADODB.RecordSet")
'this is first query to database
sSQL = "SELECT distinct allhotels.City FROM allhotels WHERE allhotels.Name LIKE'" & strsearch & "%'ORDER BY City ASC;"
'this is hotels in city selected query
sSQL = "SELECT distinct allhotels.HotelID, allhotels.Name, allhotels.City, allhotels.StateProvince, allhotels.Country FROM allhotels WHERE allhotels.Name LIKE'" & strsearch & "%'ORDER BY City ASC;"
Set rs = con.Execute(sSQL)
%>
<%
If Err.Number <> 0 Then
Response.Write "An error has occurred!<br>"
Response.Write "Error number:  " & Err.number & "<br>"
Response.Write "Error description: " & Err.description & "<br>"
Else
If rs.EOF Then
Response.Write "We are sorry, we couldn't find a match in our database for that specific hotel." & "<br>"
Else
End If
End If
%>
<table>
<%
If rs.EOF = false Then
Dim city, stateprovince, hotelID, country, hotelName, aname
hotelID = rs.Fields("HotelID").Value
city = rs.Fields("City").Value
hotelName = rs.Fields("Name").Value
Response.Write("<select name='citylist' onchange='javascript:showhotels();'>")
While Not rs.EOF
'If left(rs.Fields.("City").Value,1) 
Response.Write("<option value='" & rs("City") & "'>" & rs("City") & "</option>")
'Response.Write("<tr>" & vbcrlf & "<td>")
'Response.Write(rs.Fields("City").Value & " " & "<br>" & "<a href='?hotelID=" & rs.Fields("HotelId") & "&usersession=1&resType=hotel&version=2'>" & rs.Fields("Name").Value & "</a>")
'Response.Write("</td>" & vbcrlf & "</tr>")
Response.Flush
rs.MoveNext
Wend
Response.Write("</select>")
End If
%>
</table>
</body>
</html>
<%
rs.Close
Set rs = Nothing
con.Close
Set con = Nothing
%>

I really haven't had much exposure to db work in ASP, especially sending a var from js to a self posting asp page.
Any help would be appreciated.
Thanks!
Mac

RossWal

5:52 pm on Mar 4, 2003 (gmt 0)

10+ Year Member




sending a var from js to a self posting asp page

One way would be to place a hidden plain html (vs server control) textbox on the form. Set that value in javascript & retrieve it on the server with the classic asp form("fieldname") syntax.

Also, you may be able to avoid javascript all together by having the selectbox trigger a postback when it changes. I can't remember the details, have a look at the selectbox properties.

HTH,
ROss

macrost

9:24 pm on Mar 4, 2003 (gmt 0)

10+ Year Member



RossWal,
Welp, I nailed it out. Now the client is wanting the ability to misspell the hotel name and still retreive results. 

<%
Response.Buffer = True
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>hotnamesearch</title>
</head>
<script language="javascript" type="text/javascript">
function showhotels(){
/*
thinking this could be some js that will capture the onChange and post back to the page itself to run the second query
*/
//var name = document.dropcity.options[document.dropcity.selectedIndex].value
var name = document.citybox.citylist.options[citybox.citylist.selectedIndex].value;
document.citybox.submit();
}
</script>
<body>
<form name="hotelsearch" method="get" action="#">
<input type="text" name="hotelname">
<input type="submit" value="Search by hotel name">
</form>
<%
If Request("hotelname") = "" and Request("citylist") = "" then
Response.Write("</body>" & vbcrlf)
Response.Write("</html>" & vbcrlf)
Response.Flush
Response.End
end If
%>
<%
On Error Resume Next
Dim con,rs, strsearch, citylist, rs1
strsearch = Request("hotelname")
citylist = Request("citylist")
Set con = Server.CreateObject("ADODB.Connection")
con.ConnectionTimeout=60
con.Open "allhotels"
Set rs = Server.CreateObject("ADODB.RecordSet")
Set rs1 = Server.CreateObject("ADODB.RecordSet")
If citylist = "" Then
'this is first query to database
sSQL = "SELECT distinct allhotels.City FROM allhotels WHERE allhotels.Name LIKE'%" & strsearch & "%'ORDER BY City ASC;"
Set rs = con.Execute(sSQL)
If Err.Number <> 0 Then
Response.Write "An error has occurred!<br>"
Response.Write "Error number:   " & Err.number & "<br>"
Response.Write "Error description: " & Err.description & "<br>"
Else
If rs.EOF Then
Response.Write "We are sorry, we couldn't find a match in our database for that specific hotel." & "<br>"
Else
End If
End If
Else
If citylist <> "" Then
'this is hotels in city selected query
sSQL1 = "SELECT DISTINCT allhotels.HotelID, allhotels.Name, allhotels.City, allhotels.StateProvince, allhotels.Country, allhotels.Address1 FROM allhotels WHERE allhotels.Name LIKE '" & Request("hotelname") & "%' AND allhotels.City= '" & citylist &"';"
Set rs1 = con.Execute(sSQL1)
If Err.Number <> 0 Then
Response.Write "An error has occurred!<br>"
Response.Write "Error number:   " & Err.number & "<br>"
Response.Write "Error description: " & Err.description & "<br>"
Else
If rs1.EOF Then
Response.Write "We are sorry, we couldn't find a match in our database for that specific hotel." & "<br>"
Else
End If
End If
End If
End If
%>
<%
%>
<form name="citybox" action="#" method="get">
<%
'response.write(sSQL & sSQL1)
%>
<table>
<%
If rs.EOF = false Then
Dim city, stateprovince, hotelID, country, hotelName, aname
hotelID = rs.Fields("HotelID").Value
city = rs.Fields("City").Value
hotelName = rs.Fields("Name").Value
If citylist = "" Then
Response.Write("<input type=hidden name=hotelname value='" & strsearch & "'>")
Response.Write("<select name='citylist' onchange='javacsript:showhotels();'>")
While Not rs.EOF
Response.Write("<option value='" & rs("City") & "'>" & rs("City") & "</option>")
Response.Flush
rs.MoveNext
Wend
End If
Response.Write("</select>")
If citylist <> "" Then
While Not rs1.EOF
Response.Write("<tr>" & vbcrlf & "<td>")
Response.Write("<a href='hotinfo.asp?hotelID=" & rs1.Fields("HotelId") & "&usersession=1&resType=hotel&version=2&hotname=" & Server.URLEncode(rs1("Name")) & "&hotaddress=" & Server.URLEncode(rs1("Address1")) & "'>" & rs1.Fields("Name").Value & "</a>")
Response.Write("</td>" & vbcrlf & "</tr>")
Response.Flush
rs1.MoveNext
Wend
End If
End If
%>
</table>
</form>
</body>
</html>
<%
rs.Close
Set rs = Nothing
rs1.Close
Set rs1 = Nothing
con.Close
Set con = Nothing
%>

Do you know how I would apply a soundex to this?
Mac

macrost

2:59 pm on Mar 5, 2003 (gmt 0)

10+ Year Member



RossWal,
Thanks for all your help.
Mac
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

  1. Home
  2. Forums Index
  3. Server Side / Microsoft IIS Web Server and ASP.NET 4:00 am Apr 28, 2026