danniborg
asked on
Sorting query result into pages by alphabetic characters
Hi, there
I have an asp page that displays result from a query. The page "boatlist.asp" shows the result as a list of linkes to
another page "boat.asp" that displays detailed information about the boat. The linkes in "boatlist.asp" are divided into
pages, each containing 27 elements. At the bottom of the page there is a list of linkes to each result page as (linked)
pagenumbers.
Here is what I would like to have. In stead of dividing the result into pages with fixed number of elements, I would like to
divide it by alphabetic characters i.e. group the elements by the first character of the name. (All the names starting with
the letter A on a single page, B on the next etc. etc.)
I'm a newbie to asp and web development altogether, so you'll forgive me if this code is sloppy... but it works so...
Any suggestions?
Thanks
Here is the code for my "boatlist.asp" page:
<%@ Language=VBScript %>
<HTML>
<BODY>
<%
Dim conn, rst, currPage, row, counter, strSQL
Set conn = Server.CreateObject("ADODB
conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\db\boats"
strSQL = "SELECT boatId, Name, regNumb FROM Boats ORDER BY Name"
Set rst = Server.CreateObject("ADODB
rst.CursorType=3
rst.PageSize=27
rst.Open strSQL, conn
If Request.QueryString("CurrP
currPage=1
Else
currPage=Request.QueryStri
End If
rst.AbsolutePage=currPage
row=0
pageCount= rst.PageCount
%>
<I>Page <%=currPage%> of <%=pageCount%> </I>
<%
Do While Not rst.EOF And row < rst.PageSize
%>
<TABLE>
<TR>
<TD><A HREF="boat.asp?FnrNafns=<%
<%=rst.Fields(1).value%> </A></TD>
<TD><%=rst.Fields(2).value
<%
rst.MoveNext
row = row +1
%>
</TR>
</TABLE>
<%
Loop
Response.Write "<B>Pages: </B>"
For counter = 1 To rst.PageCount
Response.Write "<A HREF=""boatlist.asp?currPa
Response.Write counter & "</A>"
Response.Write Chr(32)
Next
rst.close
Set rst = nothing
conn.close
Set conn = nothing
%>
</BODY>
</HTML>
I have an asp page that displays result from a query. The page "boatlist.asp" shows the result as a list of linkes to
another page "boat.asp" that displays detailed information about the boat. The linkes in "boatlist.asp" are divided into
pages, each containing 27 elements. At the bottom of the page there is a list of linkes to each result page as (linked)
pagenumbers.
Here is what I would like to have. In stead of dividing the result into pages with fixed number of elements, I would like to
divide it by alphabetic characters i.e. group the elements by the first character of the name. (All the names starting with
the letter A on a single page, B on the next etc. etc.)
I'm a newbie to asp and web development altogether, so you'll forgive me if this code is sloppy... but it works so...
Any suggestions?
Thanks
Here is the code for my "boatlist.asp" page:
<%@ Language=VBScript %>
<HTML>
<BODY>
<%
Dim conn, rst, currPage, row, counter, strSQL
Set conn = Server.CreateObject("ADODB
conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\db\boats"
strSQL = "SELECT boatId, Name, regNumb FROM Boats ORDER BY Name"
Set rst = Server.CreateObject("ADODB
rst.CursorType=3
rst.PageSize=27
rst.Open strSQL, conn
If Request.QueryString("CurrP
currPage=1
Else
currPage=Request.QueryStri
End If
rst.AbsolutePage=currPage
row=0
pageCount= rst.PageCount
%>
<I>Page <%=currPage%> of <%=pageCount%> </I>
<%
Do While Not rst.EOF And row < rst.PageSize
%>
<TABLE>
<TR>
<TD><A HREF="boat.asp?FnrNafns=<%
<%=rst.Fields(1).value%> </A></TD>
<TD><%=rst.Fields(2).value
<%
rst.MoveNext
row = row +1
%>
</TR>
</TABLE>
<%
Loop
Response.Write "<B>Pages: </B>"
For counter = 1 To rst.PageCount
Response.Write "<A HREF=""boatlist.asp?currPa
Response.Write counter & "</A>"
Response.Write Chr(32)
Next
rst.close
Set rst = nothing
conn.close
Set conn = nothing
%>
</BODY>
</HTML>
or
SELECT boatId, Name, regNumb FROM Boats WHERE Name like 'A%' ORDER BY Name"
SELECT boatId, Name, regNumb FROM Boats WHERE Name like 'A%' ORDER BY Name"
ASKER
Thanks for your answers sybe and dxz2
But it seems to me that by doing this I will need a SQL statement and a recordset for each letter in the alphabet.
Isn't there a more economic way of doing this?
As you can see in my code I'm using a for-loop to display the links to the result pages. The counter variable gives the page number but I would like it to be a character.
Any thougts on that?
Even though I'm a novice to asp, I have some experiance in programming so don't be afraid to take your suggestions to the next level :)
But it seems to me that by doing this I will need a SQL statement and a recordset for each letter in the alphabet.
Isn't there a more economic way of doing this?
As you can see in my code I'm using a for-loop to display the links to the result pages. The counter variable gives the page number but I would like it to be a character.
Any thougts on that?
Even though I'm a novice to asp, I have some experiance in programming so don't be afraid to take your suggestions to the next level :)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I will try to explain this theorticaly
Your sql looks like this: strSQL = "SELECT boatId, Name, regNumb FROM Boats ORDER BY Name", the results will be displayed by alphabetical order.. so words that starts with A will be at first then B and so on...
what you have to do, is to check when the letter changes. first make a check on the recordset before write the results, check how many times each letter appears so u can set PageSize, currPage and pageCount... now you also now the index of each letter... so it would be easy to get it from the recordset
Hope u got the idea, if not, I hope maybe later today I can provide an example... but this should get u going
-Dirar
Your sql looks like this: strSQL = "SELECT boatId, Name, regNumb FROM Boats ORDER BY Name", the results will be displayed by alphabetical order.. so words that starts with A will be at first then B and so on...
what you have to do, is to check when the letter changes. first make a check on the recordset before write the results, check how many times each letter appears so u can set PageSize, currPage and pageCount... now you also now the index of each letter... so it would be easy to get it from the recordset
Hope u got the idea, if not, I hope maybe later today I can provide an example... but this should get u going
-Dirar
ASKER
I have been trying to figure this out but it doesn't work.
How do I check for changes of letter and how many times each letter appears? Hope you can give me an example :)
Thanks
How do I check for changes of letter and how many times each letter appears? Hope you can give me an example :)
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks very much guys
I've got it to work now...
I mainly used the example in the last comment from dxz2 but then I also used the array aproach from ThinkPaper to get pageCount because I wanted also to display what page the user is viewing and how many pages there are (i.e. Page <%=currPage%> of <%=PageCount%>).
Your answers where good and added alot to my knowledge in asp!
I've got it to work now...
I mainly used the example in the last comment from dxz2 but then I also used the array aproach from ThinkPaper to get pageCount because I wanted also to display what page the user is viewing and how many pages there are (i.e. Page <%=currPage%> of <%=PageCount%>).
Your answers where good and added alot to my knowledge in asp!
I am glad about that... :)
"SELECT boatId, Name, regNumb FROM Boats WHERE UCASE(Left(Name,1)) = 'A' ORDER BY Name"