[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sorting query result into pages by alphabetic characters

Posted on 2006-04-27
10
Medium Priority
?
581 Views
Last Modified: 2008-03-06
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.Connection")
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.Recordset")
rst.CursorType=3
rst.PageSize=27
rst.Open strSQL, conn
If Request.QueryString("CurrPage")="" then
currPage=1
Else
currPage=Request.QueryString("currPage")
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(0).value%>">
 <%=rst.Fields(1).value%> </A></TD>
<TD><%=rst.Fields(2).value%></TD>
<%
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?currPage=" & counter & """>"
Response.Write counter & "</A>"
Response.Write Chr(32)
Next
rst.close
Set rst = nothing
conn.close
Set conn = nothing
%>

</BODY>
</HTML>
0
Comment
Question by:danniborg
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 28

Expert Comment

by:sybe
ID: 16551238
You can select the records by starting letter like this:

"SELECT boatId, Name, regNumb FROM Boats WHERE UCASE(Left(Name,1)) = 'A' ORDER BY Name"
0
 
LVL 6

Expert Comment

by:Dirar Abu Kteish
ID: 16551278
or       
SELECT boatId, Name, regNumb FROM Boats WHERE Name like  'A%' ORDER BY Name"
0
 

Author Comment

by:danniborg
ID: 16551547
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 :)
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 28

Assisted Solution

by:sybe
sybe earned 200 total points
ID: 16551712
> 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?

Why is that not economic? You display only part of the records on each page. There is no reason to get all records, if you display only a few. I would say that is quite economic.

You can do it all in a single page, where the querystring is decide the records displayed.

<%
sLetter = Request("letter")
sSQL = "SELECT boatId, Name, regNumb FROM Boats WHERE UCASE(Left(Name,1)) = '" & sLetter & "' ORDER BY Name"
' and continue with your page.
%>

Of course you should improve the above to prevent SQLInjection and check if the Querystring really is a single valid letter.
0
 
LVL 6

Expert Comment

by:Dirar Abu Kteish
ID: 16552062
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


0
 

Author Comment

by:danniborg
ID: 16554154
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
0
 
LVL 16

Assisted Solution

by:ThinkPaper
ThinkPaper earned 400 total points
ID: 16554967
sybe's method is MUCH better and probably more easier, You'd don't need to store each letter of the alphabet. You can grab it as you retrieve the data and store it in an array as a select sorting option

but if you still want to do it the other way, it would go something like this:
the problem with this is you're pulling more data than you need to everytime and it's slightly more complicated than just adding to the sql statement.

dim prevLetter
dim strLetters, arrayLetters
dim strBoatInfo

prevLetter = ""
strLetters = ""

Do While Not rst.EOF
      '--- if first instance of this letter, print---
    If Left(rst.Fields(1).value, 1) <> prevLetter Then
          strLetters = strLetters & Left(rst.Fields(1).value, 1) & ","    ' store all first Letter characters in string separated by ,
    End If
    prevLetter = Left(rst.Fields(1).value, 1)
  '---store boat info in string---
    strBoatInfo = strBoatInfo & rst.Fields(1).value & "," 'store all boat info
    rst.MoveNext
Loop

arrayLetters = split(strLetters, ",")     ' store all the letters in an array
arrayBoatInfo = split(strBoatinfo, ",")   ' store all the results in array

'now you know how many letters you have:    UBound(arrayLetters)
'and total records :                                       UBound(arrayBoatInfo)


For i = 0 to UBound(arrayLetters)
  'print the letters in a select box for sorting. This is your sort box (ie. Choose sorting option: A, B, F..)
  ' so when user does an onchange, call javascript to call itself with parameter sortLetter (i.e. myform.asp?sortingLetter=A)
Next

dim sortLetter
sortLetter = Request("sortingLetter")  ' suppose it's A

For i = 0 to UBound(arrayBoatInfo)
  If Left(arrayBoatInfo(i), 1) = sortLetter Then
        'print out list of boat info that starts with A
  End If
Next
0
 
LVL 6

Accepted Solution

by:
Dirar Abu Kteish earned 1400 total points
ID: 16554968
thinking about this another time, sybe  has mentioned something that I recomm to follow, which is when you want to show only records that starts with a certain letter, why should you get all records from DB... you should get those that you need only.
as it was mentioned before, u will use only one recordset and one sql, a querystring variable will be used that tells what is the current letter, u add this variable to your sql query as sybe said like this

Dim letter
letter = request.querystring("LETTER")
if letter = "" then letter = "A"

strSQL = "SELECT boatId, Name, regNumb FROM Boats WHERE Name like  UCASE('" & letter & "%') ORDER BY Name"

So now you only need:

Dim conn, rst, strSQL
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DRIVER=Microsoft Access Driver (*.mdb);DBQ=C:\db\boats"
strSQL = "SELECT boatId, Name, regNumb FROM Boats WHERE Name like  UCASE('" & letter & "%') ORDER BY Name"
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, conn

Do While Not rst.EOF
%>
<TABLE>
<TR>
<TD><A HREF="boat.asp?FnrNafns=<%=rst.Fields(0).value%>">
  <%=rst.Fields(1).value%> </A></TD>
<TD><%=rst.Fields(2).value%></TD>
<%
rst.MoveNext
%>
loop

now at the bottom or the top of the page display all Letters from A to Z like this

for i = 65 to 90
    Response.Write "<a href='thispage.asp?LETTER=" & Chr(i) & "'>" & Chr(i) & "</a> "
next

or if you want to display only letters the you have in the DB

strSQL = "SELECT DISTINCT left(Name, 1)  FROM Boats"
rst.Open strSQL, conn

Do While Not rst.EOF
  Response.Write "<a href='thispage.asp?LETTER=" & UCase(rst.Fields(0).value) & "'>" & UCase(rst.Fields(0).value)  & "</a> "
  rst.MoveNext
loop

Hope you got it now.. tell me if you have any problems

-Dirar
0
 

Author Comment

by:danniborg
ID: 16561144
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!
0
 
LVL 6

Expert Comment

by:Dirar Abu Kteish
ID: 16561472
I am glad about that... :)
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever needed to get an ASP script to wait for a while? I have, just to let something else happen. Or in my case, to allow other stuff to happen while I was murdering my MySQL database with an update. The Original Issue This was written…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses
Course of the Month18 days, 7 hours left to enroll

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question