Help with asp code not claulating distance

[Pinto7]

I have managed to put together some code to first calculate distance between two zip codes.

However, I'm having a lot of trouble getting the informtion out of the database for the calculation funtion to work. Below is my code.
I also have the form for zip code input attached. Maybe someone can shed some light on whats wrong. I am getting "0 Miles" as a result when I run the form.

<%

' This routine calculates the distance between two points
' (given the latitude/longitude of those points). It is being
'used to calculate distance between two ZIP Codes
' South latitudes are negative, east longitudes are
'positive

' Passed to function
'lat1, lon1 = Latitude and Longitude of point 1
'(in decimal degrees)
'lat2, lon2 = Latitude and Longitude of point 2
'(in decimal degrees)
'unit = the unit you desire for results
'where 'M' is statute miles (default)
''K' is kilometers
'N' is nautical miles


Dim strInputSearch 'Variable for the search word
Dim strCon 'Holds the string to connect to the db
Dim adoCon 'Database Connection Variable Object
Dim strSQL 'Holds the SQL query for the database
Dim rsSearch 'Holds the search recordset

'This is the variable that has the search word
strInputSearch = Request.Form("txtSearch")


If len(strInputSearch) >= 4 Then
'This makes it so people cant inject SQL code and/or cause some unwanted

strInputSearch = Replace(strInputSearch,"'", "''", 1, -1, 1)

'This sets the connection
Set adoCon = Server.CreateObject("ADODB.Connection")

'This is the connection string
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("zipbase.mdb")

'Open the connection
adoCon.Open strCon

'Set the database connection
Set rsSearch = Server.CreateObject("ADODB.Recordset")

'This is the SQL statement for searching. You will need to change tblTable and Field to match your database

(they are in red)
strSQL = "SELECT * FROM Zip_Codes WHERE zip&zip2 LIKE '%" & strInputSearch &"%';"


'Opens the database so we can get the results of the search
rsSearch.Open strSQL, adoCon
'

const pi = 3.14159265358979323846

Function distance(lat1, lon1, lat2, lon2, unit)
Dim theta, dist
theta = lon1 - lon2
dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1)) * cos(deg2rad(lat2)) *

cos(deg2rad(theta))
dist = acos(dist)
dist = rad2deg(dist)
distance = dist * 60 * 1.1515
Select Case ucase(unit)
Case "K"
distance = distance * 1.609344
Case "N"
distance = distance * 0.8684
End Select
End Function


'
' This function get the arccos function from arctan function
'
Function acos(rad)
If Abs(rad) <> 1 Then
acos = pi/2 - Atn(rad / Sqr(1 - rad * rad))
ElseIf rad = -1 Then
acos = pi
End If
End function


'
' This function converts decimal degrees to radians
'
Function deg2rad(Deg)
deg2rad = cdbl(Deg * pi / 180)
End Function

'
' This function converts radians to decimal degrees
'
Function rad2deg(Rad)
rad2deg = cdbl(Rad * 180 / pi)
End Function

'Demo
response.write distance(lat1, lon1, lat2, lon2, "M") & " Miles<br>"


Set rsSearch = Nothing
adoCon.Close
End If
%>

Additional questions...
Do I need an additional strInputSearch = Request.Form("txtSearch") field to search the TO: and FROM Zip codes entered in the form?

[BaldEagle]

Couple things:

Select Case ucase(unit)
Case "K"
distance = distance * 1.609344
Case "N"
distance = distance * 0.8684
End Select

Did you want the "N" to be "M"?

Also, I am not seeing where you actually read data out of your db. Is there more code?

BaldEagle

{edit} disregard the first part. I missed the explanation in the comments.

[Pinto7]

Yes, I'm pulling the info from the DB at ..
strInputSearch = Request.Form("txtSearch")

And

strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("zipbase.mdb")

Its near the top of the code.

[miranda]

I see a few things wrong with the code,

You do not assign a value from the database to the variables lat1,lon1, lat2, lon2

