How to break up recordset to display first 5 at top,the rest at bottom

[IEAN]

Hi all,

Say I have 30 records I want to display on a page from my database. I want to display the first 5 records on the top of the page and the next 6-25 at the bottom of the page. How do I break up the recordset so that it will display the first five here and the rest there? Please advise, thanks!

 

[DotNetGnat]

not completely sure what you are trying to do...but i would suggest you doing this at the query level...

Select Top 5 * from mytable order by ID ASC

gives you top 5 rows that you are looking for and can be displayed on the top of the page...

-DNG

 

[IEAN]

Hi DNG, thank you for your reply. ok, so by using the Top keyword I can get the top 5 records, the first problem is solved. Now do you have any idea as to how I can get records 6 to 30? Please advise, thanks!

 

[Chopstik]

You should be able to do two loops and simply place them where you want them to show up on your page. So, at the top, you could do a for/next loop to obtain the first five records (if you want them in a certain order, you would retrieve them from your db in the required order).

[COLOR=green]'This is pseudo-code, you'll have to modify as needed.[/color]
for i = 1 to 5 [COLOR=green]'This will show the first five records[/color]
  response.write "Record " & i & " is " & rs.fields("field1") & "<br>"
  rs.movenext
next

That will show you the first five records. Then, further down the page, you basically repeat the same process.

[COLOR=green]'This is pseudo-code, you'll have to modify as needed.[/color]
for i = 6 to 30 [COLOR=green]'Because this is where you left off[/color]
[COLOR=green]'Do not reset your recordset to .movefirst as it should just stay where we left off after the first five.[/color]
  response.write "Record " & i & " is " & rs.fields("field1") & "<br>"
  rs.movenext
next

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[IEAN]

Hi Chopstik, thank you for your reply, when I do a
for i= 1 to 5, it displays the first 5 records just fine, but when I do a i= 6 to 30, instead of displaying records 6 to 30, it displayed records 1 to 30. Any ideas why? Please advise, thank you!

 

[IEAN]

Someone pleeeease help, I need this solved asap, thank you in advance for all your help!

 

[DotNetGnat]

what database are you using?

-DNG

 

[IEAN]

Hi DNG, I am using an MS Access db.

 

[Tarwn]

The only reason I could see it re-displaying 1-30 is if you either re-queried the database, created a new recordset, or did a .MoveFirst
I disagree with doing for loops on recordsets this way, but Chopstik's code should work without resetting te recordset.

 

[Chopstik]

IEAN, we may need to see some of the other code in your page in order to see if any of the conditions that Tarwn mentioned are what are causing your issue.

Tarwn, just for my own curiosity, what other solutions would you suggest (a do/until loop?)? Is there a performance or other issue that would be the reason? If so, I'd be curious... Thanks.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[kaht]

Chopstik, using recordset method getRows allows you to pull all the data in one pass and store it in an array. This allows you to close your recordset object immediately afterwards and give the resources back to your database. The link I posted above explains it better than I can

-kaht

Looking for a puppy?

[small]Silky Terriers are small, relatively odorless dogs that shed no fur and make great indoor pets.[/small]

 

[Chopstik]

I'm going to have to start using this... I'd come across it before but it didn't sink in the last time. It is making more sense now... Thanks!

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[etjohnson81]

As mentioned above, if you go to the second section without closing or requerying the recordset and don't do a "MOVEFIRST" then it should continue where it left off.