Is this possible

matt37 · December 21st, 2003, 10:40 PM

Been working through some code on a dealer locator, got it all
working, except I need to be able to sort the SQL_List recordset by a
variable total_distance so that the order will sort by the distance closest to furthest,is this poosible Ive tried several things, is there a way to get it to read that variable in the recordset, all I get is error variable not defined or when tried other ways Invalad use of order by clause, The code is below I'll leave this one to the experts, thanks for any thought's

<%
Option Explicit
Dim strSearch

Set Connection = Server.CreateObject("ADODB.Connection")
Connection.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\Inetpub\wwwroot\Distance\us_zip.md b;Uid=Admin;Pwd=pass;"

%>

<html>

<head>
<title></title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"></head>

<body bgcolor="#FFFFFF" text="#000000" topmargin=0 marginheight=0>

<center>
<TABLE>
    <TR>
    <TD><table width="542" border="0" cellpadding="0" cellspacing="0" bgcolor="#9900FF">
      <tr>
        <td width="462"><FORM NAME="ZipForm" METHOD="get" ACTION="showdealers.asp?Postal=<%=Request("search" )%><%=Request("distance")%>">
          Find Stores Within <SELECT NAME="distance" id="distance">
            <option value="10" selected>10 Miles
            <option value="20">20 Miles
            <option value="30">30 Miles
            <option value="40">40 Miles
            <option value="50">50 Miles
            <option value="100">100 Miles
            <option value="150">150 Miles
            </SELECT>
          Of Your Zip <input name="Search" value="Postal Code" size="15" onFocus="this.value = '';">
          <input name="find" type="submit" id="find" value="Find">
        </Form></td>
        <td width="80"> </td>
      </tr>
      </table></td>
