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
Andie wrote:
OK,
Sorry to sound a little slow, I am still learning this stuff.
I have been told I need to do the following steps to display random
products from the product table within my database.
1) get recordset of fetured items,
(2) disconnect,
(3) Add a field and populate with random integers,
(4) sort by the random field,
(5) display first x number of records.
The fields I need to to retrieve from the products table are:
idProduct
description
descriptionLong
listPrice
price
smallImageUrl
stock
fileName
noShipCharge
I hope this helps you understand what I am trying to do but I was
told that using a disconnected recordset works quicker than using the
array method I am currently using.
