Recordset Paging using Alphabet

[DenverMarc]

Hello,

I have an ASP page that retrieves a list of products from an Access 2000 database. Right now I have it set to show 15 products at a time, with <<Previous 1 2 3 4 5 6 7... Next>> to allow people to move through the records.

My boss would like it set up this way:
<<Previous A B C D E F G H... Next>>, where if a user clicked on "F" for instance, it would show all products starting with the first "F" and continuing. So, if there were only 11 products that start with "F", they would show on the page along with the first 4 that start with "G".

How can I do this? Not sure if you need to see the code I have already.

Thanks,

Marc

 

[DotNetGnat]

here

-DNG

 

[DenverMarc]

Thanks, I'll give it a shot.

Marc

 

[DenverMarc]

That worked, but not exactly what I was looking for. You only see one letter at a time (If I click "M" I only see the products that begin with "M"). Is there a way to have the list start with whatever letter I click, and continue on with the next letter?

Thanks,

Marc

 

[Sheco]

Maybe you could check if the first letter >= "M

 

[DenverMarc]

That sounded like a brilliant idea Sheco, but it didn't work. Maybe I did it in the wrong spot. I changed

<% 
for x=1 to 26 %>
<a class="pageselector" href="datacards3.asp?[COLOR=red]letter=<%= chr(64+x) %>">[/color]<%= chr(64+x) %></a>
<%next 
%>

to

<% 
for x=1 to 26 %>
<a class="pageselector" href="datacards3.asp?[COLOR=red]letter&gt;=<%= chr(64+x) %>">[/color]<%= chr(64+x) %></a>
<%next 
%>

.

After doing that, clicking on a letter moved me back to page 1.

I'm also having a problem with the "next" function. I had the page originally set up to show 15 records at a time. Clicking the "next" button moves me to "page 2" no matter where I am. That code looks like this:

<%if lngPage<nPageCount then %>
<a class="pageselector" href="datacards3.asp?page=<% =lngPage+1 %>">&nbsp;Next >></a>
<% end if %>

I'm afraid I'm pretty new at this and not sure how to correct that.

Thanks,

Marc

 

[Sheco]

I was thinking you would build the links like always but change your SQL Query to something like this:
[tt]
SELECT * FROM Client
WHERE Left(FirstName, 1) >= 'M'
ORDER BY FirstName, LastName
[/tt]

 

[DenverMarc]

Thanks Sheco,

That worked!

Now I have the problem that the "Previous" and "Next" buttons do not work if I've used one of the letters to navigate. If I've clicked "M", and see products beginning with M and N or whatever, then when I click "Next" it goes to "page 2" (back at "A" & "B") instead of resuming where I left off. How can I make this work?

Thanks,

Marc

 

[DotNetGnat]

show the code for your navigations links...

-DNG

 

[Sheco]

Suppose you start with "M" and then it overflows down to "N" and some, but not all, of the "O"

When you click next do you want it to go to N because N is after M? Or do you want it to go to the middle of the O section where it left off?

 

[DenverMarc]

I would like to go to the middle of the O section where it left off. I guess I'm mixing two different sets of logic here. Right now the code for the previous/next links looks like this:

<%
strPage=request("page")
if strPage="" then
 lngPage=1
else
 lngPage=clng(strPage)
end if
%>

<% if lngPage>1 then %>
<a class="pageselector" href="datacards.asp?page=<% =lngPage-1 %>"><< Previous</a>&nbsp;&nbsp;
<% end if%>

<%if lngPage<nPageCount then %>
<a class="pageselector"  href="datacards.asp?page=<% =lngPage+1 %>">&nbsp;&nbsp;Next >></a>
<% end if %>

And the link for the letter navigation looks like this:

<a class="pageselector" href="datacards.asp?initial=M">M</a>

...which doesn't have a page number.

Thanks,

Marc

 

[Tarwn]

One way to do this which would be differant than the previous method would be to use two SQL statements. The first statement is your original call to the database to populate a Recordset. Use standard paging on the recordset as you would with a numbered paging system. Setup your page size, etc.
Next, if a letter was passed to your page, do an SQL call along the lines of:
SELECT COUNT(ClientIdField) FROM Client WHERE Left(ClientName,1) < "M"

Now you know where in your numbered paging scheme M first shows up, so just set your AbsolutePage value on your recordset to Fix(countRS(0)/PageSize + 1)

So basically the idea is if you have your PageSize set to 10 and there are 14 records before the first M, then your will be setting the absolute page to Fix(14/10 + 1) = Fix(2.4) = page 2.

Then just display your records as usual in a paging scheme. There's plenty of informaiton in setting up std paging with recordsets, the only magical thing here is the second query to determine the page based on how many records precede your letter.

-T

 

[DenverMarc]

Thanks Tarwn,

I found another solution. I added the "initial" variable to the previous and next links, so it treats each them as "page 1" whenever a letter is clicked on, the moves forward to the next "page" with the next button and back with the previous button.

Thanks everyone for all your help.

Marc

 

[DotNetGnat]

why dont you go ahead and post your running code sample, so that others can use it.

-DNG

 

[DenverMarc]

Defining the first letter:

<%
  Dim FirstLetter
  FirstLetter = Request("Initial")
%>

link for "previous" button:

<% if lngPage>1 then%>
<a class="pageselector" href="datacards.asp?initial=<% =FirstLetter%>&page=<% =lngPage-1 %>"><< Previous</a>&nbsp;&nbsp;
<%end if%>

link for "next" button:

<%if lngPage<nPageCount then %>
<a class="pageselector"  href="datacards.asp?initial=<% =FirstLetter%>&page=<% =lngPage+1 %>">&nbsp;&nbsp;Next >></a>
<% end if %>

This gave results such as: If I click on the "C" the address would show

http://...

.../datacards.asp?initial=C

Then clicking the "next" button would result in

http://...

.../datacards.asp?initial=C&page=2

Hope that's clear.

Thanks,

Marc