Source Code for SearchResults.asp



<%
'PROCESS VARIABLES FROM REQUEST, BUILD SQL STATEMENT

Dim StateCode
Dim AreaQuad
Dim SpringName
Dim blnState
Dim blnLocate
Dim strSQL
Dim OrderBy

strSQL="SELECT "

StateCode=Request.Form("StateCode")
AreaQuad=Request.Form(UCASE("Location"))
SpringName=Request.Form(UCASE("SpringName"))
OrderBy=Request.Form("OrderBy")

'PROCESS INPUT AND CREATE SQL STATEMENT:

If StateCode = "All" then
If AreaQuad = "" then
'Only Spring Name is selected
strSQL = strSQL & "* FROM USGS_List WHERE Spring_Name LIKE '%" & SpringName & "%'"
Else
'SC not selected, Location is, is Spring name?
If SpringName = "" then
'SC not and name not selected, Location is
strSQL = strSQL & "* from USGS_List WHERE AREA LIKE '%" & AreaQuad & "%'OR USGS_QUAD LIKE '%" & AreaQuad & "%' "
Else
'SC not selected but location and Spring name are
strSQL = strSQL & "* from USGS_List WHERE AREA LIKE '%" & AreaQuad & "%' OR USGS_QUAD LIKE '%" & AreaQuad & "%' OR SPRING_NAME LIKE '%" & SpringName & "%' "
End If
End If
Else
'State code is selected
strSQL = strSQL & "* from USGS_List WHERE SC = '" & StateCode & "' "
If AreaQuad = "" then
'State is selected, area isn't
If SpringName <> "" then
'State and Name are selected, but area isn't
StrSQL = strSQL & "AND SPRING_NAME LIKE '%" & SpringName & "%' "
'There is no "else" here because the variable already contains the statement for state only
End If
Else
'State and Area are selected Is Name?
If SpringName <> "" then
' All are selected, use sub-query
strSQL = "SELECT * from USGS_List WHERE SC = '" & StateCode & "' AND ID = ANY (SELECT ID from USGS_List WHERE AREA LIKE '%" & AreaQuad & "%' OR USGS_QUAD LIKE '%" & AreaQuad & "%' OR SPRING_NAME LIKE '%" & SpringName & "%')"
Else
'Only State and Area are selected
strSQL = strSQL & "AND ID = ANY (SELECT ID from USGS_List WHERE AREA LIKE '%" & AreaQuad & "%' OR USGS_QUAD LIKE '%" & AreaQuad & "%')"
End If
End If
End If
'LAST PART OF STATEMENT IS ORDER BY:
strSQL=strSQL & "ORDER BY " & OrderBy & ";"

'CONNECTING TO DB:

'Create connection to DB
Set Conn = Server.CreateObject("ADODB.Connection")

'Select provider
Conn.Provider = "Microsoft.Jet.OLEDB.4.0"

'Select data source.

Conn.ConnectionString = "Data Source=" & Server.MapPath ("Db/SpringRet.mdb")

'Open the connection
Conn.Open

'CREATE OBJECT, recordset

Set Rs = Server.CreateObject("ADODB.Recordset")

'Open recordset with the connection already created

Set RS = Conn.Execute(strSQL)
%>

<%'WRITE THE OUTPUT%>

<html>

<head>
<title>HS Search Results</title>
<link type="text/css" rel="stylesheet" href="../hse.css"/>
<link type="text/css" rel="stylesheet" href="../HSEStyle.css"/>

</head>
<body vlink="#009999">

<h1 align = "center" class="fancy">Hot Springs Search Results</h1>
<p align = "center">Click on the Spring Name to see a TopoZone map of the location.</p>



<table align = "center" cellspacing="0" cellpadding = "1" border="1">

<TR>
<td class = "header">STATE</td><td class = "header">LAT</td><td class = "header">LONG</td><td class = "header">"Popular" or USGS Spring Name</td><td class = "header">TF</td><td class = "header">TC</td><td class = "header">Area</td><td class = "header">USGS quadrangle</td></TR>
<%If RS.EOF AND RS.BOF then
Response.Write "</table><p align = " & "'" & "center" & "'" & "><b><font color = " & "'" & "#FF0000" & "'" & ">None of the springs in the database match your search criteria.<br />Return to <a href = " & "'" & "SearchForm.asp" & "'" & ">search</a></font></b></p>"
End If
%>

<%Do While Not RS.EOF%>
<tr><td><%= RS.Fields("SC") %></td>
<td><%= RS.Fields("LAT")%></td>
<td><%= RS.Fields("LONG")%></td>
<td><b>
<%
'isn't working
dim locstring
locstring = "../NewWin.asp?loc=http://www.topozone.com/map.asp?qn=y&s=25&size=s&lat="
locstring = locstring & RS.Fields("LAT") & "&lon=-" & RS.Fields("LONG")
'locstring = URLEncode(locstring) 'this throws error
%>
<a target="_blank" href = "http://www.topozone.com/map.asp?qn=y&s=25&size=s&lat=<%=RS.Fields("LAT")%>&lon=-<%=RS.Fields("LONG")%>">
<% SpringName = RS.Fields("SPRING_NAME")
If IsNull(SpringName) then
Response.Write "[No Name]"
else
Response.Write SpringName
End If%></a></b></td>
<td><%=RS.Fields("TF")%></td>
<td><%=RS.Fields("TC")%></td>
<td><%=RS.Fields("AREA")%></td>
<td><%=RS.Fields("USGS_Quad")%></td></tr>
<% RS.MoveNext
Loop
%>

<%
'Deinitialize the Connection and Recordset
set Rs = nothing
set Conn = nothing

%>
</table>
<div align = "center">
<b>
<%
'CREATE LINK TO STATE PAGE IF A STATE WAS SELECTED:
if StateCode <> "All" then
Response.Write "<p><a href = " & "'" & "../" & StateCode & ".asp" & "'" & ">"
Response.Write "View " & StateCode & " Hot Springs Page</a></p>"
End If
%>
</b>

<!--Menu-->
<div align = "center">
<p>&nbsp;</p>
<table width="600" border="0" cellspacing="1" cellpadding="1" align="center">
<tr>
<td width="120">
<div align="center"><font size = "2"><b><a href="../Default.asp">Home</a></b></font></div>
</td>
<td width="120">
<div align="center"><font size = "2"><b><a href="SearchForm.asp">Search
Springs</a></b></font></div>
</td>
<td width="120">
<div align="center"><font size = "2"><b><a href="../Chat_Form.asp">Chat</a></b></font></div>
</td>
<td width="120">
<div align="center"><font size = "2"><b><a href="../USsprings.asp">US Hot
Springs</a></b></font></div>
</td>
<td width="120">
<div align="center"><font size = "2"><b><a href="../Internat.asp">International</a></b></font></div>
</td>
</tr>
<tr>
<td width="120">
<div align="center"><font size="2"></font></div>
</td>
<td width="120">
<div align="center"><font size = "2"><b><a href="../TopicsLinks.asp">Links</a></b></font></div>
</td>
<td width="120">
<div align="center"><font size = "2"><b><a href="../SiteMap.asp">Site Map</a></b></font></div>
</td>
<td width="120">
<div align="center"><font size = "2"><b><a href="../About.asp">About HSE</a></b></font></div>
</td>
<td width="120">
</td>
</tr>
</table>
</div>
</div>
</body>
</html>




<--Back