Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Nov 2011
    Location
    Milano (Italy)
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL and ASP: Order by expression

    Hi to all.
    First of all, sorry for my English.
    I have the following problem:
    Environment: ASP and MySQL db

    I have to order a Select by an expression who use some field (gps_gradi_north,gps_primi_north,gps_gradi_east,gps_primi_east) in a table and external functions distance() and converte()

    SQL = "SELECT * FROM Company ORDER BY " & cInt(distance(Latitude,Longitude,converte("gps_gradi_north","gps_primi_north"),converte("gps_gradi_e ast","gps_primi_east"))) & ""

    The logic of the query is: search all records in the db and order them by the distance from the point with coordinates Latitude and Longitude.

    The error is that the function Convert receive the string "gps_gradi_north" and not the value of the record. If I remove " Convert receive a null string.

    Where is the solution ?
    Thanks in advance to all.

    Marco

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,596
    Thanks
    80
    Thanked 4,633 Times in 4,595 Posts
    This entire code makes no sense.

    Your ASP code will run *ONE TIME* and the result will be nothing but a number.

    So you will end up doing the SQL query
    Code:
    SELECT * FROM Company ORDER BY 37
    (or whatever the number turns out to be).

    When you use a number in ORDER BY, it is specifying the *FIELD NUMBER* from the SELECT!!!

    In other words, if you did
    Code:
    SELECT a, b, c, d, e, f FROM Company ORDER BY 5
    That would be the same thing as doing ORDER BY e, the 5th field in the SELECT.

    You can *NOT* call an external function in the middle of a SQL query.

    You will need to rewrite your function to be a MySQL function, *NOT* an ASP function.

    So throw out that code and start over.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    New to the CF scene
    Join Date
    Nov 2011
    Location
    Milano (Italy)
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you for the complete answer.
    I solved my problem sorting an array after the select.

    Thanks
    Marco


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •