random records are not generated in access 2000 !!!!!

[hardik]

Hi friends,

can anyone solve this query ?

I have a table which stores products id and name and then product
image now all i want is everytime i refresh a page it should display 5
diffrent images then last one

I have almost 500 records in product_master table.

here is my product_master table

pid (Autonumber) pname pimage

1 abc 1.gif
2 xyz 2.png


now from site,

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20869325.html?qid=20869325

i got this query to generate random and unique records




query = "SELECT TOP 5 Rnd(pid) AS Expr1, product_master.Text1,
product_master.Text2 FROM product_master ORDER BY Rnd(pid)"

set productimage=server.CreateObject("adodb.recordset")

productimage query,conn
do while not productimage.eof

%>
<img align="center" src="<%="images/" &
productimage.fields("imagepath")%>" border="0" />
<%


productimage.next
loop

set productimage = nothing

my query runs fine and output also shown in five records but it never
comes random always the same i need to change 5 images everytime page
refreshes,


Thank You In Advance,

Have a good day

Best Of Luck

 

[Michel Walsh]

If pid is a field name, then you may try to issue a

Randomize

before calling the query, so the seed is itself 'changed' to the timer value
(see help file), a value that is unlikely to repeat identical to itself each
time the query is run, so the random sequence will so be unlikely the same
too.


Vanderghast, Access MVP

 

[hardik]

Thank you Michel Walsh

but can you explain how to implement Randomize

as i m bit novice and i tried to put

Randomize function before query and steel nothing happens

i read the Randomize function and i understand it used to generate
numbers but how it can effect in query ?

 

[Michel Walsh]

You run it from VBA, once for all (no need to run it each time, just once is
enough), maybe in your autoexec macro (which is executed each time you load
the application)?


Hoping it may help,
Vanderghast, Access MVP

 

[hardik]

hi Michel

hope you r fine

my application is web application

i m going to execute into web server

how i can implement it into asp ?

can you give me any example or any reference article or anything i
will learn it ....

thank you for your help....

 

[Michel Walsh]

No idea, ... :-( I have never touched asp. Sounds, on the other hand,
that you may have even a bigger problem: if you do not run form ACCESS,
itself, then JET, which is the library that makes the query works, will not
like any call to VBA, including, most likely, Rnd() itself, a VBA function,
not really a JET (SQL-Jet-database) function. A possible solution is to run
the query through an msAccess object, but that implies some memory
resources, each time you create one... But maybe there is other options,
from ASP, since, again, about it, I only know that I don't I know it.

Vanderghast, Access MVP

 

[Gary Walter]

I also have never used ASP, but I think this is what you want:

http://databases.aspfaq.com/database/how-do-i-retrieve-a-random-record.html

 

[Gary Walter]

Also...I always thought this worked in Access

ORDER BY Rnd(1 + Len([sometextfield] & ""))

or

ORDER BY Rnd([autonumberPKfield])

or

ORDER BY Rnd(Asc(Left([sometextfield],1)))

but, I would not try to include the random result
in the SELECT clause -- I'm pretty sure it would
be meaningless... the ORDER BY clause is "executed"
giving one set of numbers, then the SELECT clause
reruns the Rnd to show you what should be a new set
of numbers. I could be mistaken of course...

try

query = "SELECT TOP 5 product_master.Text1,
product_master.Text2 FROM product_master
ORDER BY Rnd(1 + Len([Text2] & ''))"

This method has always worked for me in Access...

In Access, I also would make sure I explicitly closed
the recordset before trying again. I don't know how
ASP works that way...

 

[hardik]

hi friends finally this thing work for me


Randomize()
intRandomNumber = Int (1000*Rnd)+1

strSQL = _
"SELECT TOP 4 pid, pname,image, Rnd(" & -1 * (intRandomNumber) &
"*pid)" & _
"FROM product_master " & _
"ORDER BY 4"


it perfectly works for me


Thank you all of you persons