you close recordset object but do not release it
you released the connection object but didn't close it 1st

You do not need to include the functions inside the IF /Then statement

I have re written the code and it works in my db

<%

' This routine calculates the distance between two points
' (given the latitude/longitude of those points). It is being
'used to calculate distance between two ZIP Codes
' South latitudes are negative, east longitudes are
'positive

' Passed to function
'lat1, lon1 = Latitude and Longitude of point 1
'(in decimal degrees)
'lat2, lon2 = Latitude and Longitude of point 2
'(in decimal degrees)
'unit = the unit you desire for results
'where 'M' is statute miles (default)
''K' is kilometers
'N' is nautical miles


Dim strInputSearch, fromZip, toZip 'Variables for the search zip
Dim strCon 'Holds the string to connect to the db
Dim adoCon 'Database Connection Variable Object
Dim strSQL 'Holds the SQL query for the database
Dim oRs 'Holds the search recordset
Dim lat1, lon1, lat2, lon2
const pi = 3.14159265358979323846

'This is the variable that has the search word
strInputSearch = Request.Form("txtSearch")

If len(strInputSearch) >= 4 Then
'This makes it so people cant inject SQL code and/or cause some unwanted

strInputSearch = Replace(strInputSearch,"'", "''", 1, -1, 1)

fromZip = Left(strInputSearch,5)
toZip = Right(strInputSearch,5)

'This sets the connection
Set adoCon = Server.CreateObject("ADODB.Connection")

'This is the connection string
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("zipbase.mdb")

'Open the connection
adoCon.Open strCon


'This is the SQL statement for searching. You will need to change tblTable and Field to match your database(they are in red)
strSQL = "SELECT latitude, longitude FROM Zip_Codes WHERE Zip = " & fromZip
Set oRs = adoCon.Execute(strSQL)
lat1 = oRs("latitude")
lon1 = oRs("longitude")
oRs.Close
Set oRs = Nothing
strSQL = "SELECT latitude, longitude FROM Zip_Codes WHERE Zip = " & toZip
Set oRs = adoCon.Execute(strSQL)
lat2 = oRs("latitude")
lon2 = oRs("longitude")
oRs.Close
Set oRs = Nothing
adoCon.Close
set adoCon = Nothing
'
End If


Function distance(lat1, lon1, lat2, lon2, unit)
Dim theta, dist
theta = lon1 - lon2
dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1)) * cos(deg2rad(lat2)) * cos(deg2rad(theta))
dist = acos(dist)
dist = rad2deg(dist)
distance = dist * 60 * 1.1515
Select Case ucase(unit)
Case "K"
distance = distance * 1.609344
Case "N"
distance = distance * 0.8684
End Select
End Function


'
' This function get the arccos function from arctan function
'
Function acos(rad)
If Abs(rad) <> 1 Then
acos = pi/2 - Atn(rad / Sqr(1 - rad * rad))
ElseIf rad = -1 Then
acos = pi
End If
End function


'
' This function converts decimal degrees to radians
'
Function deg2rad(Deg)
deg2rad = cdbl(Deg * pi / 180)
End Function

'
' This function converts radians to decimal degrees
'
Function rad2deg(Rad)
rad2deg = cdbl(Rad * 180 / pi)
End Function
%>

[Pinto7]

I will give it a try,

But How is your DB set up?
I have the following fields in mine:The table name is ZIP_CODES

ZIP, LATITUDE, LONGITUDE, CITY, STATE, COUNTY, ZIP_CLASS

[Pinto7]

When I use the code you gave I get no response,

shouldn't their be a "response.write " at the end some where?

[miranda]

all you need to do is grab the latitude and longitude from the table for each particular zip and use that info to calculate the distance.

fyi my db is set up as recID, city, state, zip, latitude, longitude

[miranda]

When I use the code you gave I get no response,

shouldn't their be a "response.write " at the end some where?


sorry about that. Yes, you need to call the functions.

so before you have the end if add this line

