Alphabetical Recordset Navigation/Index

if I use a WHERE clause in my select statement, only the records beginning with the chosen letter will be displayed... The user should be able to keep paging thru (both fwd and backward) the entire universe of records after jumping to records beginning with a particular letter in the recordset. For instance, after jumping to letter "J" the user should be able to click on the next/previous page # to see the records K and beyond - or I and before.... Using a WHERE clause in the SQL precludes this...

hello Corrupticus,
first of all, very long questions with less points doesn't encourage experts to read and solve ur problem.

second:
through my whole years of experience in web surfing and development, i have never such an idea.

using offsets for paging is nice, and also static that will not be changes.except u decided to change no of results per page.

but as a concept. if there were an alphabetical index in any page, that means a small query that searches all records in the database that starts  "only" with this alphabet.

u can make a survey for people around u and ur friends, ask them if u found this alphabetical index, what will it indecate.

1) just all records starting with this alphabet
2) all records starting with this alphabet and alphabets larger than it.

and let me know

To encourage experts to participate, I'm doubling the points...

Kanary, the answer to your question would be a modified 2) - i.e. Index should allow the display of all records starting with the given letter and alphabets larger (if you hit the next page link) or smaller (if you hit the previous page link) than it. I basically need a way to find out what the offset is for the first record that begins with the given letter - this way, from this offset, a user can page both front and back as needed.

My aim is to:
1) Find the first record(s) begining with the clicked on letter
2) Get the offset for that record
3) Assign that offset to the respective letter

I'm trying to achieve 1) using a regular expression, but  it's not quite working though because I'm not very good at using regular expressions.

Here is my code so far...

VARIABLES USED
rsProposals is the recordset

<%
delimiter = " "
Dim recSetCol, urlString, results
recSetCol = rsProposals("company")  'the column in the recordset that I am looking for a particular letter in

    function findRecords(ltr,rSetCol)
         dim re,matches,letter
         
         letter = ltr          
         set re = new RegExp
         re.pattern = "^"&letter&".*?"   ' I'm trying to look for the beginning of the company name starting with the letter in question
         re.IgnoreCase = True
         set matches = re.execute(rSetCol)
         if matches.count then
              findRecords = matches(0)   I need help using this - I'm trying to print out the results of the match - this shows 1st letter only
         else
              findRecords = ""
         end if
         end function

For i = 65 to 90  ' prints A-Z - the letters that a user can click on to see the company names begining with that letter clicked on
  TFM_character = Chr(i)
 
   results = findRecords(TFM_character, recSetCol)
   Response.Write("<font color='#FF0000'> ")
   Response.Write(results & "</font> ")  'trying to see what my regexp did - it actually prints out the first letter corresponding to the recordset results on the current page - i.e. prints out D, if the records on page are D records (there are some C records however), E if the next page shows E records (although there are some D records) - so it at least works somewhat....
 

   Response.Write("<a href=""" & Request.ServerVariables("URL") & "?" & MM_keepMove & "page_num=" ) 'this would be where the user would go if they click on say J, - to the page where the company name begins with J
  Response.Write(""">" + TFM_character + "</a>")
  If(i<90)Then Response.Write(delimiter)
Next
%>

hello Corrupticus  again,

i have thinked in ur problem well, im going to provide a soluition to ur problem but bot the code, if u have any problems with the code i think u should ask in the asp forum, ok.

here's the scenario:

1) define no of results per page at first, let's say: pagesize=20
2) now u search the database, let's say the result were 1000 records reurned.
 suppose u were searching for certain companies, the search result returns company specifications and locations, let the search result return company IDs also.
now u have the company ID's that have been returned from the search, and these companies are the one who should be indexed by it's first Alphabet.
3) create an array of integers and store all the company IDs into it, and store this array into session array.
now this session array will be in ur hand while browsing next or previous as u wish.
4) create another session variable telling u the page number u r in, say: pageno=1

when the pageno = 1 u list the first 20 records (0 -> 20*1) from the Company ID's array
when pageno = 2 u list the next  20 records (20*1 -> 20*2) which is (pagesize* pageno) and so on.

using this way, add as many records as u wish,  change no of result per page as u wish, u won't have to store a certain offset value per each page at all.

also this will solve ur problem, how?

now u wana index the result alphabetically, suppose u clicked the alphabet "C". now u will search the result array for the first occurance of the "C". as long as we have 1000 company IDs resulted from the search, suppose that the first occurance of the "C" is at record no  105 from the 1000.
using calculations u will get that if the page size is 20, so we will have to press next 5 times to get to index 105.
and the page no will be no 6 pageno=6.

pageno=Mod(1000/105)

sorry, delete the last line.

page no = 105/20   = 5.25

that means the page after pafe 5 , yet to page 6

so will return the same result if u were not searching for a certain alphabet, except for that u will display  page 6 at first, that means u have searched the result according to the charachter selected and the next and previous navigation are as normal as the ordinary search.

what do u think

don't u think this idea worth 500 points :))

why not completely do away with this search step and just use a dynamic drop down list on the search form page?  this way the user can just type in or choose the company and not have to deal with paging through to get what they want.

hello Corrupticus,

i have given u an excellent solution to ur problem.
so i think u should reward the points and end the question by now.
waiting ur action here.

thx a lot.

Kanary, first of all, I don't know why you assume that you gave me an "excellent solution" that you think is worth 500 points. I don't understand how you arrived at this conclusion. To cut to the chase and make the long story short, I'll reiterate, from my previous comments above, what I was looking for:

comment 1:

-> Is there any way to programmatically assign a letter to an offset/page #, such that when the letter is clicked on, the user can "jump" to the section of the recordset where the records begin with the clicked on letter?

-> Any assistance with this (esp with examples) will greatly be appreciated!

-> I've included the numeric paging code (a dreamweaver extension by tom muck) which might be useful in helping to see how the paging (and use of offset) works


If u read my comments and take a closer look at the code I provided, you will realize I already have in place a navigation scheme that defines, calculates and displays the number of records per page - 15 records per page. Which means I always know what page I'm on - even if I manually change the offset parameter (which gets translated to a page number by the code).  I would also have know what the total # of records returned is (which is how the total number of pages is determined) Hence - I don't need to go thru steps 1 & 2 of your 'solution' or redundantly use session arrays as you suggested in points 3 & 4 of your comment. Why create a superfluous array to search thru, when I can search thru my recordset??

Again, from my comments above:

My aim is to:
1) Find the first record(s) begining with the clicked on letter
2) Get the offset for that record
3) Assign that offset to the respective letter

I have figured out how to find the first record  beginning with whatever letter is clicked on. What's left is finding out what offset/page number the record is on and dynamically assign that offset to the respective letter each time the page loads - such that when the user clicks on that letter, they are taken straight to the respective page. If you or anyone else can help me find a way to do this, (and not ask that I go to the asp forum for help with code - why reward the same question twice in different forums) then we can start talking points. Till then, feel free to share any meaningful ideas you may have - thanks!

hello again,
i won't be able to help as im too bust these days.

u can ask a new question inasp withoit doubling the points.
open a new questio in asp and paste the url of this forum,
if u find an answer there u can ask the administrators to delete this question

wishing u good luck.
kanary.