random record with SELECT TOP does NOT work

[Jimmy]

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.Cr eateObject("ADO DB.Connection")
Set oRS=Server.Crea teObject("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_ADDR ESS")

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

[Aaron Bertrand [SQL Server MVP]]

Re: random record with SELECT TOP does NOT work

>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

[Jimmy]

Re: random record with SELECT TOP does NOT work

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]" <ten.xoc@dnartr eb.noraawrote in message
news:usLQrgA1GH A.4632@TK2MSFTN GP03.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
>

[Dave Anderson]

Re: random record with SELECT TOP does NOT work

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

[Jimmy]

Re: random record with SELECT TOP does NOT work

that was MY question.

and i still dont have this working :(

anyone?


"Dave Anderson" <NYRUMTPELVWH@s pammotel.comwro te in message
news:12g5o45mkf uu214@corp.supe rnews.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.
>

[Evertjan.]

Re: random record with SELECT TOP does NOT work

Jimmy wrote on 10 sep 2006 in microsoft.publi c.inetserver.as p.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)

[Guest]

Re: random record with SELECT TOP does NOT work

what the hell are you talking about?



"Evertjan." <exjxw.hannivoo rt@interxnl.net wrote in message
news:Xns983A659 7D4419eejj99@19 4.109.133.242.. .

Jimmy wrote on 10 sep 2006 in microsoft.publi c.inetserver.as p.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)

[Evertjan.]

Re: random record with SELECT TOP does NOT work

wrote on 10 sep 2006 in microsoft.publi c.inetserver.as p.general:

"Evertjan." <exjxw.hannivoo rt@interxnl.net wrote in message
news:Xns983A659 7D4419eejj99@19 4.109.133.242.. .

>Jimmy wrote on 10 sep 2006 in microsoft.publi c.inetserver.as p.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)

[Anthony Jones]

Re: random record with SELECT TOP does NOT work


"Jimmy" <j@j.jwrote in message
news:ukG$PuB1GH A.3900@TK2MSFTN GP05.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]" <ten.xoc@dnartr eb.noraawrote in

message

news:usLQrgA1GH A.4632@TK2MSFTN GP03.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

>
>

[Bob Barrows [MVP]]

Re: random record with SELECT TOP does NOT work

Anthony Jones wrote:

"Jimmy" <j@j.jwrote in message
news:ukG$PuB1GH A.3900@TK2MSFTN GP05.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"

[Anthony Jones]

Re: random record with SELECT TOP does NOT work


"Bob Barrows [MVP]" <reb01501@NOyah oo.SPAMcomwrote in message
news:eFyvdEZ1GH A.1040@TK2MSFTN GP06.phx.gbl...

Anthony Jones wrote:

"Jimmy" <j@j.jwrote in message
news:ukG$PuB1GH A.3900@TK2MSFTN GP05.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"
>
>

[Evertjan.]

Re: random record with SELECT TOP does NOT work

Bob Barrows [MVP] wrote on 11 sep 2006 in
microsoft.publi c.inetserver.as p.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)

[Bob Barrows [MVP]]

Re: random record with SELECT TOP does NOT work

Evertjan. wrote:

Bob Barrows [MVP] wrote on 11 sep 2006 in
microsoft.publi c.inetserver.as p.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"

[Jimmy]

Re: random record with SELECT TOP does NOT work

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]" <reb01501@NOyah oo.SPAMcomwrote in message
news:eFyvdEZ1GH A.1040@TK2MSFTN GP06.phx.gbl...

Anthony Jones wrote:

>"Jimmy" <j@j.jwrote in message
>news:ukG$PuB1G HA.3900@TK2MSFT NGP05.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"
>

[Evertjan.]

Re: random record with SELECT TOP does NOT work

Bob Barrows [MVP] wrote on 11 sep 2006 in
microsoft.publi c.inetserver.as p.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)