response.write distance(lat1, lon1, lat2, lon2, "M") & " Miles<br>"

[Pinto7]

ok, Im still getting a blank page.

How about the form field?

From looking at the code, I assume I need to change ..
'This is the variable that has the search word
strInputSearch = Request.Form("txtsearch") to ("fromzip")&("tozip") ?

[miranda]

if you want you can change the form to be seperate names then use

From Zip: <input name="fromZip" type="text" /><br />
To Zip: <input name="toZip" type="text" /><br />

and on the processing page then
fromZip = request.Form("fromZip")
toZip = Request.Form("toZip")

btw here is the actual code as i ran it on my machine to test it


<%@ Language=VBScript %>
<%Option Explicit%>
<%

' This routine calculates the distance between two points
' (given the latitude/longitude of those points). It is being
'used to calculate distance between two ZIP Codes
' South latitudes are negative, east longitudes are
'positive

' Passed to function
'lat1, lon1 = Latitude and Longitude of point 1
'(in decimal degrees)
'lat2, lon2 = Latitude and Longitude of point 2
'(in decimal degrees)
'unit = the unit you desire for results
'where 'M' is statute miles (default)
''K' is kilometers
'N' is nautical miles

Dim strInputSearch, fromZip, toZip 'Variable for the search word
Dim myConnStr 'Holds the string to connect to the db
Dim adoCon 'Database Connection Variable Object
Dim strSQL 'Holds the SQL query for the database
Dim oRs 'Holds the search recordset
Dim lat1, lon1, lat2, lon2
const pi = 3.14159265358979323846
'This is the variable that has the search word
strInputSearch = Request.Form("txtSearch")


If len(strInputSearch) >= 11 ' if it is shorter than this there is not 2 zips entered
strInputSearch = preventInjection(strInputSearch)

fromZip = Left(strInputSearch,5)
toZip = Right(strInputSearch,5)
'This sets the connection
Set adoCon = Server.CreateObject("ADODB.Connection")

'This is the connection string
myConnStr = "C:\Inetpub\Database\MCDB.mdb"

'Open the connection
adoCon.Open ("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & myConnStr)

strSQL = "SELECT latitude, longitude FROM zipCodes WHERE Zip = " & fromZip
Set oRs = adoCon.Execute(strSQL)
lat1 = oRs("latitude")
lon1 = oRs("longitude")
oRs.Close
Set oRs = Nothing
strSQL = "SELECT latitude, longitude FROM zipCodes WHERE Zip = " & toZip
Set oRs = adoCon.Execute(strSQL)
lat2 = oRs("latitude")
lon2 = oRs("longitude")
oRs.Close
Set oRs = Nothing
adoCon.Close
set adoCon = Nothing

'Display on screen
response.write distance(lat1, lon1, lat2, lon2, "M") & " Miles<br>"

End If


Function distance(lat1, lon1, lat2, lon2, unit)
Dim theta, dist
theta = lon1 - lon2
dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1)) * cos(deg2rad(lat2)) * cos(deg2rad(theta))
dist = acos(dist)
dist = rad2deg(dist)
distance = dist * 60 * 1.1515
Select Case ucase(unit)
Case "K"
distance = distance * 1.609344
Case "N"
distance = distance * 0.8684
End Select
End Function


'
' This function get the arccos function from arctan function
'
Function acos(rad)
If Abs(rad) <> 1 Then
acos = pi/2 - Atn(rad / Sqr(1 - rad * rad))
ElseIf rad = -1 Then
acos = pi
End If
End function


'
' This function converts decimal degrees to radians
'
Function deg2rad(Deg)
deg2rad = cdbl(Deg * pi / 180)
End Function

'
' This function converts radians to decimal degrees
'
Function rad2deg(Rad)
rad2deg = cdbl(Rad * 180 / pi)
End Function

