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

H

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
 
M

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
 
H

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 ?
 
M

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
 
H

hardik

hi Michel

hope you r fine :D

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....
 
M

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
 
G

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...
 
J

Jamie Collins

my application is web application

i m going to execute into web server

how i can implement it into asp ?
Click to expand...

Problems:

The RAND() function doesn't get seeded:

SELECT RND()

Calling the above always returns the same value.

The RND() function cannot be seeded:

SELECT Randomize()

The above errors because, presumably, Jet's expressions service does
not support this function (i.e. it's not a scalar expression).

The internal clock doesn't refresh often enough:

SELECT (NOW() * 100000) - INT(NOW() * 100000)

The above is useful if you need to use the same random number in the
same query but not so useful is that the value may remain the same for
the next query.

One possible approach is to have a working table with a random
autonumber column. Although you can also use SELECT @@IDENTITY to just
get the value, having the value already in a table has advantages e.g.
can be joined using a CROSS JOIN to use the same value in the same
query/multiple queries then a DELETE+INSERT to 'reseed'. If you want
to roll your own pseudo random numbers (i.e. store the last/next
generate number in a one-row table), google using the following
keywords: additive congruential sql.

Jamie.

--
 
H

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
 
Top