Sort vbscript Database Query by Zip Code/Miles

Hi Experts,

I'm performing an vbscript database query and database results display.

Question: How do I sort the result of a calculation?

The code below queries a database to obtain the distance in miles.  Need to sort the results (Distance) in miles from closest to farthest.

I must be missing something obvious!

Thanks in advance.  Code is below:

Chris



<%
' 3. Perform a database query which returns all records with the lines of lat/long to each side of requested Zip Code
    DatabaseQuery = "" _
        & "  SELECT * FROM vwTrainerSearch WHERE" _
        & "     Latitude >= " & MinLatitude _
        & "     AND Latitude <=" & MaxLatitude _
        & "     AND Longitude >= " & MinLongitude _
        & "     AND Longitude <= " & MaxLongitude
            
    Dim zcdDistance: Set zcdDistance = new DistanceAssistant
    Set rsZIPCodes = CreateObject("ADODB.Recordset")
    rsZIPCodes.Open DatabaseQuery, dbConn
      
    While Not rsZIPCodes.EOF
       Dim Latitude2, Longitude2, Distance
        Latitude2 = rsZIPCodes("Latitude")
        Longitude2 = rsZIPCodes("Longitude")
        Distance = zcdDistance.Distance(Latitude, Longitude, Latitude2, Longitude2)

      If Distance <= Miles Then

' JUST BELOW IS WHAT I WANT TO SORT BY 'Distance':
Dim strAccount : strStoreLocation = rsZIPCodes("StoreLocation")
Response.Write(strStoreLocation & " - " & (LEFT(Distance,4)) & "<BR>")
      End If
        rsZIPCodes.MoveNext
    Wend
    rsZIPCodes.Close
    Set rsZIPCodes = Nothing
End If
%>
jumpseatnewsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hujiCommented:
Do you have access to the source code of the zcdDistane.Distance function? If yes, then you can change your code in  way that the distance is calculated by SQL server not the Web server, for example by defining a function named distance on the database server, and change your select statment to such:

 DatabaseQuery = "" _
        & "  SELECT x,x,x,x,x,Distance(Latitude,Longitude,Latitude2,Logitude2) AS Dist FROM vwTrainerSearch WHERE" _
        & "     Latitude >= " & MinLatitude _
        & "     AND Latitude <=" & MaxLatitude _
        & "     AND Longitude >= " & MinLongitude _
        & "     AND Longitude <= " & MaxLongitude
        & "     ORDER BY Dist DESC"

Such kind of statement will return the distance in rsZIPCodes("Dist"), and the results are order by dist, decreesing.
hujiCommented:
Having a look at this: http://www.freevbcode.com/ShowCode.asp?ID=5532

I have coded it for you like this:

 DatabaseQuery = "" _
        & "  SELECT a,b,c,d," 'ALL FIELDS YOU WANT TO RETRIEVE   + A COMA AT THE END
        & "  acos(sin((" & Latitude & " * 3.14159265358979323846 / 180)) * sin((Latitude * 3.14159265358979323846 / 180)) + cos((" & Latitude &" * 3.14159265358979323846 / 180)) * cos((Latitude * 3.14159265358979323846 / 180))
 * cos(((" & Longitude & " - Longitude) * 3.14159265358979323846 / 180)))) * 180 / 3.14159265358979323846
 * 60 * 1.1515 * 1.609344 AS Dist "
        & "     FROM vwTrainerSearch WHERE" _
        & "     Latitude >= " & MinLatitude _
        & "     AND Latitude <=" & MaxLatitude _
        & "     AND Longitude >= " & MinLongitude _
        & "     AND Longitude <= " & MaxLongitude
        & "     ORDER BY Dist DESC"


I suppose you are using MS SQL.
I tested a similar code on my MS SQL server and it worked ok. Note that the Dist value is in Kilometers.
This is what I tested on my MS SQL server:

SELECT (acos(sin((32.9697 * 3.14159265358979323846 / 180)) * sin((29.46786 * 3.14159265358979323846 / 180))
 + cos((32.9697 * 3.14159265358979323846 / 180)) * cos((29.46786 * 3.14159265358979323846 / 180))
 * cos(((-96.80322 - -98.53506) * 3.14159265358979323846 / 180)))) * 180 / 3.14159265358979323846
 * 60 * 1.1515 * 1.609344 AS Dist

The result must be  422.7389313940137  kilometers.
jumpseatnewsAuthor Commented:
Thank you for posting this!  I've plugged in the code but did get a
Microsoft OLE DB Provider for SQL Server error '80040e14'

Incorrect syntax near ')'.

