Problems with zip code search in ASP

[eagleman]

I have been developing a dating web site for a customer and I am having problems making the 'zip code search' work properly. The original programmer moved out of the area so it is my job to pick up the pieces and carry the load. In any case, here is my problem. Hopefully someone can help solve my dilemma.

The page with the search form is coded this way:

<form method=post action="searching.asp">
<table border="0" width=100% cellspacing="0" cellpadding="4">
<tr>
<td>
<table>
<tr>
<td nowrap>I am a</td>
<td nowrap>
<select name="Gender" id="Gender">
<option value="Male" selected>Male</option>
<option value="Female" >Female</option>
</select>
seeking a
<select name="Gender_Look" id="Gender_look">
<option value="Male" >Male</option>
<option value="Female" selected>Female</option>
</select>
</td>
<td>&nbsp;</td>
<td nowrap>between ages of
<select name=age_from>
<option value="18" >18
<option value="19" >19
<option value="20" >20
<option value="21" >21
<option value="22" >22
<option value="23" >23
<option value="24" >24
</select>
and
<select name=age_to>
<option value="18" >18
<option value="19" >19
<option value="20" >20
<option value="21" >21
<option value="22" >22
<option value="23" >23
<option value="24" >24
</select>
</td>
</tr>
</table>
</td>
</tr>
<tr>
<td class=section>Location</td>
</tr>
<tr>
<td>
Show profiles within<br>
<select name=country size=5 multiple="multiple">
<option selected="selected" value="" >All Countries</option>
<option value='US' >United States
<option value='AL' >Albania
<option value='AZ' >Algeria
<option value='AS' >American Samoa
<option value='AD' >Andorra
<option value='AO' >Angola
<option value='AI' >Anguilla
<option value='AG' >Antigua & Barbuda
<option value='AR' >Argentina
<option value='AM' >Armenia
<option value='AW' >Aruba
<option value='AU' >Australia
<option value='AT' >Austria
<option value='AP' >Azores
<option value='BS' >Bahamas
<option value='BH' >Bahrain
<option value='BD' >Bangladesh
<option value='BB' >Barbados
<option value='BE' >Belgium
<option value='BZ' >Belize
<option value='BY' >Belarus
<option value='BJ' >Benin
<option value='BM' >Bermuda
<option value='BT' >Bhutan
<option value='BO' >Bolivia
<option value='BL' >Bonaire
<option value='BA' >Bosnia
<option value='BW' >Botswana
<option value='BR' >Brazil
<option value='VG' >British Virgin Islands
<option value='BN' >Brunei
<option value='BG' >Bulgaria
<option value='BF' >Burkina Faso
<option value='BI' >Burundi
</select>
<br>
<table>
<tr>
<td height=20 class=note>Hold down CTRL to select multiple countries</td></tr>
</table>
</td>
</tr>
<tr>
<td>If United States, search Zip Code <input name=search_zip size=5>
within
<select name=search_miles>
<option value=15>15
<option value=50>50
<option value=100>100
<option value=200>200
<option value=500>500
</select>
</td>
</tr>
<tr>
<td class=section>Photo</td>
</tr>
<tr>
<td height=50><input type=checkbox name=photo value="1" checked> Show me profiles with photos only </td>
</tr>
<tr>
<td class=section>Save this Search</td>
</tr>
<tr>
<td>
<input type=checkbox name=save_search value="1"> I want to save this search
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Name this search <input size=30 name=search_name maxlength=50>
</td>
</tr>
<tr>
<td align="right">&nbsp;</td>
</tr>
<tr>
<td align="right"> <input type="submit" name="Submit" value="Submit"> </td>
</tr>
</table>
</form>


Here's the problem. If someone enters a correct zip code (that's in the Access database table), and the US as the country, the following error gets returned:

Microsoft OLE DB Provider for ODBC Driverserror '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Undefined function 'ACOS' in expression.

/include/search.asp, line 198

If I enter an incorrect zip code (one not in the database table): I get this error message:


