J
Jimmy
Larry Bud said:
thank you. ill try this and report back...
thank you. ill try this and report back...
J
Jimmy
Larry Bud said:
ok, your code always displays the 4th record. here is the entire code from
my test page (thank you for your time, by the way)
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
Set oRS=oConn.Execute("SELECT TOP 1 EMAIL_ADDRESS, Rnd(ID) FROM TABLE1 ORDER
BY 2")
Response.Write oRS("EMAIL_ADDRESS")
oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
D
Dave Anderson
I said:
One more thing -- you never really explained why you switched from SQL
Server to Access. This whole thing is *trivial* in SQL Server:
SELECT ...
... ORDER BY NEWID()
J
Jimmy
Dave Anderson said:
i know... was out of my control unfortunately. i dont make those calls
L
Larry Bud
Jimmy said:
Did you the instructions and add an "ID" column, and populate it with
unique records?
Works on my end in Access 2003.
J
Jimmy
Larry Bud said:
well thats weird. yes, i have a column named "ID" that is the primary key
and autoincrements. there are only 4 records in the database, the ID field
is unique in each record. here is the code in my ASP page:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
Set oRS=oConn.Execute("SELECT TOP 1 EMAIL_ADDRESS, Rnd(ID) FROM TABLE1 ORDER
BY 2")
Response.Write oRS("EMAIL_ADDRESS")
oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>
this displays the second record ALWAYS. im thinking something to do with the
Rnd(ID) function... either way, i can probably delete the "randNum" line,
correct? but this still doesnt work...
E
Evertjan.
Jimmy wrote on 12 sep 2006 in microsoft.public.inetserver.asp.general:
Where do you use the randNum ???
What is the expecter behavour of rnd(ID)?
I think(!) it will randomize from the same seed every time.
Right see above.
You still need to seed the rnd()
try:
SQL =
"SELECT TOP 1 EMAIL_ADDRESS, Rnd("&randNum&") FROM TABLE1 ORDER BY 2"
as suggested before.
Where do you use the randNum ???
What is the expecter behavour of rnd(ID)?
I think(!) it will randomize from the same seed every time.
Right see above.
You still need to seed the rnd()
try:
SQL =
"SELECT TOP 1 EMAIL_ADDRESS, Rnd("&randNum&") FROM TABLE1 ORDER BY 2"
as suggested before.
D
Dave Anderson
Evertjan. said:
I agree. Moreover, when you are really struggling, it helps to construct and
tweak a standalone SQL statement in the tool (Access, in this case) before
ever committing it to a script.
http://en.wikipedia.org/wiki/KISS_Principle
M
Mike Brind
Jimmy said:
Try this approach. It's from the same guy who apparently supplied
aspfaq.com (Ken Schaefer) with the erroneous code, but this one works:
http://www.adopenstatic.com/faq/RandomRecord.asp
Larry's code also works for me.
E
Evertjan.
Dave Anderson wrote on 13 sep 2006 in
microsoft.public.inetserver.asp.general:
As Edsger Wybe Dijkstra (1930-2002),
the famours opponent of the "goto" statement,
said:
"Simplicity is a prerequisite voor reliability"
<http://en.wikipedia.org/wiki/Edsger_Dijkstra>
microsoft.public.inetserver.asp.general:
As Edsger Wybe Dijkstra (1930-2002),
the famours opponent of the "goto" statement,
said:
"Simplicity is a prerequisite voor reliability"
<http://en.wikipedia.org/wiki/Edsger_Dijkstra>
J
Jimmy
Mike Brind said:
this is killing me....
here is the code from that link:
' Initialize ASP RND() function
Randomize()
intRandomNumber = Int (1000*Rnd)+1
' Return 3 random records
strSQL = _
"SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumber) &
"*TableID)" & _
"FROM Table1 " & _
"ORDER BY 3"
Set objRS = objConn.Execute(strSQL)
im trying to make it work with a table that has an ID column, and an
EMAIL_ADDRESS column, and i need it to only return 1 row.
here is my code:
Dim oConn, oRS, intRandomNumber
Randomize()
intRandomNumber = Int(1000*Rnd)+1
Set oConn=Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")
Set oRS=oConn.Execute("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
(intRandomNumber) & "*ID) FROM TABLE1")
Response.Write oRS("EMAIL_ADDRESS")
oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
and guess what? ALWAYS returns the same email address. what am i doing wrong
here?
A
Aaron Bertrand [SQL Server MVP]
this is killing me....
Are we still on this? Is this the thread that will never end?
Your code is not the same as the article's. Where's your ORDER BY? Without
an ORDER BY clause, Access is going to return you the rows in the order it
deems appropriate. Unless there is heavy modification to the table between
runs, this is not going to change.
What version of Access are you using? Maybe most of us are trying the code
on a newer version which handles Rnd() better/correctly.
Is it possible the page is caching in your browser?
http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html
Are we still on this? Is this the thread that will never end?
Your code is not the same as the article's. Where's your ORDER BY? Without
an ORDER BY clause, Access is going to return you the rows in the order it
deems appropriate. Unless there is heavy modification to the table between
runs, this is not going to change.
What version of Access are you using? Maybe most of us are trying the code
on a newer version which handles Rnd() better/correctly.
Is it possible the page is caching in your browser?
http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html
J
Jimmy
Aaron Bertrand said:
;o)
access 2003. i removed the order by clause because im now only returning 1
row... my thought was that theres nothing to order by(?)
what should i be ordering by? ill try it right away
A
Aaron Bertrand [SQL Server MVP]
access 2003. i removed the order by clause because im now only returning 1
Have you not been paying attention at all?
The whole purpose of the random number is to apply a random number to each
row. Then, to pick a random row, you select TOP 1 and order by the random
number. See? Every time it runs, rows will get a different distribution of
random numbers.
UH, JUST LIKE THE CODE SAMPLE. You are ordering by the random number, which
is the 3rd column, so copy and paste:
ORDER BY 3
Have you not been paying attention at all?
The whole purpose of the random number is to apply a random number to each
row. Then, to pick a random row, you select TOP 1 and order by the random
number. See? Every time it runs, rows will get a different distribution of
random numbers.
UH, JUST LIKE THE CODE SAMPLE. You are ordering by the random number, which
is the 3rd column, so copy and paste:
ORDER BY 3
M
Mike Brind
Jimmy said:
As Aaron effectively pointed out, how can you possibly expect code
samples to work correctly if you modify them based on what you *think*
looks right? You may well say that you still don't fully understand
the theory behind how it all works (although enough explanations have
been given here), and if that is the case, that's even less reason to
tinker with the sample.
In any database-related code sample, the only things you should be
looking to change are the connection string, db name, table, and column
names to suit your environment. Changing the fundamental SQL statement
that the code sample offers is totally pointless.
Understanding the theory is, of course important. But I would suggest
that since you are obviously not going through any formal learning
here, you should focus on getting it to work. The theory side of it
will all fall into place as you go along.
J
Jimmy
Mike Brind said:
thank you. this sql string appears to work:
SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 * (intRandomNumber) & "*ID) FROM
TBL_SECRETS ORDER BY 3
however i have seen so many different examples and variations on this string
(most of which were wrong and did not work) that i obviously started playing
myself. my confusion here was that i did not know that ORDER BY 3 ordered by
the 3rd column, i thought it was ordering by the number 3, which i did not
understand.
if you go back and look at some of the other examples given, there are a few
where the ID column was not selected, and was not part of the multiplication
in the Rnd function. so my question is, do i NEED to select this column?
(many people here obviously thought i didnt) so i just want to make this
string as simple as possible.
thank you
M
Mike Brind
Jimmy said:
The only one I couldn't get to work was the one on aspfaq.com, (which
is unusual). It may be that it works in previous versions of Access.
I don't know.
A full and complete explanation of this was provided by Bob Barrows
very early on in this thread, where he discusses the fact that you
can't order by an alias in Jet SQL, so you need to order by the ordinal
position of the calculated column. Aaron has clarified why you need to
order by the calculated column a number of times in all the threads you
have started on this topic.
No, you only need to select the column(s) that you want to display AND
the calculated column. Hopefully, you will by now understand that if
you omit the ID column from your SELECT clause, you will only be
selecting 2 columns, and therefore the calculated column will be the
2nd of these. Consequently you will have to amend your ORDER BY to 2.
Equally, if you ADD extra columns to your SELECT clause, put them
before Rnd(" & -1 * (intRandomNumber) & "*ID), and amend the position
of the column to ORDER BY accordingly.
I didn't know any of this before your questions started, because I have
never needed to. Everything I have picked up has been from the
relevant threads. Bob, Aaron and others have explained all this
clearly enough, and often enough that, as Aaron said, this thread
should have been over long ago. It probably would have been if you'd
managed to keep Bob on your side. But you messed that up too.
J
Jimmy
Mike Brind said:
last post on this topic....
just tell me if this looks ok.
i have a stored query named "sp_random" that looks like this:
SELECT TOP 1 EMAIL_ADDRESS, Rnd(@intRandomNumber * ID)
FROM TABLE1
ORDER BY 2;
then this ASP code:
Randomize()
intRandomNumber = (CInt(1000 * Rnd) + 1) * -1
Set oRS=oConn.Execute("EXEC sp_random " & intRandomNumber)
this appears to work, but just wanted to make sure you didnt see problems
with it
thank you
D
Dave Anderson
Mike said:
I generally try to stay out of Access threads, but I have been following
this one, and I have to ask why anyone even bothers to create a column for
the random value. It is not required.
Working from the tried & true SQL Server favorite...
SELECT ... ORDER BY NEWID()
....I wondered if the same would work in Access. So I opened a connection to
the Northwind database and tried:
RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(EmployeeID*" + -Math.random() + ")"
)
This is obviously in JScript, but the principle is the same -- There is no
need to squabble over the column number, since we do not return the random
number in a column.
This particular example is a poor random generator -- I ran this 10,000
times and got a distribution like this:
Record 1 : 100
Record 2 : 122
Record 3 : 1526
Record 4 : 1584
Record 5 : 1302
Record 6 : 1412
Record 7 : 1232
Record 8 : 1487
Record 9 : 1235
When normalized so the highest value is 100, this has a standard deviation
of 36.64. Not good. But when I added another Rnd to the mix, the
distribution got flatter:
RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(Rnd(EmployeeID*" + -Math.random() + "))"
)
Record 1 : 1462
Record 2 : 1389
Record 3 : 1115
Record 4 : 937
Record 5 : 982
Record 6 : 1027
Record 7 : 1094
Record 8 : 996
Record 9 : 998
Normalized standard deviation: 12.81. I ran this test a couple of times, and
noticed a slight bias toward record 1, so I jacked it up to 50,000 (this
required me to pump up Server.ScriptTimeout), and got this:
7504
7282
5350
4723
4818
5329
5158
4825
5011
(Std dev: 14.21). There definitely seems to be a bias toward the lowest
values. I made one last adjustment (sign change on inner Rnd)...
RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(-Rnd(EmployeeID*" + -Math.random() + "))"
)
....and ran it 180,000 times. This yielded the following distribution:
20529
22977
20013
17616
19926
21250
20085
17665
19939
This is much better, with normalized standard deviation of 7.19. Probably
random enough for most needs. My conclusion? Jimmy could get by with this:
Randomize()
Set oRS=oConn.Execute("SELECT TOP 1 EMAIL_ADDRESS FROM TABLE1 ORDER BY
Rnd(-Rnd(ID*" & -Rnd & "))")
J
Jimmy
now THAT was an intelligent answer from someone who clearly understands
every piece of the SQL string he provided as an example.
all of the early posters to this topic were simply pasting something they
found on a website, not understanding exactly what it was doing, yet still
having the nerve to have a holier than thou attitude!
thank you!
every piece of the SQL string he provided as an example.
all of the early posters to this topic were simply pasting something they
found on a website, not understanding exactly what it was doing, yet still
having the nerve to have a holier than thou attitude!
thank you!