/zipsearch.asp, line 127


Line 127 is here:     rsZIPCodes.Open DatabaseQuery, dbConn

Also, I have to return the results in miles as all locations in the U.S.  Thank you for your assistance.

Code is here:

' 3. Perform a database query which returns all records with the lines of lat/long to each side of requested Zip Code

    DatabaseQuery = "" _
        & "  SELECT accout," _
        & "  acos(sin((" & Latitude & " * 3.14159265358979323846 / 180)) * sin((Latitude * 3.14159265358979323846 / 180)) + cos((" & Latitude &" * 3.14159265358979323846 / 180)) * cos((Latitude * 3.14159265358979323846 / 180)) * cos(((" & Longitude & " - Longitude) * 3.14159265358979323846 / 180)))) * 180 / 3.14159265358979323846 * 60 * 1.1515 * 1.609344 AS Dist " _
        & "     FROM vwTrainerSearch WHERE" _
        & "     Latitude >= " & MinLatitude _
        & "     AND Latitude <=" & MaxLatitude _
        & "     AND Longitude >= " & MinLongitude _
        & "     AND Longitude <= " & MaxLongitude _
        & "     ORDER BY Dist DESC"
            
    Dim zcdDistance: Set zcdDistance = new DistanceAssistant
    Set rsZIPCodes = CreateObject("ADODB.Recordset")
    rsZIPCodes.Open DatabaseQuery, dbConn
      
      
' 5.  Loop through each returned record and calculate the distance between Point A and each record

    While Not rsZIPCodes.EOF
       Dim Latitude2, Longitude2, Distance
        Latitude2 = rsZIPCodes("Latitude")
        Longitude2 = rsZIPCodes("Longitude")
        Distance = zcdDistance.Distance(Latitude, Longitude, Latitude2, Longitude2)


' 6. Records more than requested miles are disposed of, and records less than or equal to requested miles are included in a list

      If Distance <= Miles Then

                  
'7. Display appropriate information to the user...
'this ZIP Code is within Miles of ZIPCode



                  Dim strAccount : strAccount = rsZIPCodes("account")
                  Response.Write(strAccount & " " & strzip & " - " & (LEFT(Distance,4)) & "<BR>")
        End If
        rsZIPCodes.MoveNext
    Wend
    rsZIPCodes.Close
    Set rsZIPCodes = Nothing
End If



%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Zip Search</title>
</head>

<body>
<form id="form1" method="post" action="zipsearch.asp">
<p>Zip Code:
<input name="txtZip" type="text" id="txtZip" />
<br />
Miles:
<input name="txtMiles" type="text" id="txtMiles" />
</p>
<p>
<input type="submit" name="Submit" value="Search" />
</p>
</form>
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

hujiCommented:
There is a bug with what I have posted. I'll check it out asap, and tell you the results. I'll also change it to show in miles.
hujiCommented:
By the way, are you using SQL server?
jumpseatnewsAuthor Commented:
Yeah, MS SQL 2005.  Thanks for your help!!!!

Christopher
hujiCommented:
You had to change this:

 3.14159265358979323846 / 180)))) * 180 / 3.14159265358979323846

to:

 3.14159265358979323846 / 180))) * 180 / 3.14159265358979323846

Now in order to make it show the distances in Miles, I'm posting the complete SQL query for you:

DatabaseQuery = "" &_
        "SELECT *" &_
        " , acos(sin((" & Latitude & " * 3.14159265358979323846 / 180)) * sin((Latitude * 3.14159265358979323846 / 180)) " &_
            " + cos((" & Latitude &" * 3.14159265358979323846 / 180)) * cos((Latitude * 3.14159265358979323846 / 180)) * cos(((" &_
            Longitude & " - Longitude) * 3.14159265358979323846 / 180))) * 180 / 3.14159265358979323846 * 60 * 1.1515 " &_
            " AS Dist " &_
            "     FROM vwTrainerSearch WHERE" &_
            "     Latitude >= " & MinLatitude &_
            "     AND Latitude <=" & MaxLatitude &_
            "     AND Longitude >= " & MinLongitude &_
            "     AND Longitude <= " & MaxLongitude &_
            "     ORDER BY Dist DESC"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jumpseatnewsAuthor Commented:
PERFECT!!!!!!!

You are awesome.  Works perfectly and sorts properly now.

Thank you so much!

All the best,

Chris
jumpseatnewsAuthor Commented:
test
hujiCommented:
:o) you are very welcome!
BTW, what is that test message for?! :op
jumpseatnewsAuthor Commented:
My mistake, had too many web browsers open!  :-)
hujiCommented:
:o)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.