Random Image loads same one over and over

[Nilgni]

I have been trying this with no luck

Background Information:
Dreamweaver MS and Access Query

I wrote a query that displays a random record using the following SQL

SELECT TOP 1 Rnd(tblOutsideLinks.LinkNo) AS RandomNo, tblOutsideLinks.LinkName, tblOutsideLinks.AdroitServerLocation, tblOutsideLinks.WebsiteLink, tblOutsideLinks.ContactName, tblOutsideLinks.ContactEmail
FROM tblOutsideLinks
ORDER BY Rnd(tblOutsideLinks.LinkNo);

Then I created an image in dynamic form in Dreamweaver. The link goes to the query and the proper field in MS Access.

When testing the query in the Dreamweaver window using the TEST button the result is random just as it should.

Then I put it all together and uploaded the page only to find that it loads the same image each time IE.

Any thoughts? I know that Random isn't always random, but if it worked just part way it would be fine for the purpose that I have.


When I get it to work I would like to extend it to six images. Essential choosing six links from the table in Access and linking them to the dynamic image feature in MX

Thanks! My 15 year old is pestering me about finishing his project.

Keith

 

[Sheco]

This seems like more of an MS Access issue than an ASP question but...

Random number function are not truely random. They always give the same sequence of numbers starting from a
given "seed" number. Perhaps when you used the function within DreamCleaver the random function was holding state between iterations but after it was uploaded it is starting again fresh every time.

In most randomizer functions, the appearance of randomness is acheived by not always starting with the same "seed" number. It is very common to use the system clock to rotate the seed.

PS: also might want to check that the image isn't just plain cached.

 

[Tarwn]

MS Access will give you random numbers when you have it open and are playing around inside. It will give you the same series of numbers when you are querying from outside. In fact Rnd() is pretty useless in my opinion.

#1
One option is to create a pseudo random function of your own. Basically what you are aiming for is a method to assign a differant number to each row so that the order changes on each call. Using time() as part of the equation and a unique id for the row, we can create a pseudo-random ordering like so:
SELECT TOP 1 fieldName, fieldName2, etc
FROM TableName
ORDER BY rowIdField * time() * 1000000 mod 1000

This method will return the same value if several calls come in the same exact second, but also will give you a fairly random order. In tests on a small table I noticed a trend where it would take about 3-4 times as many records to return each of them at least once. ie, with 25 records it generally took about 80 calls (seconds) to get each record at least once.

An option to increase the "randomness" would be to create a random number your ASP code and toss that into the mix:

Randomize()
strSQL = SELECT TOP 1 fieldName, fieldName2, etc FROM TableName ORDER BY " & Rnd() & " & rowIdField * time() * 1000000 mod 1000"

There are some other tricks that might help. My preference would be for the records to come out a little more random, but we are limited in whatwe can do with access.

#2
Another solution is to select * and then generate a random number in your ASP code and move forward that many records. I hate this method because pulling down all of the records when you only need a single one is a waste of resources.

#3
A third option is to do two SQL calls. The first call is a simple SELECT COUNT(fieldname) FROM tableName to get your number of rows, the second is a pair of nested selects that get you a record:

strSQL = "SELECT COUNT(idfield) FROM tableName"
Set rs = myConn.Execute(strSQL)
numRows = rs(0)

strSQL = "SELECT TOP 1 fieldName, fieldName2, etc FROM (SELECT TOP " & Round(Rnd() * numRows + 1,0) & " fieldName, fieldName2, etc FROM TableName ORDER BY idField DESC) ORDER BY idField ASC"

Basically this is getting a random number of records, starting from the bottom, then flipping it and getting the top record. We get to use a built-in random function and it appears to work a little better than the one I built above (most of the time) without the restriction of one call per second and it only retrieves a single record, but it does take two SQL calls to do it.


Anyways, hope one of these proves useful,
-T