Alphabetical Listing in ASP

blondebier · Jul 24, 2003

I'm trying to break up a huge list of data from a database into alphabetical segments.

Ideally i'd like to display A B C ... Z at the top of a page and when the user clicks a letter it filters the data alphabetically according to the letter selected.

Are there any articles that would illustrate this?

Thanks for your comments,

Francis.

TruthInSatire · Jul 24, 2003

its easy,
you have your links on top.
a b c d e ...
each has a query string in the url. <a href = "orderpage.asp?letter=a>a</a>

the sql in the order page will look like

sql = select * from table where myFld like '"&request.querystring("letter&quot

&"%' order by myFld"

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
- Quote by Douglas Adams

blondebier · Jul 25, 2003

Basically the application displays a catalogue of roses.

I've created a loop that generates the index list from ascii characters. My problem is getting it to display the list it's new indexed form. ie a List for when A is selected, List for when B is selected and so on...

My code so far is:

<%@ LANGUAGE = "VBScript" %>




<%
catcode=request.querystring("catcode&quot

set rscat=db.execute("select * from categories where catcode=" & catcode)

if rscat.eof then
response.redirect("error.asp?Unable to locate category!&quot

end if

'Selects the names and codes of all the roses in that category
set rscatlist=db.execute("select name,roseid from roses where catcode=" & catcode & " order by name&quot

%>

<html>

<head>
<title>My Rose Catalogue</title>

<body bgcolor="<%= bgcolor %>" topmargin="0" leftmargin="0" marginwidth="0" marginheight="0" text="<%= text %>" alink="<%= COLlight %>" link="<%= COLlight %>" vlink="<%= vlink %>">

<%
header

categorymenu
%>
<table>
<td valign=top align=right>
"><%= rscat("catname&quot

%> Roses
<%
for num = 97 to 122
lett = "&#" & num & ";"
response.write "<a href=category.asp?catcode=" & catcode & " &letter=" & lett & "> " & lett & "</a>"

next%>
 
<%

'Displays a list of the other roses in the category
if not rscatlist.eof then
rscatlist.movefirst
while not rscatlist.eof
%>
">
<a href="rose.asp?roseid=<%= rscatlist("roseid&quot

%>"><%=rscatlist("name&quot

%></a>
 
<%
rscatlist.movenext
wend
end if
%>
</td>
<%
ci=rscat("catimage&quot

if ci<>"" then
%>
<td width=180 align="left">
<%=rscat("catimage&quot

%>
<%
else
%>
<td width=300 align="center" height=250 valign=center>

<%=lcase(rscat("catname&quot

) %> 

<%=lcase(rscat("catdes&quot

) %>
<%
end if
%>

</td>
</table>

<% footer %>
</body>
</html>
<%
db.close
set db=nothing
%>

At the moment the output is something like:

CatName a b c d e f g h i j k l m n o p q r s t u v w x y z

Then the complete list
.
.
.
.
.
.
.
.
.
.

Thanks for your help.

shaddow · Jul 25, 2003

What database you using SQL or Acces?

________
George, M

blondebier · Jul 25, 2003

Access.

shaddow · Jul 25, 2003

Add this
...
catcode=request.querystring("catcode&quot

letter=request.querystring("letter&quot

...
'Selects the names and codes of all the roses in that category
set rscatlist=db.execute("select name,roseid from roses where catcode=" & catcode & " and name like '"&letter&"%' order by name&quot

This should fix it

________
George, M

blondebier · Jul 25, 2003

Would I remove this section of the code as well?

<%
'Displays a list of the other roses in the category
if not rscatlist.eof then
rscatlist.movefirst
while not rscatlist.eof
%>
">
<a href="rose.asp?roseid=<%= rscatlist("roseid&quot

%>"><%=rscatlist("name&quot

%></a>
 
<%
rscatlist.movenext
wend
end if
%>

shaddow · Jul 25, 2003

No only modify those lines i showed you, it should work with the code you have now

________
George, M

blondebier · Jul 25, 2003

I modified those two lines but it still lists the entire list from the "name" field in my database.

Thanks for your help.

shaddow · Jul 25, 2003

Try to
response.write("select name,roseid from roses where catcode=" & catcode & " and name like '"&letter&"%' order by name&quot

and see if the querry is ok

________
George, M

blondebier · Jul 25, 2003

I get this output:

Bush Roses Aselect name,roseid from roses where catcode=1 and name like '%' order by name Bselect name,roseid from roses where catcode=1 and name like '%' order by name Cselect name,roseid from roses where catcode=1 and name like '%' order by name .........and so on.

shaddow · Jul 25, 2003

yes that's ok, but like '%' it means that letter is empty instead of having a letter value.
do you have these lines?

letter=request.querystring("letter&quot

response.write "<a href=category.asp?catcode=" & catcode & " &letter=" & lett & "> " & lett & "</a>"

set rscatlist=db.execute("select name,roseid from roses where catcode=" & catcode & " and name like '"&letter&"%' order by name&quot

?

________
George, M

blondebier · Jul 25, 2003

This is what it looks like now:

<%@ LANGUAGE = "VBScript" %>




<%
catcode=request.querystring("catcode&quot

letter=request.querystring("letter&quot

set rscat=db.execute("select * from categories where catcode=" & catcode)

if rscat.eof then
response.redirect("error.asp?Unable to locate category!&quot

end if

'Selects the names and codes of all products in that category
set rscatlist=db.execute("select name,roseid from roses where catcode=" & catcode & " and name like '"&letter&"%' order by name&quot

%>

<html>

<head>
<title>My Rose Catalogue</title>

<body bgcolor="<%= bgcolor %>" topmargin="0" leftmargin="0" marginwidth="0" marginheight="0" text="<%= text %>" alink="<%= COLlight %>" link="<%= COLlight %>" vlink="<%= vlink %>">

<%
header

categorymenu
%>
<table>
<td valign=top align=right>
"><%= rscat("catname&quot

%> Roses
<%
for num = 65 to 90
lett = "&#" & num & ";"
response.write "<a href=category.asp?catcode=" & catcode & " &letter=" & lett & "> " & lett & "</a>"
response.write("select name,roseid from roses where catcode=" & catcode & " and name like '"&letter&"%' order by name&quot

next%>
 
<%

'Displays a list of the other roses in the category
if not rscatlist.eof then
rscatlist.movefirst
while not rscatlist.eof
%>
">
<a href="rose.asp?roseid=<%= rscatlist("roseid&quot

%>"><%=rscatlist("name&quot

%></a>
 
<%
rscatlist.movenext
wend
end if
%>
</td>
<%
ci=rscat("catimage&quot

if ci<>"" then
%>
<td width=180 align="left">
<%=rscat("catimage&quot

%>
<%
else
%>
<td width=300 align="center" height=250 valign=center>

<%=lcase(rscat("catname&quot

) %> 

<%=lcase(rscat("catdes&quot

) %>
<%
end if
%>

</td>
</table>

<% footer %>

</body>
</html>

<%
db.close
set db=nothing
%>

shaddow · Jul 28, 2003

And does it work? the query it seems ok unless the letter as i see it's empty so will return all the fields available.

________
George, M

blondebier · Jul 28, 2003

No it doesn't work. It's very frustrating.

I think either; it doesn't wait for a letter to be passed and returns all the values in the database. Or it loops in some way and gets all the letters passed to it and returns all of the values?

It's most puzzling?

Nevermoor · Jul 28, 2003

Put the following function in your global header (or the top of your page)
Function view()
echo "#- debug message -#" & " "
echo "#- debug queryString -#" & " "
For Each Name In Request.QueryString
echo Name & ":" & GetVar(Name) & " "
Next
echo "#- debug form -#" & " "
For Each Name In Request.Form
echo Name & ":" & GetVar(Name) & " "
Next
echo "#- debug end -#" & " "
Response.end
End Function

then call view() at the top of this page. It is a useful little function that shows you all the inputs your page is recieving. If it isn't recieving a letter, the query above won't limit. If it is then we have more work to do.

blondebier · Jul 29, 2003

I've changed my code and removed that loop. It looks like this, but now I get this syntax error.

This page cannot be displayed.

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'catcode='.
/public/category.asp, line 10

<%@ LANGUAGE = "VBScript" %>




<%
catcode=request.querystring("catcode&quot

letter=request.querystring("letter&quot

set rscat=db.execute("select * from categories where catcode=" & catcode)

if rscat.eof then
response.redirect("error.asp?Unable to locate category!&quot

end if

'Selects the names and codes of all products in that category
set rscatlist=db.execute("select name,roseid from roses where catcode=" & catcode & " and name like '"&letter&"%' order by name&quot

%>

<html>

<head>
<title>My Rose Catalogue</title>

<body bgcolor="<%= bgcolor %>" topmargin="0" leftmargin="0" marginwidth="0" marginheight="0" text="<%= text %>" alink="<%= COLlight %>" link="<%= COLlight %>" vlink="<%= vlink %>">

<%
header

categorymenu
%>

<table>
<td valign=top align=right>
"><%= rscat("catname&quot

%> Roses

 <a href="category.asp?catcode= " & catcode & " &letter=A">A</a>
<a href="category.asp?catcode= " & catcode & " &letter=B">B</a>
<a href="category.asp?catcode= " & catcode & " &letter=C">C</a>
<a href="category.asp?catcode= " & catcode & " &letter=D">D</a>
<a href="category.asp?catcode= " & catcode & " &letter=E">E</a>
<a href="category.asp?catcode= " & catcode & " &letter=F">F</a>
<a href="category.asp?catcode= " & catcode & " &letter=G">G</a>
<a href="category.asp?catcode= " & catcode & " &letter=H">H</a>
<a href="category.asp?catcode= " & catcode & " &letter=I">I</a>
<a href="category.asp?catcode= " & catcode & " &letter=J">J</a>
<a href="category.asp?catcode= " & catcode & " &letter=K">K</a>
<a href="category.asp?catcode= " & catcode & " &letter=L">L</a>
<a href="category.asp?catcode= " & catcode & " &letter=M">M</a>
<a href="category.asp?catcode= " & catcode & " &letter=N">N</a>
<a href="category.asp?catcode= " & catcode & " &letter=O">O</a>
<a href="category.asp?catcode= " & catcode & " &letter=P">P</a>
<a href="category.asp?catcode= " & catcode & " &letter=Q">Q</a>
<a href="category.asp?catcode= " & catcode & " &letter=R">R</a>
<a href="category.asp?catcode= " & catcode & " &letter=S">S</a>
<a href="category.asp?catcode= " & catcode & " &letter=T">T</a>
<a href="category.asp?catcode= " & catcode & " &letter=U">U</a>
<a href="category.asp?catcode= " & catcode & " &letter=V">V</a>
<a href="category.asp?catcode= " & catcode & " &letter=W">W</a>
<a href="category.asp?catcode= " & catcode & " &letter=X">X</a>
<a href="category.asp?catcode= " & catcode & " &letter=Y">Y</a>
<a href="category.asp?catcode= " & catcode & " &letter=Z">Z</a> 
 

<%

'Displays a list of the other roses in the category
if not rscatlist.eof then
rscatlist.movefirst
while not rscatlist.eof
%>
">
<a href="rose.asp?roseid=<%= rscatlist("roseid&quot

%>"><%=rscatlist("name&quot

%></a>
 
<%
rscatlist.movenext
wend
end if
%>
</td>
<%
ci=rscat("catimage&quot

if ci<>"" then
%>
<td width=180 align="left">
<%=rscat("catimage&quot

%>
<%
else
%>
<td width=300 align="center" height=250 valign=center>

<%=lcase(rscat("catname&quot

) %> 

<%=lcase(rscat("catdes&quot

) %>
<%
end if
%>

</td>
</table>

<% footer %>

</body>
</html>

<%
db.close
set db=nothing
%>

shaddow · Jul 29, 2003

as you may see you need to pass catcode and letter as querry string, since you dont pass any catcode anywere it wont work.

________
George, M

blondebier · Jul 30, 2003

I found the problem.

lett = "&#" & num & ";"

Should've been written as:

lett = "&#0" & num & ";"

Little things hey?

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Alphabetical Listing in ASP

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Programmer

Similar threads

Log in

Part and Inventory Search

Sponsor