473,835 Members | 1,676 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Sep 9 '06 #1
48 4275
>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*****@dnartr eb.noraawrote in message
news:us******** ******@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

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**********@s pammotel.comwro te in message
news:12******** *****@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.

Sep 10 '06 #5
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)
Sep 10 '06 #6
what the hell are you talking about?

"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ************@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)

Sep 10 '06 #7
wrote on 10 sep 2006 in microsoft.publi c.inetserver.as p.general:
"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ************@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)
Sep 10 '06 #8

"Jimmy" <j@j.jwrote in message
news:uk******** ******@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]" <te*****@dnartr eb.noraawrote in
message
news:us******** ******@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


Sep 11 '06 #9
Anthony Jones wrote:
"Jimmy" <j@j.jwrote in message
news:uk******** ******@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"
Sep 11 '06 #10

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

Similar topics

5
5063
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 thing. Thanks for any help you can give Jamie
2
6296
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 number between 1 and <recordcount> and then move to this record in the recordset. There is a easier way, isn't it?
7
6945
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
1603
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 trying to extract a random record from a table everything a page loads. Does anyone have a clip of code or something I can use as a reference. I can easily create a random number and I can easily create a recordset...err...DataReader...but I'm...
1
5261
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 procedure should check if the record number (seat number) is valid(this works fine), then if the record number(seat number) is vaild but not been assigned to anyone the app should throw up a messagebox "seat X is
9
2939
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 return the matching record however the second query needs to find a random record and return it. The union is causing me some trouble. It seems that any method i try to find a random record just causes an error. Here is an example of a query...
26
3167
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 need to open the DB and randomly select a record (and then display the name, which i dont have a problem with) how can i randomly select a record? im guessing id have to open a recordset
7
7877
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 details of the categories are stored in another table called Category. What I need to do is retrieve a recordset that contains one record from each category, but where the records that are retrieved are random. I know how to retrieve one or more...
3
3888
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
9652
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10517
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10558
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9343
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7765
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6961
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5631
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4430
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3086
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.