classic ASP - trying to page through database records 2

[wvdba]

Hi,
i have an intranet website that lists records from an access database. right now, there are about 300 records in the database, which are listed continuously in a page that's too long. i would like to list 30-40 records per page and start the next page when they press "next" button starting from the next record in the database. is there any kind of logic that i can employ in looping through the recordset to accomplish this. thanks.

 

[vicvirk]

When you query the records, sort them in some way (i.e. order_id)

Everytime you call the page, check to see if a page number is set, if it is not set, manually set it to 0 (make it zero based)

thisPage = request.querystring("page")
if thisPage = "" or isNull(thisPage) or isEmpty(thisPage) then
thisPage = 0
end if

Your SQL query uses this information and queries accordingly

numRecordsToShow = 30
firstRecord = thisPage * numRecordsToShow
sql = "select top " & numRecordsToShow & " from myTable where order_id > " & firstRecord & " order by order_id asc"

So you'll see on the first page, when it's set to 0 the query will select the first 30 records where the order_id is more than 0, on page 2 it will select the first 30 records where the order_id is more than 30 and so on.

Your prev/next links are set by the "thisPage" var

if thisPage > 0 then 
prevPage = thisPage - 1
else 
prevPage = 0
end if
nextPage = thisPage + 1
<a href="myPage.asp?page=" & prevPage & ">previous</a>
<a href="myPage.asp?page=" & nextPage & ">next</a>

Obviously it will require more tweaking, but hopefully it can lead you in the right direction.

If you are still having problems, post some code and I (or someone else) will try to help out...



--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[wvdba]

vicvirk, thanks for the reply.
this means that i have to do multiple queries. is there anyway to do just a single query for all the records and do something with the recordset that results from the query? is there any way to page through this recordset?
thanks.

 

[vicvirk]

you could do some CSS/Javascript tricks to accompish this:

<script language="javascript">
function navigate(intSet) {
	var divs = document.getElementById("records").getElementsByTagName("div");
	// hide all of them
	for (var i = 0 ; i < divs.length ; i++) {
		divs[i].style.display = "none";
	}
	// show the one you want
	document.getElementById("page" + intSet).style.display = "block";
}
</script>
<div id="records">
	<div id="page1" style="display:block;">
		<p>records 1 - 30</p>
		<a href="javascript:navigate('2')">next set</a>
	</div>
	<div id="page2" style="display:none;">
		<p>records 31 - 60</p>
		<a href="javascript:navigate('1')">prev set</a> | 
		<a href="javascript:navigate('3')">next set</a>
	</div>
	<div id="page3" style="display:none;">
		<p>records 61 - 90</p>
		<a href="javascript:navigate('2')">prev set</a>
	</div>
</div>

There are things to consider, however...

Doing it the above way you'll have to assume the user has javascript enabled (if it's on an intranet, shouldn't be a problem)

Doing it the above way, you are making 1 call to the db and the page could take a while to load.

Doing it by multiple queries does make multiple calls, but grabbing 30 (or whatever) records at a time is much quicker than grabbing 300+.

Hope that helps.



--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[wvdba]

vicvirk, thanks again. i think the first method is somewhat more efficient, even though you make multiple queries. but my question is, when you use "TOP 30", how does it pick up records 31-60, 61-90 and so on? i'm kind of puzzled on the first method's logic. if i can understand it better, i could implement it.
thanks so much.

 

[wvdba]

i forgot to mention that my order_id / record key is alpha numeric and it's not a number.
thanks.

 

[vicvirk]

vicvirk, thanks again. i think the first method is somewhat more efficient, even though you make multiple queries. but my question is, when you use "TOP 30", how does it pick up records 31-60, 61-90 and so on? i'm kind of puzzled on the first method's logic. if i can understand it better, i could implement it.
thanks so much.

If you have a table of records like this:

cust_id first_name last_name
1 John Doe
2 Jane Doe
3 Mary Jane
4 Susy Smith
5 Mike Richards
6 Anna Banana
7 Gerry Harry

and do this

select top 3 * from table where cust_id > 0
it will return John, Jane and Mary

select top 3 * from table where cust_id > 3
will return Susy, Mike and Anna

select top 3 * from from table where cust_id > 6
will return 1 record - Gerry

The * is the fields you want, if you only want the first name,

select top 3 cust_id,first_name from table where cust_id > 1

There are different ways to run the query depending ont he db software you are using, ie...

select * from table where cust_id > 0, LIMIT 3 (mysql)
or
select * from table where cust_id > 0 and rownum = 3 (oracle)

but what you are doing is returning a set of x records instead of the whole record set.

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[vicvirk]

i forgot to mention that my order_id / record key is alpha numeric and it's not a number.
thanks.

that will cause problems - is there anyway you can create an addition field in the db that increments by 1 everytime?

--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[gmmastros]

Alpha numerics shouldn't cause a problem. You should still be able to use a comparison operator.

In fact, what you could do is pass in the last shown value to the query.

The following is pseudocode...

Select Top 30 *
From   YourTable
Where  YourKeyColumn > TheLastKeyValueShown
Order By YourKeyColumn

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wvdba]

thanks,
i think i have a pretty good idea on how to proceed. this was great help. thanks both.

 

[wvdba]

I tried the top nn method. paging forward is ok. paging backward is a bit problematic. here's the scenario:
paging forward i list orders 1 thru 30, then 31 thru 60.
page looks like this:
31
32
33
.....
60
at that point, when i press prev page, with order-by-desc, where order_num < 31
the next page looks like this
30
29
28
.....
1
which is backward.
is there any solution for this?
thanks.

 

[vicvirk]

do an order-by-asc.



--------
GOOGLE is a great resource to find answers to questions like "how do i..."

If you don't know exaclty what you want to do or what to search on, try Google Suggest:

http://www.google.com/webhp?complete=1&hl=en

--------
I have recently been semi-converted to ensuring all my code (well most of it) works in both javascript and non-javasc

 

[wvdba]

here's how i fixed it, in case anybody was interested:
i used top 30 and ASCending option for page forward.
i used top 30 and DESCending option for paging backward.
there are two hyperlinks on the top: "PREV" and "NEXT" which page backward and forward. PREV has the first record of the page and NEXT has the last record of the page. when you press PREV, it takes the first record of the page and uses < to get all the records before that. when they press NEXT it takes the last record of the page and uses > to get all the records after that. it's working pretty good.
thanks for all the help and suggestions.