By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
458,125 Members | 1,411 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 458,125 IT Pros & Developers. It's quick & easy.

Controlling the sort order for records retrieved from an Access database

P: 1
I have an ASP file that retrieves names from an Access database for 4 different categories of membership, which the visitor to the page selects (corporate, institutional, regular, or student). The DNS name is "cati", the names are specified in the "Last_names" field, and the categories are in the "categories" field.

l want the results sorted in alphabetic order by last name. However, the results appear to be in a totally random, non-alphabetic order, which is exactly the same every time I search. I do not understand what is causing them to be in this random order. Apart from that it works just fine.

Here is the code. The link to "identierV7.asp" simply displays more information when you click on one of the names in the search results. I would really appreciate any help!

Expand|Select|Wrap|Line Numbers
  1. <p><strong>List all CATI members by category:</strong></p>
  2.  
  3.  
  4. <form BOTID="0" METHOD="POST" ACTION="category3.asp">
  5.  
  6. <select type="text" name=category size=4>
  7. <option>Corporate</option>
  8. <option>Institutional</option>
  9. <option>Regular</option>
  10. <option>Student</option>
  11. </select>
  12. <p>
  13.  
  14.   <input type="submit" value="Search">&nbsp;&nbsp;&nbsp;<input type="reset" value="Reset">
  15.  
  16. <font size="-1" color="#000000">
  17.  
  18. <!--webbot bot="SaveAsASP" CLIENTSIDE SuggestedExt="asp" PREVIEW=" " -->
  19.  
  20. </form>
  21.  
  22. <font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000000">
  23.  
  24. <p><strong>Note:</strong> When search results are displayed, click on a last name for more information about that translator or interpreter.
  25. To return to the search results, use the <strong>Back</strong> button on your browser.</p>
  26.  
  27. <!--webbot bot="DatabaseRegionStart" startspan
  28. s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"
  29. s-columntypes="3,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,202,203,202,202,202,202,11,202,11,202,202,202"
  30. s-dataconnection="cati" b-tableformat="FALSE" b-menuformat="FALSE"
  31. s-menuchoice="Last_Name" s-menuvalue="Last_Name" b-tableborder="TRUE"
  32. b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
  33. b-listseparator="TRUE" i-ListFormat="0" b-makeform="TRUE"
  34. s-recordsource="Members"
  35. s-displaycolumns="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,Biography"
  36. s-criteria="[category] CNT {category} +" s-order="[Last_name]"
  37. s-sql="SELECT * FROM Members WHERE (category LIKE '%::category::%')"
  38. b-procedure="FALSE" clientside SuggestedExt="asp"
  39. s-DefaultFields="category=x" s-NoRecordsFound=""
  40. i-MaxRecords="256" i-GroupSize="15" BOTID="0" u-dblib="_fpclass/fpdblib.inc"
  41. u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY"
  42. local_preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;Database Results regions will not preview unless this page is fetched from a Web server using a web browser. The section of the page from here to the end of the Database Results region will repeat once for each record returned by the query.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;"
  43. preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the start of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;"
  44. b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->
  45. <%
  46. fp_sQry="SELECT * FROM Members WHERE (category LIKE '%::category::%')"
  47. fp_sDefault="category=x"
  48. fp_sNoRecords=""
  49. fp_sDataConn="cati"
  50. fp_iMaxRecords=256
  51. fp_iCommandType=1
  52. fp_iPageSize=15
  53. fp_fTableFormat=False
  54. fp_fMenuFormat=False
  55. fp_sMenuChoice="Last_Name"
  56. fp_sMenuValue="Last_Name"
  57. fp_iDisplayCols=20
  58. fp_fCustomQuery=False
  59. BOTID=0
  60. fp_iRegion=BOTID
  61. %>
  62. <!--#include file="_fpclass/fpdbrgn1.inc"-->
  63. <!--webbot bot="DatabaseRegionStart" i-CheckSum="5319" endspan -->
  64.  
  65. <!--------------------------- identifier ------------------------------------>
  66.  
  67. <p><a href="identifierV7.asp?ID=<!--webbot bot="DatabaseResultColumn" startspan
  68. s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"
  69. s-column="ID" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
  70. local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
  71. preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;ID&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"ID")%>
  72.  
  73. <!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->">
  74.  
  75. <!--------------------------- last name ------------------------------------>
  76.  
  77. <font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000099">
  78.  
  79. <p><strong><!--webbot bot="DatabaseResultColumn" startspan
  80. s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"
  81.  
  82. s-column="Last_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
  83. local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Last_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
  84. preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;Last_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"Last_Name")%></a></strong>,
  85.  
  86. <!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->
  87.  
  88. <!-------------------------- first name ------------------------------>
  89.  
  90. <strong><!--webbot bot="DatabaseResultColumn" startspan
  91. s-columnnames="ID,Last_Name,First_Name,Title,Address,Extra_Address,City,State,Zip,Phone,Alternate_Email,Fax,Email,Local_Group,Category,Expires,Native_language,origin,Working_languages,Ata_certification,other_affiliations,Preferred_Fields,pager/cellphone,web_page,new,change_detail,change,codes,Biography"
  92.  
  93. s-column="First_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside
  94. local_preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;First_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;"
  95. preview="&lt;font size=&quot;-1&quot;&gt;&amp;lt;&amp;lt;&lt;/font&gt;First_Name&lt;font size=&quot;-1&quot;&gt;&amp;gt;&amp;gt;&lt;/font&gt;" --><%=FP_FieldVal(fp_rs,"First_Name")%></strong></font>
  96.  
  97. <!--webbot bot="DatabaseResultColumn" i-CheckSum="27645" endspan -->
  98.  
  99. <!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="FALSE"
  100. b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="15" clientside
  101. tag="BODY"
  102. local_preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;center&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;End of Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;TR&gt;&lt;TD ALIGN=LEFT VALIGN=MIDDLE&gt;&lt;FORM&gt;&lt;NOBR&gt;&lt;INPUT TYPE=Button VALUE=&quot;  |&lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;   &lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;   &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;|  &quot;&gt;  [1/5]&lt;/NOBR&gt;&lt;/FORM&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;"
  103. preview="&lt;table border=0 width=&quot;100%&quot;&gt;&lt;tr&gt;&lt;td bgcolor=&quot;#FFFF00&quot; align=&quot;left&quot;&gt;&lt;font color=&quot;#000000&quot;&gt;This is the end of a Database Results region.&lt;/font&gt;&lt;/td&gt;&lt;/tr&gt;&lt;TR&gt;&lt;TD ALIGN=LEFT VALIGN=MIDDLE&gt;&lt;NOBR&gt;&lt;INPUT TYPE=Button VALUE=&quot;  |&lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;   &lt;  &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;   &quot;&gt;&lt;INPUT TYPE=Button VALUE=&quot;  &gt;|  &quot;&gt;  [1/5]&lt;/NOBR&gt;&lt;BR&gt;&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;" --><!--#include file="_fpclass/fpdbrgn2.inc"-->
  104. <!--webbot bot="DatabaseRegionEnd" i-CheckSum="62730" endspan -->
Mar 25 '08 #1
Share this Question
Share on Google+
1 Reply


DrBunchman
Expert 100+
P: 979
Hi Brit, for some reason your post isn't appearing on the forum clearly but in general when you are retrieving data from a database you can add an ORDER BY clause to your sql string. Like this:

Expand|Select|Wrap|Line Numbers
  1. SELECT FirstName, LastName FROM Table1 ORDER BY LastName ASC
Where ASC stands for ascending (you could use DESC for descending).

Hope this helps,

Dr B
Mar 26 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.