</tr>
<TR>
<td valign=top>
<br>
<a href="Login.asp">Admin Menu</a><BR>
<BR>
<%
DIM SQL1, SQL2, sql_list, objRS1, objRS2, ziplist
DIM mydistance, connection, totaldealers, dealers, total_distance,total_dist,shops
IF Request.QueryString("Search") <> "" Then
    Set Connection = Server.CreateObject("ADODB.Connection")
    Connection.open "Driver={Microsoft Access Driver (*.mdb)};Dbq=c:\Inetpub\wwwroot\Distance\us_zip.md b;Uid=Admin;Pwd=pass;"
    'check if the postal code submitted is in valid format
    IF IsNumeric(left(Request.QueryString("Search"),5)) = false then
        Response.Write("Please enter a properly formed postal code.")
    Else
        'grab the location of the user's submitted postal code
        SQL1 = "SELECT * From US_ZIP where zip= '" & Request.QueryString("Search") & "'"
        Set objRS1 = Connection.Execute(SQL1)
        'get a list of locations for all of our dealers by joining on ZIP
        'SQL_list = "SELECT Latitude, Longitude, address, city, state, zip, email, directions "
        SQL_list = "SELECT * From Dealers INNER JOIN US_ZIP ON Dealers.ZIP = US_ZIP.ZIP order by Dealers.ZIP"
        'Response.Write(SQL_list)
        'Response.End
        set objRS2 = Connection.Execute(SQL_list)
        'Set distance = Server.CreateObject("calculate.dist")
        totaldealers = 0
        Do while not objRS2.eof
        IF Not objRS1.EOF Then
            'compare the submitted location with each item in our dealer list and return the distance
            total_distance = round(distance(objRS1("Latitude"),objRS1("Longitud e"),objRS2("Latitude"),objRS2("Longitude"),"miles" ),1)
            'total_dist = round(total_distance,1)
        Else
            Response.Write("Your postal code was not found in our current table.")
            Response.End
    End IF
    '=======Change distance here=========='
    If Request("distance") > 20000 Then
        Response.Write("Please enter a smaller distance.")
        Response.End
    End IF
    '=======Change distance here=========='
    IF total_distance =< cInt(Request("distance")) Then
        shops = shops & "<tr><td valign=""top"" width=""350"">"
        shops = shops & "<b>[u]" & objRS2("DealerName") & "</b></u>"
        if total_distance <> "0" then
            shops=shops&"<br><a href=""http://maps.yahoo.com/py/ddResults.py?Pyt=Tmap&tarname=&tardesc=&newname=&n ewdesc=&newHash=&newTHash=&newSts=&newTSts=&tlt=&t ln=&slt=&sln=&newFL=Use+Address+Below&newaddr=&new csz= " & Request.QueryString("search")&"&newcountry=us&newT FL=Use+Address+Below&newtaddr=" & server.URLencode(objRS2("Address")) & "&newtcsz= " & objRS2("ZIP") &"&newtcountry=us&Submit=Get+Directions','popwindo w','toolbar=no,location=no,directories=no,status=n o,menubar=no,scrollbars=yes,resizable=yes,copyhist ory=no,width=600,height=400')"" class=""navlink"" class=""navlink"">Directions To us</A>"
        end if

        if objrs2("email") <> "" then
            shops=shops&"<br><a href=""mailto:" & objRS2("email") & """ class=""navlink"" class=""navlink"">Email</A>"
        end if
        shops = shops & "<br>" & objRS2("Address")
        shops = shops & "<br>" & objRS2("City")
        shops = shops & "<br>" & objRS2("State")
        shops = shops & "<br>" & objRS2("ZIP") & "</td>"
        shops = shops & "<td valign=""top"" width=""150"">"
        if total_distance <> "0" then
            shops = shops & "Miles " & total_distance
        Else
            shops = shops & "Located within the same zip code"
        End if
        shops = shops & "</td></tr>"
        totaldealers = totaldealers + 1
    End IF
    objRS2.movenext
    Loop
    'Give a total for shops found within this distance
    Response.Write("<b>" & totaldealers)
    Response.Write(" Shops within ") & Request("distance") & (" Miles ")
    Response.Write("of " & objRS1("zip_name") & ", " & objRS1("state_abbr"))
    'if we have 1 or more shops that are valid, write the results to the page
    If totaldealers > 0 Then
        Response.Write("<table border=""0"" width=""600"">")
        Response.Write(shops)
        Response.Write("</table>")
    End If
    End IF
    Connection.Close
End IF
%>
</body>

</html>

Imar · December 22nd, 2003, 06:53 AM

Hi there,

I am not sure I understand the question clearly.

Are you just trying to add a dynamic ORDER BY clause?

If so, this should work:

Code:

Dim MyOrderByClause
Dim MySQLStatement

' Retrieve OrderBy from QueryString. 
' Just an example, could be any value / variable
MyOrderByClause = Request.QueryString("ORDERBY")
If MyOrderByClause & "" <> "" Then
  ' We have a valid OrderBy
  ' TBD: Make SQL Safe, to prevent passing illegal / dangerous data
  MySQLStatement = MySQLStatement & " ORDER BY " & MyOrderByClause
End If

If this is not what you're after, could you be more specific?

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

matt37 · December 23rd, 2003, 12:07 AM

I think that's what I'm trying to do, I need that variable inthe order
by clause, I just was'nt sure if you could pass varaibles that
were'nt in the the select statement, I'm not even sure if this is the rihgt way to do it, I just need to sort the details by the miles(distance) away, but the distance is'nt in the database it is a variable created after running the code

Imar · December 23rd, 2003, 04:15 AM

Hi there,

You *can* use columns names in the WHERE and ORDER BY clause that are not listed in the SELECT statement. For example:

SELECT Field1, Field2 FROM MyTable WHERE Field3 = 'Bla' ORDER BY Field4

However, for this to work, the table MyTable needs all four fields of course.

Where do you calculate your distance? In the database and have it returned as another column? In that case, you can use ORDER BY:

SELECT Field1, (Field2 + Field3) AS Total FROM MyTable ORDER BY Total

This will add the value in field 2 to the value in field 3. The result will be stored in a column called Total. You can use this alias to sort the result set on.

If the distance is calculated in ASP code, I guess you need another way to do it. Maybe the easiest way to do this is to put the contents of the recordset in an array, calculate the distance and then sort the array on the distance column. Take a look here: http://www.4guysfromrolla.com/webtech/012799-3.shtml for an article on sorting arrays.

If this doesn't help, I suggest you post the relevant part of your code (strip anything you don't need, like html tags) so we can take a look at it.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

matt37 · December 23rd, 2003, 05:12 PM

Ya the calculation is done in the code, not the database, thanks for
all your ideas I'll try some things and let ya know