473,320 Members | 1,876 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

random record with SELECT TOP does NOT work

thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

this gives the error: "No value given for one or more required parameters".

what i would really LOVE is for someone to fill in whatever required parameter im missing, but more important i would like to understand whats going on. there are people who go through life dumb and happy once something "works", but i need to understand how and why its working. even if this code did work, im confused with the SELECT statement (no, i dont have much SQL experience aside from basic queries). what is the "r = Rnd" line doing in the middle of the querie? how am i ordering by r?? also, i dont have an indexed, primary "ID" field in this databae. is that ok for this code to work?

thank you for your help

Sep 9 '06 #1
48 4185
>oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
>TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
Do you have adovbs.inc included? Otherwise, where are adOpenStatic and
adLockReadyOnly defined?

How about

Set oRS = oConn.Execute("SELECT ... ORDER BY r")
what is the "r = Rnd" line doing in the middle of the querie?
It's generating a new random number within Access, seeded by the one you
created in the ASP code.
how am i ordering by r??
You're applying a random number to each row. TOP 1 ... ORDER BY r will give
you whatever row happened to get the lowest random number. If you don't use
ORDER BY, then you will likely get the same row over and over again.

A
Sep 9 '06 #2
ok so...

is there a difference in the way you open the recordset, with the Execute
statement and the way i do it with oRS.Open? will they both accomplish the
same thing for the purpose of this piece of code?

yes i do include adovbs.inc

and im still confused.... what is wrong with the statement as i have it
right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1
ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:us**************@TK2MSFTNGP03.phx.gbl...
>>oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Do you have adovbs.inc included? Otherwise, where are adOpenStatic and
adLockReadyOnly defined?

How about

Set oRS = oConn.Execute("SELECT ... ORDER BY r")
>what is the "r = Rnd" line doing in the middle of the querie?

It's generating a new random number within Access, seeded by the one you
created in the ASP code.
>how am i ordering by r??

You're applying a random number to each row. TOP 1 ... ORDER BY r will
give you whatever row happened to get the lowest random number. If you
don't use ORDER BY, then you will likely get the same row over and over
again.

A