'prevent SQL interjection --
'SQL interjection is used to take command of a database.
'generally this funtion will have ascii character codes in the replacement
' & # 39; for apostrophe & # 59; for semicolon & # 45;& # 45; for double dash (remove spaces)
'cannot show here because they will display their characters on the screen
Private Function preventInjection(ByRef theString)
theString = Replace(theString, "'", "''") 'removes lone apostrophe's
theString = Replace(theString, ";", "") 'removes semicolon
theString = Replace(theString, "--", "") 'removes double dash sql comment
preventInjection = theString
End Function
%>

[Pinto7]

miranda,

For some reason this code isn't working for me, I will try to go through it again and repost what i have for someone to see what I could be doing wrong..

[Pinto7]

I got it to work,

The problem was in the query statement
strSQL = "SELECT latitude, longitude FROM zip_Codes WHERE Zip = " & fromZip

I changed it to
strSQL = "SELECT latitude, longitude FROM Zip_Codes WHERE Zip LIKE '%" & fromZip &"%';"

But now I get a mileage calculation that is not correct. example if I enter the same zip code for the To & From I get the following:
77449 - 77449 = 1488.549824147 Miles

Now need to find why the calculations are comming out wrong.

[Pinto7]

OK I finally got it work perfect
BIG Thanks to Miranda !

Below is my code as it works on my Machine
<%@ Language=VBScript %>

<%

Dim strInputSearch, fromZip, toZip 'Variable for the search word
Dim strCon 'Holds the string to connect to the db
Dim adoCon 'Database Connection Variable Object
Dim strSQL 'Holds the SQL query for the database
Dim oRs 'Holds the search recordset
Dim lat1, lon1, lat2, lon2



'This is the variable that has the search word
strInputSearch = Request.Form("txtSearch")


If len(strInputSearch) >= 5 Then
'This makes it so people cant inject SQL code and/or cause some unwanted

strInputSearch = Replace(strInputSearch,"'", "''", 1, -1, 1)



fromZip = Left(strInputSearch,5)
toZip = Right(strInputSearch,5)

'This sets the connection
Set adoCon = Server.CreateObject("ADODB.Connection")

'This is the connection string
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("zipbase.mdb")

'Open the connection
adoCon.Open strCon

strSQL = "SELECT latitude, longitude FROM Zip_Codes WHERE Zip LIKE '%" & fromZip &"%';"
Set oRs = adoCon.Execute(strSQL)
lat1 = oRs("latitude")
lon1 = oRs("longitude")
oRs.Close
Set oRs = Nothing
strSQL = "SELECT latitude, longitude FROM Zip_Codes WHERE Zip LIKE '%" & toZip &"%';"
Set oRs = adoCon.Execute(strSQL)
lat2 = oRs("latitude")
lon2 = oRs("longitude")
oRs.Close
Set oRs = Nothing
adoCon.Close
set adoCon = Nothing

'Display on screen
Response.write distance(lat1, lon1, lat2, lon2, "M") & " Miles<br>"
End If

const pi = 3.14159265358979323846

Function distance(lat1, lon1, lat2, lon2, unit)
Dim theta, dist
theta = lon1 - lon2
dist = sin(deg2rad(lat1)) * sin(deg2rad(lat2)) + cos(deg2rad(lat1)) * cos(deg2rad(lat2)) * cos(deg2rad(theta))
dist = acos(dist)
dist = rad2deg(dist)
distance = dist * 60 * 1.1515
Select Case ucase(unit)
Case "K"
distance = distance * 1.609344
Case "N"
distance = distance * 0.8684
End Select
End Function


'
' This function get the arccos function from arctan function
'
Function acos(rad)
If Abs(rad) <> 1 Then
acos = pi/2 - Atn(rad / Sqr(1 - rad * rad))
ElseIf rad = -1 Then
acos = pi
End If
End function


'
' This function converts decimal degrees to radians
'
Function deg2rad(Deg)
deg2rad = cdbl(Deg * pi / 180)
End Function

'
' This function converts radians to decimal degrees
'
Function rad2deg(Rad)
rad2deg = cdbl(Rad * 180 / pi)
End Function



%>