random image

adamhw · June 25th, 2010, 04:57 AM

Hi there

I'm trying to pull out 5 random images from a SQL database using the following:

Set con = Server.CreateObject("ADODB.Connection")
con.Open Application("TORCString")

strSQL = "SELECT id, Title, imagename, imagename_m, Detail FROM tbl_gallery WHERE IsActive = 0 ORDER BY sequence"
Set rsImg = Server.CreateObject("ADODB.RecordSet")
rsImg.Open strSQL, con, 3, 1

if Not rsImg.EOF then

strtitle = rsImg("title")
strimage = rsImg("imagename")
strimagename_m = rsImg("imagename_m")
strdetail = rsImg("detail")

upperlimit = rsImg.RecordCount
lowerlimit = 1

Randomize
rsImg.AbsolutePosition = Int((upperlimit - lowerlimit + 5) * Rnd() + lowerlimit)

but it's only pulling out the first image of the recordset - not sure why it's not working...any help greatly appreciated.

Many thanks

Adam

Imar · June 25th, 2010, 05:10 AM

Hi there,

Try debugging the coder and see what value RecordCount has. Maybe it's -1?

You could also let SQL get random images using ORDER BY NewID()

Hope this helps,

Imar

adamhw · June 25th, 2010, 05:15 AM

Thanks Imar

When I response.write the recordcount I get 6?

response.Write(upperlimit)

Don't think I know how to get SQL to randomize images using ORDER By NewID()?

Imar · June 25th, 2010, 05:40 AM

There are two reasons I can think of why this doesn't work: the recordset doesn't support it (can't recall what exactly 3, 1 means) or your random number is not OK. What happens when you assign the random number to a variable first and debug that?

To get random records from SQL Server, you can use NewID() which generates a random value for each record. Something like this should give you two random records:

SELECT TOP 2 id, Title, imagename, imagename_m, Detail FROM tbl_gallery WHERE IsActive = 0 ORDER BY NewID()

Imar

adamhw · June 25th, 2010, 05:47 AM

Imar

You're right - when I output the random value as a variable it returns -1 so I understand why it's not working but I don't understand why it returns -1?

thanks

Adam

Imar · June 25th, 2010, 05:58 AM

Neither do I ;-)

This:

Code:

upperlimit = 6
lowerlimit = 1
Randomize 
Response.Write((Int((upperlimit - lowerlimit + 5) * Rnd() + lowerlimit))

should output positive numbers. Does it do that for you?

Another thing: you're reading from the recordset before you set the AbsolutePosition. Is that deliberate?

Imar

adamhw · June 25th, 2010, 06:08 AM

Imar

think I'm going to do it your other method - NewID()

thanks alot

Adam