Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Order by INSTR, urgent !

Reply
Thread Tools

Order by INSTR, urgent !

 
 
Mary
Guest
Posts: n/a
 
      01-22-2006
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 !


 
Reply With Quote
 
 
 
 
Bob Lehmann
Guest
Posts: n/a
 
      01-22-2006
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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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 !
>
>



 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      01-23-2006
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"


 
Reply With Quote
 
Mary
Guest
Posts: n/a
 
      01-23-2006
Dear Bob :
It work ! You solve my problem, thanks a lot.....


"Bob Lehmann" <(E-Mail Removed)> glsD:(E-Mail Removed) bl...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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 !
>>
>>

>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
is order urgent doubt new to c C Programming 51 06-10-2008 08:36 PM
If you get an order # does it mean the order is accepted? =?Utf-8?B?U3RldmUxMDc3?= Windows 64bit 3 05-12-2005 11:46 PM
Traversion order cf. output order in XSL Soren Kuula XML 2 02-01-2004 09:10 AM
In which order are files looked for when loaded/requierd - and what'sthe order of suffixes? Stephan Kmper Ruby 2 01-18-2004 02:07 PM
How to Display DropDownList with preserved order (custom order) cspoh ASP .Net Web Controls 0 07-31-2003 09:19 AM



Advertisments