Calling an "Nth" record in a recordset

[jasonsalas]

Hi everyone,

I wrote something yesterday:
thread333-157833 returning a random record. I modified my script so that I'm just returning a random number.

<%
Randomize
Dim TotalWinners
Dim random
TotalWinners = CInt(10) ' this would be the total number of winning records..this will be variable
random = Int((TotalWinners - 1 + 1) * Rnd + 1)

' formula:
' Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Response.Write(&quot;This is the winning record's Nth value: &quot; & random)


I was wondering if anyone has any idead on how to return a &quot;Nth&quot; record from a recordset, and not necessarily the precise record by ID. For example, if the total number of records in the my script generates the number &quot;10&quot;, I want to have a SQL statement pull the 10th record in the recordset, and not necessarily the record with ID &quot;10&quot; as the unique key field. Thus, that paerticular record may have ID 7,895, or 13...it won't matter, because the record will be precise.

Something like this:

SELECT * FROM tblEntries WHERE EntryID = '&quot; & random & &quot;'&quot;

Thanks!

 

[ToddWW]

Well there's two ways you can do it. First open the recordset with all of the records. Then you have two options.

WHILE objRS.AbsolutePosition <> random
  objRS.MoveNext
WEND

That will stop the recordset pointer at the random number.

OR

objRS.Move random

That will move the record pointer x number of positions forward.

You'll have to play with these depending on if zero can be one of your random numbers so you don't get an EOF error.

ToddWW