Controlling the sort order for records retrieved from an Access database

Brit

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

 
<p><strong>List all CATI members by category:</strong></p>
 
<form BOTID="0" METHOD="POST" ACTION="category3.asp">
 
<select type="text" name=category size=4>

<option>Corporate</option>

<option>Institutional</option>

<option>Regular</option>

<option>Student</option>

</select>

<p>
 
  <input type="submit" value="Search">&nbsp;&nbsp;&nbsp;<input type="reset" value="Reset">
 
<font size="-1" color="#000000">
 
<!--webbot bot="SaveAsASP" CLIENTSIDE SuggestedExt="asp" PREVIEW=" " -->
 
</form>
 
<font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000000">
 
<p><strong>Note:</strong> When search results are displayed, click on a last name for more information about that translator or interpreter.

To return to the search results, use the <strong>Back</strong> button on your browser.</p>
 
<!--webbot bot="DatabaseRegionStart" startspan

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"

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"

s-dataconnection="cati" b-tableformat="FALSE" b-menuformat="FALSE"

s-menuchoice="Last_Name" s-menuvalue="Last_Name" b-tableborder="TRUE"

b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"

b-listseparator="TRUE" i-ListFormat="0" b-makeform="TRUE"

s-recordsource="Members"

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"

s-criteria="[category] CNT {category} +" s-order="[Last_name]"

s-sql="SELECT * FROM Members WHERE (category LIKE '%::category::%')"

b-procedure="FALSE" clientside SuggestedExt="asp"

s-DefaultFields="category=x" s-NoRecordsFound=""

i-MaxRecords="256" i-GroupSize="15" BOTID="0" u-dblib="_fpclass/fpdblib.inc"

u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="BODY"

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;"

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;"

b-WasTableFormat="FALSE" --><!--#include file="_fpclass/fpdblib.inc"-->

<%

fp_sQry="SELECT * FROM Members WHERE (category LIKE '%::category::%')"

fp_sDefault="category=x"

fp_sNoRecords=""

fp_sDataConn="cati"

fp_iMaxRecords=256

fp_iCommandType=1

fp_iPageSize=15

fp_fTableFormat=False

fp_fMenuFormat=False

fp_sMenuChoice="Last_Name"

fp_sMenuValue="Last_Name"

fp_iDisplayCols=20

fp_fCustomQuery=False

BOTID=0

fp_iRegion=BOTID

%>

<!--#include file="_fpclass/fpdbrgn1.inc"-->

<!--webbot bot="DatabaseRegionStart" i-CheckSum="5319" endspan -->
 
<!--------------------------- identifier ------------------------------------>
 
<p><a href="identifierV7.asp?ID=<!--webbot bot="DatabaseResultColumn" startspan

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"

s-column="ID" b-tableformat="FALSE" b-hasHTML="FALSE" clientside

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;"

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")%>
 
<!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->">
 
<!--------------------------- last name ------------------------------------>
 
<font face="Verdana, Arial, Helvetica, sans-serif" size="-1" color="#000099">
 
<p><strong><!--webbot bot="DatabaseResultColumn" startspan

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"
 
s-column="Last_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside

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;"

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>,
 
<!--webbot bot="DatabaseResultColumn" i-CheckSum="17991" endspan -->
 
<!-------------------------- first name ------------------------------>
 
<strong><!--webbot bot="DatabaseResultColumn" startspan

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"
 
s-column="First_Name" b-tableformat="FALSE" b-hasHTML="FALSE" clientside

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;"

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>
 
<!--webbot bot="DatabaseResultColumn" i-CheckSum="27645" endspan -->
 
<!--webbot bot="DatabaseRegionEnd" startspan b-tableformat="FALSE"

b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="15" clientside

tag="BODY"

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;"

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"-->

<!--webbot bot="DatabaseRegionEnd" i-CheckSum="62730" endspan -->

DrBunchman

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

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

Controlling the sort order for records retrieved from an Access database

re: Controlling the sort order for records retrieved from an Access database

/bytes/development

/bytes/it

/bytes/about