Sep 9 '06 #3
[please don't toppost on USENET]

Jimmy wrote:
is there a difference in the way you open the recordset,
with the Execute statement and the way i do it with
oRS.Open?
Yes. His method is more readable, and reflects an understanding that you
don't ever want to find yourself worrying about which cursor to use because
you ought not be using anything but the static forward readonly type in a
stateless application anyway.
will they both accomplish the same thing for the purpose
of this piece of code?
Yes, and so would 100 million other things. Aaron is offering you a best
practice based on years of experience working with ASP and ADO. He has a
whole site full of valuable information for ASP developers, both new and
experienced.
and im still confused.... what is wrong with the statement as i have
it right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.
You didn't answer Aaron's question. What is r=Rnd() doing in the middle of
your SQL query?


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Sep 9 '06 #4
that was MY question.

and i still dont have this working :(

anyone?
"Dave Anderson" <NY**********@spammotel.comwrote in message
news:12*************@corp.supernews.com...
[please don't toppost on USENET]

Jimmy wrote:
>is there a difference in the way you open the recordset,
with the Execute statement and the way i do it with
oRS.Open?

Yes. His method is more readable, and reflects an understanding that you
don't ever want to find yourself worrying about which cursor to use
because you ought not be using anything but the static forward readonly
type in a stateless application anyway.
>will they both accomplish the same thing for the purpose
of this piece of code?

Yes, and so would 100 million other things. Aaron is offering you a best
practice based on years of experience working with ASP and ADO. He has a
whole site full of valuable information for ASP developers, both new and
experienced.
>and im still confused.... what is wrong with the statement as i have
it right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.

You didn't answer Aaron's question. What is r=Rnd() doing in the middle of
your SQL query?


--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.

Sep 10 '06 #5
Jimmy wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:
and i still dont have this working :(
No, it hat true?
Don't you know that topposting is frowned upon by many?
Because I like topposting.
Why don't you change to a more aggreable way of posting?
Because I toppost.
Why cann't others easily follow your thread?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Sep 10 '06 #6
what the hell are you talking about?

"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@194.109.133.242...
Jimmy wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:
>and i still dont have this working :(

No, it hat true?
Don't you know that topposting is frowned upon by many?
Because I like topposting.
Why don't you change to a more aggreable way of posting?
Because I toppost.
Why cann't others easily follow your thread?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Sep 10 '06 #7
wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:
"Evertjan." <ex**************@interxnl.netwrote in message
news:Xn********************@194.109.133.242...
>Jimmy wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:
>>and i still dont have this working :(

No, it hat true?
Don't you know that topposting is frowned upon by many?
Because I like topposting.
Why don't you change to a more aggreable way of posting?
Because I toppost.
Why cann't others easily follow your thread?
[topposting corrected]
what the hell are you talking about?
Please reread, and be polite, hgive a name.

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Sep 10 '06 #8

"Jimmy" <j@j.jwrote in message
news:uk**************@TK2MSFTNGP05.phx.gbl...
ok so...

is there a difference in the way you open the recordset, with the Execute
statement and the way i do it with oRS.Open? will they both accomplish the
same thing for the purpose of this piece of code?

yes i do include adovbs.inc

and im still confused.... what is wrong with the statement as i have it
right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1
ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.
Did you mean:-

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM TABLE1
ORDER BY r", oConn, adOpenStatic, adLockReadOnly

??
>

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message
news:us**************@TK2MSFTNGP03.phx.gbl...
>oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
Do you have adovbs.inc included? Otherwise, where are adOpenStatic and
adLockReadyOnly defined?

How about

Set oRS = oConn.Execute("SELECT ... ORDER BY r")
what is the "r = Rnd" line doing in the middle of the querie?
It's generating a new random number within Access, seeded by the one you
created in the ASP code.
how am i ordering by r??
You're applying a random number to each row. TOP 1 ... ORDER BY r will
give you whatever row happened to get the lowest random number. If you
don't use ORDER BY, then you will likely get the same row over and over
again.

A


Sep 11 '06 #9
Anthony Jones wrote:
"Jimmy" <j@j.jwrote in message
news:uk**************@TK2MSFTNGP05.phx.gbl...
>ok so...

is there a difference in the way you open the recordset, with the
Execute statement and the way i do it with oRS.Open? will they both
accomplish the same thing for the purpose of this piece of code?

yes i do include adovbs.inc

and im still confused.... what is wrong with the statement as i have
it right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.

Did you mean:-

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
You are not allowed to order by a column alias in JetSQL. However, you can
order by the ordinal position of a column:
TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Sep 11 '06 #10

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:eF**************@TK2MSFTNGP06.phx.gbl...
Anthony Jones wrote:
"Jimmy" <j@j.jwrote in message
news:uk**************@TK2MSFTNGP05.phx.gbl...
ok so...

is there a difference in the way you open the recordset, with the
Execute statement and the way i do it with oRS.Open? will they both
accomplish the same thing for the purpose of this piece of code?

yes i do include adovbs.inc

and im still confused.... what is wrong with the statement as i have
it right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.
Did you mean:-

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

You are not allowed to order by a column alias in JetSQL. However, you can
order by the ordinal position of a column:
TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly
You see this is why I don't answer Jet based questions there are always
nuances I miss. Should've stuck to my usual policy ;)
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


Sep 11 '06 #11
Bob Barrows [MVP] wrote on 11 sep 2006 in
microsoft.public.inetserver.asp.general:
You are not allowed to order by a column alias in JetSQL. However, you
can order by the ordinal position of a column:
Regarding JetSQL, Bob, do you mean that:

"SELECT * FROM TABLE1 ORDER BY 0"

orders by the first field in the database, as set up in Access,
and

"SELECT p,q,r FROM TABLE1 ORDER BY 0"

orders by field p, even if p stands 3rd in the Access setup order?
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Sep 11 '06 #12
Evertjan. wrote:
Bob Barrows [MVP] wrote on 11 sep 2006 in
microsoft.public.inetserver.asp.general:
>You are not allowed to order by a column alias in JetSQL. However,
you can order by the ordinal position of a column:

Regarding JetSQL, Bob, do you mean that:

"SELECT * FROM TABLE1 ORDER BY 0"

orders by the first field in the database, as set up in Access,
and

"SELECT p,q,r FROM TABLE1 ORDER BY 0"

orders by field p, even if p stands 3rd in the Access setup order?

I'm pretty sure the ordinal position in this context starts at 1, not 0. I'd
have to try it to be sure.
The ordinal position refers to the columns that appear in the select list,
not to the columns as they appear in the table. Of course, selstar makes the
select list equal to the Access setup order.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Sep 11 '06 #13
i wish so much that someone could make sense of this and make it work...

here is the query i have, which many people from this group have copied and
pasted from a website where i also found the query, and it does not work:

"SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TBL_SECRETS ORDER
BY r"

can anyone see why this doesnt work and possibly make it work?


"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:eF**************@TK2MSFTNGP06.phx.gbl...
Anthony Jones wrote:
>"Jimmy" <j@j.jwrote in message
news:uk**************@TK2MSFTNGP05.phx.gbl...
>>ok so...

is there a difference in the way you open the recordset, with the
Execute statement and the way i do it with oRS.Open? will they both
accomplish the same thing for the purpose of this piece of code?

yes i do include adovbs.inc

and im still confused.... what is wrong with the statement as i have
it right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.

Did you mean:-

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

You are not allowed to order by a column alias in JetSQL. However, you can
order by the ordinal position of a column:
>TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Sep 11 '06 #14
Bob Barrows [MVP] wrote on 11 sep 2006 in
microsoft.public.inetserver.asp.general:
>"SELECT p,q,r FROM TABLE1 ORDER BY 0"

orders by field p, even if p stands 3rd in the Access setup order?


I'm pretty sure the ordinal position in this context starts at 1, not
0. I'd have to try it to be sure.
The ordinal position refers to the columns that appear in the select
list, not to the columns as they appear in the table. Of course,
selstar makes the select list equal to the Access setup order.
You are right:

Microsoft JET Database Engine error '80040e14'

The Microsoft Jet database engine does not recognize '0' as a valid field
name or expression.

[Still a bit strange that field 0 in the star sense is field 1.]

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Sep 11 '06 #15
Evertjan. wrote:
>
[Still a bit strange that field 0 in the star sense is field 1.]
It's znzlogous to the Ubound and LBound functions: the dimension
identifiers start at 1, not 0.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Sep 11 '06 #16
Bob Barrows [MVP] wrote:
Evertjan. wrote:
>>
[Still a bit strange that field 0 in the star sense is field 1.]
It's znzlogous
Errmmm, that should, of course, be "analogous"
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Sep 11 '06 #17

"Jimmy" <j@j.jwrote in message
news:uT**************@TK2MSFTNGP06.phx.gbl...
i wish so much that someone could make sense of this and make it work...

here is the query i have, which many people from this group have copied
and
pasted from a website where i also found the query, and it does not work:

"SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TBL_SECRETS
ORDER
BY r"

can anyone see why this doesnt work and possibly make it work?
Hello, Hello... is this mic on??

Have you read any of the responses to your question??
>
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:eF**************@TK2MSFTNGP06.phx.gbl...
Anthony Jones wrote:
"Jimmy" <j@j.jwrote in message
news:uk**************@TK2MSFTNGP05.phx.gbl...
ok so...

is there a difference in the way you open the recordset, with the
Execute statement and the way i do it with oRS.Open? will they both
accomplish the same thing for the purpose of this piece of code?

yes i do include adovbs.inc

and im still confused.... what is wrong with the statement as i have
it right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.

Did you mean:-

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
You are not allowed to order by a column alias in JetSQL. However, you
can
order by the ordinal position of a column:
TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"


Sep 11 '06 #18
yes and none have given a clear answer.
its funny but the code i originally found on the aspfaq site is apparently
what everyone uses to answer a question regarding random records in access,
however it doesnt work!! LOL... pretty funny actually. but yes, i have
confirmed that it does not work as written, and all of the "guru's" that
were so quick to have an attitude in the beginning have since shut up
because they cant look at the SQL query and figure out whats wrong with it.
oh well, ill continue to use the function that i wrote which works fine. and
if anyone else would like to point out how inefficient my function is, and
offer another solution, have the sense to test it first!
"Anthony Jones" <An*@yadayadayada.comwrote in message
news:eQ**************@TK2MSFTNGP02.phx.gbl...
>
"Jimmy" <j@j.jwrote in message
news:uT**************@TK2MSFTNGP06.phx.gbl...
>i wish so much that someone could make sense of this and make it work...

here is the query i have, which many people from this group have copied
and
>pasted from a website where i also found the query, and it does not work:

"SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TBL_SECRETS
ORDER
>BY r"

can anyone see why this doesnt work and possibly make it work?

Hello, Hello... is this mic on??

Have you read any of the responses to your question??
>>
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcomwrote in message
news:eF**************@TK2MSFTNGP06.phx.gbl...
Anthony Jones wrote:
"Jimmy" <j@j.jwrote in message
news:uk**************@TK2MSFTNGP05.phx.gbl...
ok so...

is there a difference in the way you open the recordset, with the
Execute statement and the way i do it with oRS.Open? will they both
accomplish the same thing for the purpose of this piece of code?

yes i do include adovbs.inc

and im still confused.... what is wrong with the statement as i have
it right here:

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

it seems like just what youre doing, but it doesnt work.

Did you mean:-

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
You are not allowed to order by a column alias in JetSQL. However, you
can
order by the ordinal position of a column:
TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"



Sep 11 '06 #19

Jimmy wrote:
thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>
That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.

Here's the real answer

SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

where mytable is your table that contains email_address and ID. ID is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you get a
different value for the 2nd column on each row. ORDER BY 2 orders by
the 2nd column listed.

NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread, they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.

Hope this helps.

Sep 11 '06 #20
Jimmy wrote:
yes and none have given a clear answer.
its funny but the code i originally found on the aspfaq site is
apparently what everyone uses to answer a question regarding random
records in access, however it doesnt work!! LOL... pretty funny
actually. but yes, i have confirmed that it does not work as written,
and all of the "guru's" that were so quick to have an attitude in the
beginning have since shut up because they cant look at the SQL query
and figure out whats wrong with it. oh well, ill continue to use the
function that i wrote which works fine. and if anyone else would like
to point out how inefficient my function is, and offer another
solution, have the sense to test it first!
We have shut up because you spend more time whining that "it doesn't work"
than giving useful details that would help us help you.

I have to say I lost interest in helping you when you admitted on your
*12th* message that you still didn't have a DB up and running with which to
test all of the advice you were getting.

Meet us halfway, and we can be an accommodating bunch. Make us work to give
you advice, and you will get what you paid for: nothing.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Sep 11 '06 #21

"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
>
Jimmy wrote:
>thanks to everyone that helped, unfortunately the code samples people
gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.

Here's the real answer

SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

where mytable is your table that contains email_address and ID. ID is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you get a
different value for the 2nd column on each row. ORDER BY 2 orders by
the 2nd column listed.

NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread, they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.

Hope this helps.

thank you. ill try this and report back...
Sep 11 '06 #22

"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...
>
Jimmy wrote:
>thanks to everyone that helped, unfortunately the code samples people
gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.

Here's the real answer

SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

where mytable is your table that contains email_address and ID. ID is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you get a
different value for the 2nd column on each row. ORDER BY 2 orders by
the 2nd column listed.

NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread, they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.

Hope this helps.
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
Sep 11 '06 #23
I wrote:
We have shut up because you spend more time whining that "it
doesn't work" than giving useful details that would help us
help you...
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()

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Sep 11 '06 #24

"Dave Anderson" <NY**********@spammotel.comwrote in message
news:em**************@TK2MSFTNGP02.phx.gbl...
>I wrote:
>We have shut up because you spend more time whining that "it
doesn't work" than giving useful details that would help us
help you...

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()

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.
i know... was out of my control unfortunately. i dont make those calls :(
Sep 11 '06 #25

Jimmy wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...

Jimmy wrote:
thanks to everyone that helped, unfortunately the code samples people
gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>
That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.

Here's the real answer

SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

where mytable is your table that contains email_address and ID. ID is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you get a
different value for the 2nd column on each row. ORDER BY 2 orders by
the 2nd column listed.

NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread, they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.

Hope this helps.

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
Did you the instructions and add an "ID" column, and populate it with
unique records?

Works on my end in Access 2003.

Sep 12 '06 #26

"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
>
Jimmy wrote:
>"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@i3g2000cwc.googlegro ups.com...
>
Jimmy wrote:
thanks to everyone that helped, unfortunately the code samples people
gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.

Here's the real answer

SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

where mytable is your table that contains email_address and ID. ID is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you get a
different value for the 2nd column on each row. ORDER BY 2 orders by
the 2nd column listed.

NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread, they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.

Hope this helps.

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

Did you the instructions and add an "ID" column, and populate it with
unique records?

Works on my end in Access 2003.


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

Sep 12 '06 #27
Jimmy wrote on 12 sep 2006 in microsoft.public.inetserver.asp.general:
<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Where do you use the randNum ???
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")
What is the expecter behavour of rnd(ID)?

I think(!) it will randomize from the same seed every time.
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...
Right see above.
either way, i can probably delete the
"randNum" line, correct? but this still doesnt work...
You still need to seed the rnd()

try:

SQL =
"SELECT TOP 1 EMAIL_ADDRESS, Rnd("&randNum&") FROM TABLE1 ORDER BY 2"

as suggested before.
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Sep 12 '06 #28
Evertjan. wrote:
You still need to seed the rnd()

try:

SQL =
"SELECT TOP 1 EMAIL_ADDRESS, Rnd("&randNum&") FROM TABLE1 ORDER BY 2"

as suggested before.
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

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Sep 12 '06 #29

Jimmy wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...

Jimmy wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@i3g2000cwc.googlegrou ps.com...

Jimmy wrote:
thanks to everyone that helped, unfortunately the code samples people
gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.

Here's the real answer

SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

where mytable is your table that contains email_address and ID. ID is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you get a
different value for the 2nd column on each row. ORDER BY 2 orders by
the 2nd column listed.

NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread, they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.

Hope this helps.


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
Did you the instructions and add an "ID" column, and populate it with
unique records?

Works on my end in Access 2003.

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

--
Mike Brind

Sep 13 '06 #30
Dave Anderson wrote on 13 sep 2006 in
microsoft.public.inetserver.asp.general:
http://en.wikipedia.org/wiki/KISS_Principle
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>

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)
Sep 13 '06 #31

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@e63g2000cwd.googlegr oups.com...
>
Jimmy wrote:
>"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@p79g2000cwp.googlegr oups.com...
>
Jimmy wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11*********************@i3g2000cwc.googlegro ups.com...

Jimmy wrote:
thanks to everyone that helped, unfortunately the code samples
people
gave me don't work. here is what i have so far:

<%
Dim oConn, oRS, randNum
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.

Here's the real answer

SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

where mytable is your table that contains email_address and ID. ID
is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you
get a
different value for the 2nd column on each row. ORDER BY 2 orders
by
the 2nd column listed.

NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to
a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread,
they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.

Hope this helps.
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

Did you the instructions and add an "ID" column, and populate it with
unique records?

Works on my end in Access 2003.

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

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.

--
Mike Brind

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?
Sep 13 '06 #32
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?

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...m-caching.html
Sep 13 '06 #33

"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:Ok**************@TK2MSFTNGP06.phx.gbl...
>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?


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...m-caching.html
;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
Sep 13 '06 #34
access 2003. i removed the order by clause because im now only returning 1
row... my thought was that theres nothing to order by(?)
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.
what should i be ordering by? ill try it right away
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
Sep 13 '06 #35

Jimmy wrote:
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in message
news:Ok**************@TK2MSFTNGP06.phx.gbl...
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?

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...m-caching.html

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

--
Mike Brind

Sep 13 '06 #36

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...
>
Jimmy wrote:
>"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message
news:Ok**************@TK2MSFTNGP06.phx.gbl...
>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?

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...m-caching.html

;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

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.

--
Mike Brind

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
Sep 13 '06 #37

Jimmy wrote:
"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googlegr oups.com...

Jimmy wrote:
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message
news:Ok**************@TK2MSFTNGP06.phx.gbl...
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?

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...m-caching.html


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

--
Mike Brind


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

--
Mike Brind

Sep 13 '06 #38

"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@e63g2000cwd.googlegr oups.com...
>
Jimmy wrote:
>"Mike Brind" <pa*******@hotmail.comwrote in message
news:11**********************@i42g2000cwa.googleg roups.com...
>
Jimmy wrote:
"Aaron Bertrand [SQL Server MVP]" <te*****@dnartreb.noraawrote in
message
news:Ok**************@TK2MSFTNGP06.phx.gbl...
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?

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...m-caching.html
;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

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.

--
Mike Brind


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)

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

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

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.

--
Mike Brind

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
Sep 13 '06 #39
Mike Brind wrote:
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.
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 & "))")

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Sep 13 '06 #40
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!

"Dave Anderson" <NY**********@spammotel.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...
Mike Brind wrote:
>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.

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 & "))")

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.

Sep 14 '06 #41

Jimmy wrote:
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!
And after THAT comment, let's just see how much help you get in future.

--
Mike Brind

Sep 14 '06 #42
Jimmy wrote:
now THAT was an intelligent answer from someone who clearly
understands every piece of the SQL string he provided as an
example.
Only if you classify "I don't know much, but this is what I observed" as
intelligent.

I need to point out the fact that before this thread, I had never used
Access (unless you count exporting into SQL Server), so there was much about
it that I did not understand. For example, I had no idea that you could call
VBA functions inside your SQL statement. But then I saw all the examples
with Rnd(), which I could not find in the Access SQL reference, so I went
digging. The only reference to Rnd() was in the VBA function list, so I read
its documentation
(http://msdn.microsoft.com/library/en...l/vafctRnd.asp) and
drew more of my conclusions from that, such as the notion that I should
change the sign on the inner Rnd.

In short, I knew about as much about your desired task as you did. I don't
work in Access, I don't use VBScript for ASP unless forced to -- I am a
virtual novice in each. But I used my intellectual curiosity to overcome
this. And I can't understand why you did not do so yourself. If you want to
know if something works, try it. Don't ask us if it will work. When you come
across a problem, SIMPLIFY to the smallest case that will duplicate that
problem. These are fundamental debugging skills. And there is one more that
I find most important of all: http://en.wikipedia.org/wiki/RTFM.
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!
Count me among them, then. It is a rare article on aspfaq.com that contains
non-functioning examples. When someone claims he tried the example from
Aaron's site and it "doesn't work", I assume he has not implemented it
properly. There are dozens of reasons for this assumption, not least of
which is the newbie signature "it doesn't work".

Oh, and how was "simply pasting something they found on a website, not
understanding exactly what it was doing" any different from what you were
doing? There were real answers provided in many of those responses, but I
don't think you saw past the fact that pasting them into your script
verbatim did not produce your desired result.

I do not post here in order to do your work for you. I helped you because
you did not understand a concept. My goal was not for you to solve your
problem, but rather for you to understand how to solve the problem yourself.
I truly believe that most of the contributors in this thread share that
goal.

You would be well served to calm down and re-read the thread to see what
else you might pick up. Leave the ad hominem stuff out of this forum.

--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Sep 14 '06 #43
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!
Right, we were the ones not understanding what it was doing.

Something I should have done about 50 messages ago:

*PLONK*

Good luck with your next moronic thread here.
Sep 14 '06 #44
>
"Dave Anderson" <NY**********@spammotel.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...
Mike Brind wrote:
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.
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


Jimmy wrote:
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!
You're unwelcome, jackass.

Sep 14 '06 #45

"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
>
>>
"Dave Anderson" <NY**********@spammotel.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...
Mike Brind wrote:
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.

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

Jimmy wrote:
>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!

You're unwelcome, jackass.
i wasnt thanking you, dumbass.

after all, youre the genius that said:

"heres the real answer:"
SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2

nice guess, but as you are now aware, that couldnt be more wrong (or stupid)

maybe find a new group to offer help in?
Sep 14 '06 #46

SEVEN wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
>
"Dave Anderson" <NY**********@spammotel.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...
Mike Brind wrote:
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.

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


Jimmy wrote:
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!
You're unwelcome, jackass.

i wasnt thanking you, dumbass.

after all, youre the genius that said:

"heres the real answer:"
SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2

nice guess, but as you are now aware, that couldnt be more wrong (or stupid)

maybe find a new group to offer help in?
Larry's example worked perfectly. It did exactly what you wanted it to
do. The fact that you still don't get that reinforces just how stupid
you really are. You have demonstrated beyond any doubt whatsoever many
times that you are not capable of discerning what is right and wrong
here.

I suggest that you not only find another group to help you, but another
subject - one that is within your very obviously limited intellectual
capabilities. Either that or a therapy group.

What a loser.

--
Mike Brind

Sep 14 '06 #47
I wrote:
Working from the tried & true SQL Server favorite...
[snip]
...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 & "))")
Just to put the final nail in this thread, I think it is fair to point out
that my analysis is thoroghly incomplete. I did no comparison between using
a column-based ordering and my solution, nor did I examine anything but a
simple example. My solution may be acceptible for a table with 9 records,
each with an identity under 10, but it made no attempt to do the same for a
larger table with sequence gaps and a wide range of identities.

To illustrate the danger of relying on limited analysis, I offer this
observation: When run against Northwind's [Orders] table (approximately 830
unique IDs), the distribution is horribly skewed toward a small handful of
values. Over a run of 10,000, I only hit about 700 values, with most in
single digits for frequency, but five values in excess of 100 -- one of
those values appearing 343 times!

Just to be sure this was not an aberration, I ran it again. These are the
top 10 values, ordered by frequency:

Run 1 Run 2
========= =========
10700 343 10700 355
10497 257 11005 224
11005 217 10497 223
10561 156 10561 167
10601 140 10601 133
10397 81 11016 85
11022 80 10397 83
11016 78 11022 82
11017 72 10843 66
10714 60 11017 66

Those are virtually the same values. Moreover, a flat distribution would
have yielded 830 values with roughly 12 appearances each. So my suggestion
is far from ideal for this data set.

There *is* a better way to get a random distribution. This one is as good as
your random number generator. Oddly enough, it turned in better performance
than my earlier suggestion. It is in JScript, so the distribution depends on
Math.random():

Step 1: Get a count
RS = CN.Execute("SELECT Count(*) AS RecordCount FROM Orders")
rc = RS.Fields("RecordCount").Value

Step 2: Use top 1 DESC from top N ASC to get your row
RS = CN.Execute("SELECT TOP 1 * FROM Orders WHERE OrderID IN (SELECT TOP
" + Math.ceil(rc*Math.random()) + " OrderID FROM Orders ORDER BY OrderID
ASC) ORDER BY OrderID DESC")

Run 50,000 times against [Orders], I got minimum frequency 35 and maximum
88. All 830 values were covered. Using VBScript and Rnd**, results were
similar, with frequencies ranging from 38 to 90.

I have no more to say on the topic. I think.
** What a pain in the posterior THAT was. It took me quite some time to
figure out that I needed to use CSng() on the recordcount to multiply
against Rnd without an overflow. Never mind that it took twice as many lines
of code. Who needs that nonsense?
--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.
Sep 14 '06 #48

SEVEN wrote:
"Larry Bud" <la**********@yahoo.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
>
"Dave Anderson" <NY**********@spammotel.comwrote in message
news:O5**************@TK2MSFTNGP02.phx.gbl...
Mike Brind wrote:
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.

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


Jimmy wrote:
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!
You're unwelcome, jackass.

i wasnt thanking you, dumbass.

after all, youre the genius that said:

"heres the real answer:"
SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2

nice guess, but as you are now aware, that couldnt be more wrong (or stupid)
Yep, my example works perfectly. The fact that you don't have the
ability to apply it to your situation doesn't say anything about me.

But believe me, I'll make us both happy and I won't hesitate to skip
over any other help you ask for in the future.

Sep 18 '06 #49

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Jamie Fryatt | last post by:
Hi all, im a little bit new to all this so i you could help me a little i would be greatful. How do i select a completly random record from a database? sql = select (random) from tbl, sort of...
2
by: Ini | last post by:
Hi, Is there an easy way to pick at random one record out of a recordset and then leave this recordset? I was thinking about doing a recordcount of the recordset, then find at random the...
7
by: Bill | last post by:
Hello, I am trying to use a SQL Query to return a random record from an Access 2000 Database. I am using: SELECT TOP 1 Example FROM TABLE ORDER BY Rnd;
6
by: D. Shane Fowlkes | last post by:
I'm surprised I haven't found a clip of code or a tutorial on this.....I've thumbed through a couple of books, did some Google searching for about 15 minutes and still no luck. =( I'm simply...
1
by: Steven Smith | last post by:
Hi Guys How do I check whether or not a specific record number in an initialised random access file is empty or not ? What I need to do is when DisplayButton.Click event is triggered the...
9
by: Jeremy | last post by:
I have a situation where i am trying to run two query's and retrieve one record from each query, then using Union combine them into one recordset. The First Query is strait forward and should just...
26
by: Jimmy | last post by:
ill have a database with 1 table and 3 fields: ID FIRSTNAME LASTNAME (the ID field will be the auto incrementing index) there might be 10 records in the DB, there might be 10,000. i...
7
by: FrankEBailey | last post by:
I'm not sure if this is a completely dumb question, but please humor me :) I have a table of records, called Records, each of which has a Category_ID that places it in a specific category; the...
3
by: John Fairhurst | last post by:
Hi, The following code should select the specified number of records randomly from the database <% .... query = "SELECT FROM " Set RS = Server.CreateObject("ADODB.Recordset")
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.