Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > random record with SELECT TOP does NOT work

Reply
Thread Tools

random record with SELECT TOP does NOT work

 
 
Jimmy
Guest
Posts: n/a
 
      09-09-2006
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

 
Reply With Quote
 
 
 
 
Aaron Bertrand [SQL Server MVP]
Guest
Posts: n/a
 
      09-09-2006
>> 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


 
Reply With Quote
 
 
 
 
Jimmy
Guest
Posts: n/a
 
      09-09-2006
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]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>>> 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
>



 
Reply With Quote
 
Jimmy
Guest
Posts: n/a
 
      09-10-2006
that was MY question.

and i still dont have this working

anyone?


"Dave Anderson" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> [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.
>



 
Reply With Quote
 
Evertjan.
Guest
Posts: n/a
 
      09-10-2006
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)
 
Reply With Quote
 
Guest
Posts: n/a
 
      09-10-2006
what the hell are you talking about?



"Evertjan." <(E-Mail Removed)> wrote in message
news:Xns983A6597D4419eejj99@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)



 
Reply With Quote
 
Evertjan.
Guest
Posts: n/a
 
      09-10-2006
wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:

> "Evertjan." <(E-Mail Removed)> wrote in message
> news:Xns983A6597D4419eejj99@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)
 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      09-11-2006

"Jimmy" <(E-Mail Removed)> wrote in message
news:ukG$(E-Mail Removed)...
> 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]" <(E-Mail Removed)> wrote in

message
> news:(E-Mail Removed)...
> >>> 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
> >

>
>



 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      09-11-2006
Anthony Jones wrote:
> "Jimmy" <(E-Mail Removed)> wrote in message
> news:ukG$(E-Mail Removed)...
>> 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"


 
Reply With Quote
 
Anthony Jones
Guest
Posts: n/a
 
      09-11-2006

"Bob Barrows [MVP]" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Anthony Jones wrote:
> > "Jimmy" <(E-Mail Removed)> wrote in message
> > news:ukG$(E-Mail Removed)...
> >> 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"
>
>



 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Math.random() and Math.round(Math.random()) and Math.floor(Math.random()*2) VK Javascript 15 05-02-2010 03:43 PM
random.random(), random not defined!? globalrev Python 4 04-20-2008 08:12 AM
to select random number from select list Pranjal Jain Ruby 3 04-10-2008 04:01 PM
Random "The IListSource does not contain any datasources" and more (Crashing a live site at random, twice a week or so) Lars-Erik Aabech ASP .Net 8 04-28-2005 07:52 AM
Why does my Top DashBoard Image do not touch top of the screen? Frederic HOUDE HTML 4 08-30-2004 11:22 PM



Advertisments