Microsoft OLE DB Provider for ODBC Driverserror '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error. in query expression '(country like '%US%') and (Photo is not null) and (birth_year <= 1987) and (birth_year >= 1970) and (zip in ( SELECT ZIP_CODE FROM ZIP WHERE 15 > 3959 * ACOS(SIN(/ 57.3) * SIN(LAT/57.3) + COS(/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (/57.3))) )) and (G'.

/include/search.asp, line 198


Looking at the code, I see the following:

'search miles (US only)
if country = "US" then
if len(search_zip) > 0 then
If len(trim(search_zip)) = 5 and isnumeric(search_zip) Then
call SetCoordinates(search_zip)

if has_next then sql = sql & " and "
sql = sql & "("
'miles sql
sql = sql & "zip in ( SELECT ZIP_CODE FROM ZIP WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(LAT/57.3) + COS(@lat/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (@lng/57.3))) )"
sql = Replace(sql, "@radius",search_miles)
sql = Replace(sql, "@lng",dLNG)
sql = Replace(sql, "@lat",dLAT)
sql = sql & ")"
has_next = 1
End If
end if
end if

I really would like this to work.

Im using ASP pages with a Microsoft Access Database. The original programmer was attempting to convert over to sql database but we decided to keep everything in an Access database for now.

Any help would be greatly appreciated.

Thanks!

[wmif]

it looks like your replace functions are taking out the "@lat" and not putting anything in their place.

for instance:
ACOS(SIN(@lat/ 57.3) is becoming
ACOS(SIN(/ 57.3)
you need a value on the left side of the "/".

[eagleman]

That makes sense if the zip code is not in the database table as it would find results for that to replace. What about the main problem?

[wmif]

you seem to be missing a part of your code. you are contatinating your "sql" variable with something, but i dont see it.

[eagleman]

function QuickSearch()
%>
<form method=post action="searching.asp">
<table border="0" width=100% cellspacing="0" cellpadding="4">
<tr>
<td>
<table>
<tr>
<td nowrap>I am a</td>
<td nowrap>
<select name="Gender" id="Gender">
<option value="Male" <%=WriteSelected(Gender, "Male")%>>Male</option>
<option value="Female" <%=WriteSelected(Gender, "Female")%>>Female</option>
</select>
seeking a
<select name="Gender_Look" id="Gender_look">
<option value="Male" <%=WriteSelected(Gender_look, "Male")%>>Male</option>
<option value="Female" <%=WriteSelected(Gender_look, "Female")%>>Female</option>
</select>
</td>
<td>&nbsp;</td>
<td nowrap>
between ages of
<select name=age_from>
<% for a=18 to 120 %>
<option value="<%=a%>" <%if a=18 then response.write "selected"%>><%=a%>
<% next %>
</select>
and
<select name=age_to>
<% for a=18 to 120 %>
<option value="<%=a%>" <%if a=35 then response.write "selected"%>><%=a%>
<% next %>
</select>
</td>
</tr>
</table>
</td>
</tr>

<tr>
<td class=section>Location</td>
</tr>
<tr>
<td>
Show profiles within<br>
<select name=country size=5 multiple="multiple">
<option selected="selected" value="" >All Countries</option>
<%=ListCountries(country)%>
</select>
<br>
<table>
<tr>
<td height=20 class=note>Hold down CTRL to select multiple countries</td>
</tr>
</table>
</td>
</tr>

<tr>
<td>If United States, search Zip Code <input name=search_zip size=5>
within
<select name=search_miles>
<option value=15>15
<option value=50>50
<option value=100>100
<option value=200>200
<option value=500>500
</select>
</td>
</tr>
<tr>
<td class=section>Photo</td>
</tr>
<tr>
<td height=50><input type=checkbox name=photo value="1" checked> Show me profiles with photos only </td>
</tr>
<tr>
<td class=section>Save this Search</td>
</tr>
<tr>
<td>
<input type=checkbox name=save_search value="1"> I want to save this search
<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Name this search <input size=30 name=search_name maxlength=50>
</td>
</tr>
<tr>
<td align="right">&nbsp;</td>
</tr>
<tr>
<td align="right"> <input type="submit" name="Submit" value="Submit"> </td>
</tr>
</table>
</form>
<%
end function

[eagleman]

'search by miles
search_miles = request("search_miles")
search_zip = request("search_zip")

Dim sql
Dim dLAT, dLNG






'search miles (US only)
if country = "US" then
if len(search_zip) > 0 then
If len(trim(search_zip)) = 5 and isnumeric(search_zip) Then
call SetCoordinates(search_zip)

if has_next then sql = sql & " and "
sql = sql & "("
'miles sql
sql = sql & "zip in ( SELECT ZIP_CODE FROM ZIP WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(LAT/57.3) + COS(@lat/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (@lng/57.3))) )"
sql = Replace(sql, "@radius",search_miles)
sql = Replace(sql, "@lng",dLNG)
sql = Replace(sql, "@lat",dLAT)
sql = sql & ")"
has_next = 1
End If
end if
end if






Sub SetCoordinates(sZip)
'get dLNG, dLAT
Dim rsTemp, sSql

sSql = "SELECT LAT, LNG FROM ZIP WHERE ZIP_CODE = '" & sZip & "'"

set rsTemp = conn.execute(sSql)
If not rsTemp.EOF Then
dLAT = rsTemp("LAT")
dLNG = rsTemp("LNG")
End If
Set rsTemp = Nothing
End Sub

'save to session *********************************
session("sql") = sql

'if search by miles

if len(save_search) > 0 then
call SaveSearchQuery(session("user_id"), search_name, fixStr(sql))
end if

response.redirect("user_list.asp")
%>

[eagleman]

set rs = server.createobject("ADODB.Recordset")
rs.PageSize = num_row
rs.CursorLocation = adUseClient
rs.open sql, conn <------ Undefined function in ACOS error 198 here ----]

If Not rs.EOF Then
response.write "<table width=100% cellspacing=0 cellpadding=0>"
If page = "" Then
page = 1
Else
If page < 1 Then
page = 1
Else
If CInt(page) > rs.PageCount Then
page = rs.PageCount
Else
page = CInt(page)
End If
End If
End If

[eagleman]

If you could give me any pointers based on the additional code I added, that would be great.

Thanks in advance.

[wmif]

you have this:

if has_next then sql = sql & " and "
sql = sql & "("
'miles sql
sql = sql & "zip in ( SELECT ZIP_CODE FROM ZIP WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(LAT/57.3) + COS(@lat/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (@lng/57.3))) )"

that first line you are joining this with the variable value that it has already. ive looked through your code and i cant find it. am i missing it somewhere?

also, if you will put a couple lines in your code right before the line you are getting the error on:
response.write sql
response.end()

this will show the query on the page and end the page before the error happens. paste that in here.

[eagleman]

you have this:

if has_next then sql = sql & " and "
sql = sql & "("
'miles sql
sql = sql & "zip in ( SELECT ZIP_CODE FROM ZIP WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(LAT/57.3) + COS(@lat/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (@lng/57.3))) )"

that first line you are joining this with the variable value that it has already. ive looked through your code and i cant find it. am i missing it somewhere?

also, if you will put a couple lines in your code right before the line you are getting the error on:
response.write sql
response.end()

this will show the query on the page and end the page before the error happens. paste that in here.
Thanks for the help so far.

When I addred:

response.write sql
resonse.end()

I received the following:

select * from [user] where (country like '%US%') and (birth_year <= 1987) and (birth_year >= 1970) and (zip in ( SELECT ZIP_CODE FROM ZIP WHERE 15 > 3959 * ACOS(SIN(44.4873/ 57.3) * SIN(LAT/57.3) + COS(44.4873/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (87.9333/57.3))) ))

Any ideas?

[eagleman]

Thanks for the help so far.

When I addred:

response.write sql
resonse.end()

I received the following:

select * from [user] where (country like '%US%') and (birth_year <= 1987) and (birth_year >= 1970) and (zip in ( SELECT ZIP_CODE FROM ZIP WHERE 15 > 3959 * ACOS(SIN(44.4873/ 57.3) * SIN(LAT/57.3) + COS(44.4873/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (87.9333/57.3))) ))

Any ideas?
If it make it easier I could send the directory to you. Perhaps it would be easier to see it that way?

[wmif]

Thanks for the help so far.

When I addred:

response.write sql
resonse.end()

I received the following:

select * from [user] where (country like '%US%') and (birth_year <= 1987) and (birth_year >= 1970) and (zip in ( SELECT ZIP_CODE FROM ZIP WHERE 15 > 3959 * ACOS(SIN(44.4873/ 57.3) * SIN(LAT/57.3) + COS(44.4873/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (87.9333/57.3))) ))

Any ideas?

on the sql query try adding some ()'s in there...

select * from [user] where (country like '%US%') and (birth_year <= 1987) and (birth_year >= 1970) and (zip in ( SELECT ZIP_CODE FROM ZIP WHERE 15 > (3959 * ACOS(SIN(44.4873/ 57.3) * SIN(LAT/57.3) + COS(44.4873/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (87.9333/57.3)))) ))

i think the server is trying to interpret the "15 > 3959" and then trying to move on to the next part where it finds "* ACOS(SIN(44.4873/ 57.3)...". this should force it to calculate that whole last value and then compare it with 15.

[fvcuri]

The Math function ACOS() don't exists in the Access, I was with the same problem some days ago and my problem was solved just replacing the SQL. Try use the follow SQL statement:

3963*(Atn(-(Sin(LATITUDE/57.2958)*Sin(@lat/57.2958)+Cos(LATITUDE/57.2958)*Cos(@lat/57.2958)*Cos(@lng/57.2958-LONGITUDE/57.2958))/Sqr(-(Sin(LATITUDE/57.2958)*Sin(@lat/57.2958)+Cos(LATITUDE/57.2958)*Cos(@lat/57.2958)*Cos(@lng/57.2958-LONGITUDE/57.2958))*(Sin(LATITUDE/57.2958)*Sin(@lat/57.2958)+Cos(LATITUDE/57.2958)*Cos(@lat/57.2958)*Cos(@lng/57.2958-LONGITUDE/57.2958))+1))+2*Atn(1))

Look that the values of LATITUDE and LONGITUDE are converted to radians, if you try calculate in degrees don't will work.

on the sql query try adding some ()'s in there...

select * from [user] where (country like '%US%') and (birth_year <= 1987) and (birth_year >= 1970) and (zip in ( SELECT ZIP_CODE FROM ZIP WHERE 15 > (3959 * ACOS(SIN(44.4873/ 57.3) * SIN(LAT/57.3) + COS(44.4873/ 57.3) * COS(LAT/57.3) * COS((LNG/ 57.3) - (87.9333/57.3)))) ))

i think the server is trying to interpret the "15 > 3959" and then trying to move on to the next part where it finds "* ACOS(SIN(44.4873/ 57.3)...". this should force it to calculate that whole last value and then compare it with 15.