Order by array

Discussion in 'ASP General' started by Peter, Nov 14, 2005.

  1. Peter

    Peter Guest

    I put the result into an array getting from another db, the arrangement
    should not be changed,

    MemberList = "'007910', '006853', '007965'"

    SQL = "Select MemberID, name From Member"
    SQL = SQL & " Where MemberID In (" & MemberList & ")"

    Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)

    While not rs.eof
    ........ Wend

    But the order changes to 006853 first, then 007910....007965

    Is it possible to use "order by" to keep the arrangement

    SQL = SQL & " Order By '" & MemberList & "'"

    Please help !
     
    Peter, Nov 14, 2005
    #1
    1. Advertising

  2. "Peter" <> wrote in message
    news:%...
    >I put the result into an array getting from another db, the arrangement
    >should not be changed,
    >
    > MemberList = "'007910', '006853', '007965'"
    >
    > SQL = "Select MemberID, name From Member"
    > SQL = SQL & " Where MemberID In (" & MemberList & ")"
    >
    > Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
    >
    > While not rs.eof
    > ....... Wend
    >
    > But the order changes to 006853 first, then 007910....007965
    >
    > Is it possible to use "order by" to keep the arrangement
    >
    > SQL = SQL & " Order By '" & MemberList & "'"
    >
    > Please help !
    >


    SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
     
    Chris Hohmann, Nov 14, 2005
    #2
    1. Advertising

  3. Peter

    Peter Guest

    Thanks for yr help, but when I apply yr syntax,

    SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"

    the result get error, however I change to below

    MemberList = "'007910', '006853', '007965'"

    SQL = "Select MemberID, name From Member"
    SQL = SQL & " Where MemberID In (" & MemberList & ")"
    SQL = SQL & " ORDER BY INSTR(" & MemberList & ")"
    Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)

    While not rs.eof
    007965
    007910
    006853
    ....... Wend

    The results come out, but it seems no sorting at all.

    I also try all the following :

    SQL = SQL & " ORDER BY INSTR('" & MemberList & "')"
    SQL = SQL & " ORDER BY INSTR(" & MemberList & ",MemberID)"
    SQL = SQL & " ORDER BY INSTR('" & MemberList & "',MemberID)"
    SQL = SQL & " ORDER BY INSTR('" & MemberList & "','MemberID')"

    I am using Access2003 to create the db, and the data of MemberID is text
    format.

    What wrong to me? Please help !

    Alan Yuan
    Thanks very much



    "Chris Hohmann" <> ¼¶¼g©ó¶l¥ó·s»D:%...
    >
    > "Peter" <> wrote in message
    > news:%...
    >>I put the result into an array getting from another db, the arrangement
    >>should not be changed,
    >>
    >> MemberList = "'007910', '006853', '007965'"
    >>
    >> SQL = "Select MemberID, name From Member"
    >> SQL = SQL & " Where MemberID In (" & MemberList & ")"
    >>
    >> Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
    >>
    >> While not rs.eof
    >> ....... Wend
    >>
    >> But the order changes to 006853 first, then 007910....007965
    >>
    >> Is it possible to use "order by" to keep the arrangement
    >>
    >> SQL = SQL & " Order By '" & MemberList & "'"
    >>
    >> Please help !
    >>

    >
    > SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
    >
     
    Peter, Nov 15, 2005
    #3
  4. Peter

    Mark Schupp Guest

    What is the data type of the memberid column?

    --
    --Mark Schupp


    "Peter" <> wrote in message
    news:...
    > Thanks for yr help, but when I apply yr syntax,
    >
    > SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
    >
    > the result get error, however I change to below
    >
    > MemberList = "'007910', '006853', '007965'"
    >
    > SQL = "Select MemberID, name From Member"
    > SQL = SQL & " Where MemberID In (" & MemberList & ")"
    > SQL = SQL & " ORDER BY INSTR(" & MemberList & ")"
    > Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
    >
    > While not rs.eof
    > 007965
    > 007910
    > 006853
    > ....... Wend
    >
    > The results come out, but it seems no sorting at all.
    >
    > I also try all the following :
    >
    > SQL = SQL & " ORDER BY INSTR('" & MemberList & "')"
    > SQL = SQL & " ORDER BY INSTR(" & MemberList & ",MemberID)"
    > SQL = SQL & " ORDER BY INSTR('" & MemberList & "',MemberID)"
    > SQL = SQL & " ORDER BY INSTR('" & MemberList & "','MemberID')"
    >
    > I am using Access2003 to create the db, and the data of MemberID is text
    > format.
    >
    > What wrong to me? Please help !
    >
    > Alan Yuan
    > Thanks very much
    >
    >
    >
    > "Chris Hohmann" <>
    > ¼¶¼g©ó¶l¥ó·s»D:%...
    >>
    >> "Peter" <> wrote in message
    >> news:%...
    >>>I put the result into an array getting from another db, the arrangement
    >>>should not be changed,
    >>>
    >>> MemberList = "'007910', '006853', '007965'"
    >>>
    >>> SQL = "Select MemberID, name From Member"
    >>> SQL = SQL & " Where MemberID In (" & MemberList & ")"
    >>>
    >>> Set rs = GetMdbStaticRecordset( "Member.mdb", SQL)
    >>>
    >>> While not rs.eof
    >>> ....... Wend
    >>>
    >>> But the order changes to 006853 first, then 007910....007965
    >>>
    >>> Is it possible to use "order by" to keep the arrangement
    >>>
    >>> SQL = SQL & " Order By '" & MemberList & "'"
    >>>
    >>> Please help !
    >>>

    >>
    >> SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"
    >>

    >
    >
     
    Mark Schupp, Nov 15, 2005
    #4
  5. "Peter" <> wrote in message
    news:...
    > Thanks for yr help, but when I apply yr syntax,
    >
    > SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"


    Try this instead:

    SQL = SQL & "ORDER BY INSTR(""" & MemberList & """,MemberID)"
     
    Chris Hohmann, Nov 15, 2005
    #5
  6. Peter

    Peter Guest

    It works, thanks for yr help very very much.

    "Chris Hohmann" <> ¼¶¼g©ó¶l¥ó·s»D:...
    > "Peter" <> wrote in message
    > news:...
    >> Thanks for yr help, but when I apply yr syntax,
    >>
    >> SQL = SQL & "ORDER BY INSTR('" & MemberList & "',MemberID)"

    >
    > Try this instead:
    >
    > SQL = SQL & "ORDER BY INSTR(""" & MemberList & """,MemberID)"
    >
     
    Peter, Nov 16, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Soren Kuula
    Replies:
    2
    Views:
    501
    Soren Kuula
    Feb 1, 2004
  2. Mara Guida

    const and array of array (of array ...)

    Mara Guida, Sep 2, 2009, in forum: C Programming
    Replies:
    3
    Views:
    496
    David RF
    Sep 3, 2009
  3. cspoh
    Replies:
    0
    Views:
    250
    cspoh
    Jul 31, 2003
  4. Stephan Kämper
    Replies:
    2
    Views:
    237
    Stephan Kämper
    Jan 18, 2004
  5. Tuxedo
    Replies:
    8
    Views:
    133
    Tuxedo
    May 7, 2007
Loading...

Share This Page