Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-15 : 08:47:04
|
| Lawrence writes "I apologize if this question has already been answered, but I've looked through the Forum, and found similar postings, but I didn't come across any that meet my needs.Here's my scenerio: I have employee directory which has a "Jump To" menu that allows users to jump to specific letters of the alphabet which correspond to the last names of our co-workers.The ASP page uses PageSize to display 30 records per page. As you'd imagine, if you click on "J" you might get a page that perhaps starts with "Jackson" and ends with "June" (though this may take more than one page to do so). If you click on "Q", you may or may not get any records.What I'd like to do is figure out how I can use AbsolutePage to go to the specific page where the first "J" is found, and display all 30 records, regardless of whether or not if there are 30 J's on that page or 10 I's, 3 J's, and 17 K's.In other words, if the SQL statement was set to SELECT all records and list them in alphabetical order, with 30 records per page, I'd want to see the first page with an employee whose last name starts with "J", not just all employees whose last name starts with "J". In doing so, navigating from page to page would allow the user to go from "J" all the way to "A" or "Z", as opposed to stopping at "Jackson" or June"." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-02-15 : 08:55:08
|
| If all of the rows are loaded into the recordset, it's unlikely the AbsolutePage method will work as you would like, simply because each letter would have to begin and end on the PageSize boundaries. Since this isn't likely, you'd pretty much have to requery the recordset with a new filter each time someone clicks a letter.Is it really necessary to pull down all the rows in the resultset, and then page through them? What about a stored procedure that accepts a character parameter that returns only those names beginning with that character? You'd more or less have to do this anyway, but it will lower the amount of data being sent to the client. If they're looking for Jones, why include A-Z? |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-15 : 12:34:16
|
| I've seen some cool demos done on the client side of a web page, paging through recordsets using XML, but I haven't created any myself. If you don't want to go there, then like Rob said, why not just return the data that you want? If you want to display 30 records regardless of whether they start with letters later, then you could do something likeSELECT TOP 30 *FROM EmployeeWHERE LastName >= 'J'ORDER BY LastName, FirstName------------------------GENERAL-ly speaking... |
|
|
|
lawrencelove
Starting Member
2 Posts |
Posted - 2002-02-15 : 12:43:15
|
I don't know why, but I always seem to think better AFTER asking someone for help...With that being said, I've come up with a solution to my problem. For those of you who may have been just as stumped as I was, I'll show you my solution. If anyone has a better method of doing it, please feel free to share it with me. In either case, here's mine: sql = "SELECT employee_id, employee_lastname, employee_firstname FROM employee_table ORDER BY employee_lastname, employee_firstname"rs.Open sql, Connrs.Find("item_equipment_short_desc LIKE '" & searchFor & "%'")rs.AbsolutePage = CInt(rs.AbsolutePosition/rs.PageSize) [Addition operator] 1In this case, "seachFor" is set to the value of Request.Querystring("someVariable"), and rs.PageSize was set to 30, both in previous lines. By the way, I used "[Addition operator]" because I couldn't figure out how to produce a "plus sign" in this Forum. I used "CInt(expression) [Addition operator] 1" because I needed to round the value UP. If you know of a better way to round up, I'd be interested in hearing that as well.This may not be the best way to do this, but it seems to work fine. And like I said, I've still like to hear any other suggestions.Thanks!Edited by - lawrencelove on 02/15/2002 14:02:23 |
|
|
|
lawrencelove
Starting Member
2 Posts |
Posted - 2002-02-15 : 12:48:09
|
| Rob and Ajarn,Thanks for your suggestions, but in my case I actually do need to display all the surrounding records. In all honesty, I used the Employee Directory as an example, it's not literally the purpose I have intended.Edited by - lawrencelove on 02/15/2002 13:28:36 |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-02-15 : 17:42:11
|
quote:
By the way, I used "[Addition operator]" because I couldn't figure out how to produce a "plus sign" in this Forum.
Lawrence, glad to hear you got a solution that works. Can't say I've done much with the AbsolutePage setting myself. Regardless, in response to your comment above, many people have said the same thing, but actually, it's only the preview window that does not display it. In the actual forum posts it + displays + just + fine.  Graz, what do you think about adding this factoid to the Forum FAQ until the preview page is fixed?------------------------GENERAL-ly speaking...Edited by - AjarnMark on 02/15/2002 17:45:09 |
|
|
|
|
|
|
|
|
Jumping into a specific point in a Recordset which uses PageSize and AbsolutePage.