ASP - Order by INSTR, urgent !

Author

Order by INSTR, urgent !

Mary

2006-01-22, 6:57 pm

MemberID VID
1002 1001
1003 1002
1004 1003
1005 1003
1007 1001
1012 1002

<%
MemberList = "1001, 1003, 1002"
' The arrangement should not be changed....., the memberID and VID is number
format

SQL = "Select * From Member Where VID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID)"
Set rs = GetMdbRecordset( "Member.mdb", SQL)

While not rs.eof
A = rs("MemberID")
Response.write A
rs.movenext
Wend
A = rs("memberID")
Response.write A
%>

The results come out is
MemberID VID
1007 1001
1002 1001
1005 1003
1004 1003
1012 1002
1003 1002

The MemberID of each VID always sort from the bottom, for example, 1003 get
1005 first, and then 1004....., I try the following but no hope :
SQL = SQL & " ORDER BY INSTR("" & MemberList & "", VID)"
SQL = SQL & " ORDER BY INSTR("" & MemberList & "", "MemberID)"
Also I try to change the MemberID to text format, eg: '1001', '1002', ......
It still "Bottom First Top Last", the result I want is as below :

MemberID VID
1002 1001
1007 1001
1004 1003
1005 1003
1003 1002
1012 1002

Please Help me how to fix it, Thanks a lot !

Bob Lehmann

2006-01-22, 6:57 pm

Not sure I understand what you're asking, but maybe ...
SQL = "Select * From Member Where VID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID), MemberID"

Bob Lehmann

"Mary" <alan@neind.net> wrote in message
news:%23FHYgz4HGHA.2680@TK2MSFTNGP09.phx.gbl...
> MemberID VID
> 1002 1001
> 1003 1002
> 1004 1003
> 1005 1003
> 1007 1001
> 1012 1002
>
> <%
> MemberList = "1001, 1003, 1002"
> ' The arrangement should not be changed....., the memberID and VID is
number
> format
>
> SQL = "Select * From Member Where VID In (" & MemberList & ")"
> SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID)"
> Set rs = GetMdbRecordset( "Member.mdb", SQL)
>
> While not rs.eof
> A = rs("MemberID")
> Response.write A
> rs.movenext
> Wend
> A = rs("memberID")
> Response.write A
> %>
>
> The results come out is
> MemberID VID
> 1007 1001
> 1002 1001
> 1005 1003
> 1004 1003
> 1012 1002
> 1003 1002
>
> The MemberID of each VID always sort from the bottom, for example, 1003
get
> 1005 first, and then 1004....., I try the following but no hope :
> SQL = SQL & " ORDER BY INSTR("" & MemberList & "", VID)"
> SQL = SQL & " ORDER BY INSTR("" & MemberList & "", "MemberID)"
> Also I try to change the MemberID to text format, eg: '1001', '1002',
.......
> It still "Bottom First Top Last", the result I want is as below :
>
> MemberID VID
> 1002 1001
> 1007 1001
> 1004 1003
> 1005 1003
> 1003 1002
> 1012 1002
>
> Please Help me how to fix it, Thanks a lot !
>
>

Bob Barrows [MVP]

2006-01-23, 7:55 am

Mary wrote:
> MemberID VID
> 1002 1001
> 1003 1002
> 1004 1003
> 1005 1003
> 1007 1001
> 1012 1002
>
> <%
> MemberList = "1001, 1003, 1002"
> ' The arrangement should not be changed....., the memberID and VID is
> number format
>
> SQL = "Select * From Member Where VID In (" & MemberList & ")"
> SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID)"
> Set rs = GetMdbRecordset( "Member.mdb", SQL)
>

Bob has given you your immediate solution, but I just wanted to throw in
this caveat: add this record to the table and try your query using
MemberList="1001,1003,1002,100":

MemberId VID
1021 100

To fix this, you need to remove the spaces from MemberList, add commas to
the beginning and end of it in the InStr call, as well as putting commas
around VID:

MemberList = "1001, 1003, 1002, 100"
MemberList = Replace(MemberList," ","")
SQL = "Select * From Member Where VID In (" & MemberList & ")"
SQL = SQL & " ORDER BY INSTR(""" & "," & _
MemberList & ",', ',' & VID &','), MemberID"

If you Response.Write SQL, you should see

Select * From Member Where VID In (1001,1003,1002,100) ORDER BY
INSTR(',1001,1003,1002,100,', ',' & VID & ','), MemberID

You should be able to open your database in Access, create a new query in
Design view, switch to SQL View, paste the above sql statement in and run it
to see if it gives you the results you want.

You should always test your queries in Access before attempting to run them
from a client application such as ASP. This will allow you to catch
performance issues early in the process, as well as getting the benefit of
the more informative error messages that Access displays when you've done
somehthing wrong.

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Mary

2006-01-23, 6:55 pm

Dear Bob :
It work ! You solve my problem, thanks a lot.....

"Bob Lehmann" <nospam@dontbotherme.zzz> ¼¶¼g©ó¶l¥ó·s»D:ejrP6M6HGHA.3000@TK2MSFTNGP14.phx.gbl...
> Not sure I understand what you're asking, but maybe ...
> SQL = "Select * From Member Where VID In (" & MemberList & ")"
> SQL = SQL & " ORDER BY INSTR(""" & MemberList & """, VID), MemberID"
>
> Bob Lehmann
>
> "Mary" <alan@neind.net> wrote in message
> news:%23FHYgz4HGHA.2680@TK2MSFTNGP09.phx.gbl...
> number
> get
> ......
>
>

Sponsored Links