ASP - Loop while eof or bof - Select random product from idproduct but skip empty ids
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 prod开发者_如何转开发tot 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! :)
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.
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 :)
精彩评论