ASP - Loop while eof or bof - Select random product from idproduct but skip empty ids

Question

Hi, I am trying to display a random product image and description from the access database, so i am selecting the highest idproduct then randomising a number between 1 and %highestid%, this is what i have so far....

IF frontpage = 1 then

SQLSTR = "SELECT idproduct AS prodtot FROM products order by idproduct desc"
Set objRS = Server.CreateObject("ADODB.Recordset")

SET objrs = oconn.execute(SQLSTR)

' Check result
Response.Write objRS("prodtot")
' attach 
ntop = objRS("prodtot")

Randomize
' Generate random value between 1 and nTop .

nRandom= Int((nTop * Rnd) + 1)
sqlstr = "select * from products where idProduct = " & nRandom
response.Write"<br /><br />" & (sqlstr) & "<br /><br />"
    'SET rs = oConn.execute(randomprod)
    SET rs = oconn.execute(SQLSTR)

    pranproddesc = rs("description")
    response.Write(pranproddesc)
    pranprodimg = rs("smallImageUrl")

end if

So far so good! But i have a problem, over time products have come and gone and I have alot of gaps in the %idproduct%, ive tried loop while rs.eof but it doesn't seem to do anythimg usefull, if anything at all. Just to clarify I have idproduct 1, 2, 5, 10, 11, 12 etc etc, so when it randomises idproduct3 it all goes up the spout! Can anyone help?

Thank you in advance! :)

Answer 1

Do the following:

IF frontpage = 1 then
  Set objRS = Server.CreateObject("ADODB.Recordset")
  SET objrs = oconn.execute(SQLSTR)
  sqlSTR = "SELECT TOP 1 * FROM products ORDER BY NEWID()"
  response.Write"<br /><br />" & (sqlstr) & "<br /><br />"
  SET rs = oconn.execute(SQLSTR)

  pranproddesc = rs("description")
  response.Write(pranproddesc)
  pranprodimg = rs("smallImageUrl")
end if

That sql will work in SQL Server:
Look at this page for SQL to return a random row for other databases: http://www.petefreitag.com/item/466.cfm

Thanks, btw, I learnt something new figuring this out.

Answer 2

Rather than select a random ProductId, select a random row index from the recordset. That way you only have to hit the database once as well :)