Results 1 to 3 of 3

Thread: SQL-problem

  1. #1
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80

    Unanswered: SQL-problem

    Hi!

    I have problem sorting my table. It contains both text and numeric data. I made a links which could be used to sort the table by clicking the column title. I have problems sorting the numeric columns (for example 110 was smaller than 98). After a quick search with google, I found out that I should use COLLATE NUMERIC with the ORDER BY-clause. So I made the following select case:

    -----------------------------------
    sort = Request.QueryString("SortBy")

    select case sort

    case ""
    sql = "SELECT * FROM Autot"

    (this is for text-columns)
    case "Aureknro", "Aumerkki", "Aumalli", "Aukori", "Auvari"
    sql = "SELECT * FROM Autot ORDER BY "& sort &""

    (this is for numeric-columns, and it doesn't work)
    case "Auvm", "Auajetutkm", "Auhinta"
    sql = "SELECT * FROM Autot ORDER BY "& sort &" COLLATE NUMERIC"

    end select
    -----------------------------------

    Error Type:
    Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
    [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Auvm COLLATE NUMERIC'.

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Sounds like you have your numeric values in character fields in your table. You shouldn't have to change collations to do this type of sorting. Double check your table "Autot" and make sure columns "Auvm," "Auajetutkm," and "Auhint" have numeric datatypes.
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Oct 2003
    Location
    Finland
    Posts
    80
    Thanks Seppuku!

Posting Permissions

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