Disconected Recordset & Random Selection

[Andie]

Hello All,

How would I go about using a disconnect recordset and select (x) records
from it, x being the number of records to be selected.

Many thanks in advance.

 

[Andie]

Basically within my database, I have a table called products, and I was told
about using a client side or "disconnected recordset" to display records on
the asp page I am using, I currently use an array to store all the product
information to be displayed, and then randomly display 6 products on the
screen.

How would I used a client side recordset to display 6 different records each
time?

Soeey but you will have to bear with me as I am only just getting to grips
with ASP programming..

Many thanks in advance.

 

[Tim Williams]

You don't need a disconnected recordset just to display records - they are
only really useful if you want to persist the data between calls/pages.

Selecting "random" records from a database is not so straightforward: try
this or a variant of it -
http://www.aspfaq.com/show.asp?id=2132

Exactly how you do it may depend on how many records you have in your DB
table...
A search on Google for "asp random records" should get you some ideas.


tim

 

[Bob Barrows]

Depending on your backend database, it will almost always be more efficient
to do this via your original query (see Tim's link).

I do not believe that using a disconnected recordset will help here. For one
thing, you cannot add a field to a recordset that has been opened on a data
source, disconnected or otherwise.

Since I don't know what your database is, let me show a variation of the
alternate technique shown in Tim's link that may be more efficient (untested
air code):

dim cn, rs, strSQL,ar, arSelected(5), rCount, CurrRR, SelectedCount, i,j
'open a recordset using the default firehose cursor
strSQL = "Select idProduct FROM products"
set rs=cn.execute(strSQL,,&H0001)
ar=rs.getrows
rs.close
rCount = ubound(ar,2)
SelectedCount = 0
do until SelectedCount = 6
randomize
CurrRR = cLng(rnd*rCount+0.5)
if not AlreadySelected(arSelected, CurrRR) then
arSelected(SelectedCount) = CurrRR
SelectedCount = SelectedCount + 1
end if
loop

strSQL="Select idProduct, description, descriptionLong " & _
"listPrice, price, smallImageUrl, stock, fileName, noShipCharge " & _
"FROM products WHERE idProduct IN ("
for i = 0 to 5
if i = 0 then
strSQL = strSQL & ar(0,i)
else
strSQL = strSQL & "," & ar(0,i)
end if
next
strSQL = strSQL & ")"
response.write strSQL 'for debugging only
set rs=cn.execute(strSQL,,&H0001)
ar=rs.getrows
rs.close
set rs=nothing
cn.close
set cn=nothing

response.write "<table>"
for i = 0 to 5
response.write "<tr>"
for j = 0 to ubound(ar,1)
response.write "<td>"
response.write ar(j, i)
response.write "</td>"
next
response.write "</tr>"
next
response.write "</table>"

Function AlreadySelected(pAr, pSelected)
dim i
AlreadySelected = false
for i = 0 to ubound(pAr)
if len(pAr(i)) = 0 then
exit for
if pAr(i) = pSelected then
AlreadySelected = true
exit for
end if
next