|
 |
asp_web_howto thread: Selecting random values from a fields collection
Message #1 by "Rick Lull" <ricklull@h...> on Thu, 18 Jul 2002 18:46:56
|
|
I have a simple recordset of 30 records. Each record only has one value.
How do I randomly select only one of these values (I've been trying to
use the fields collection) each time the page is refreshed? - Thanks.
Message #2 by "Larry Woods" <larry@l...> on Thu, 18 Jul 2002 12:32:19 -0700
|
|
Rick,
One way is to set the AbsolutePosition of the recordset to a
value between 1-30 in your case. Another way is to load the
complete recordset into memory using the GetRows method.
Here is the way using AbsolutePosition:
Randomize(time)
rs.AbsolutePosition=CLng(CLng((Rnd * 30)mod 30) +1)
The Randomize function assures you that the Rnd function will not
be the same value each time that you call it.
The next line of code will set the AbsolutePosition of the
recordset 'rs' to a value between 1 and 30 (Extra code is to
protect against rounding). Then you can read it. I have assumed
that you have opened the recordset, etc. Also that there are 30
records, etc.
Larry Woods - MCSD, MCT
> -----Original Message-----
> From: Rick Lull [mailto:ricklull@h...]
> Sent: Thursday, July 18, 2002 6:47 PM
> To: ASP Web HowTo
> Subject: [asp_web_howto] Selecting random values from a fields
> collection
>
>
> I have a simple recordset of 30 records. Each record
> only has one value.
> How do I randomly select only one of these values
> (I've been trying to
> use the fields collection) each time the page is
> refreshed? - Thanks.
>
> ---
>
> Improve your web design skills with these new books
> from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=n
> osim/theprogramme
> r-20
> Constructing Accessible Web Sites
> http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=n
osim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/thepr
ogramme
r-20
Message #3 by "Ken Schaefer" <ken@a...> on Fri, 19 Jul 2002 13:04:54 +1000
|
|
Probably a better way would be to just select a single, random, record from
the database in the first place.
For SQL Server:
SELECT
TOP 1 Field1,
Field2,
Field3,
NewID()
FROM
Table1
ORDER BY
4
You can use the Rnd(Table1_PK) function in Access to accomplish the same
thing
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Larry Woods" <larry@l...>
Subject: [asp_web_howto] RE: Selecting random values from a fields
collection
: Rick,
:
: One way is to set the AbsolutePosition of the recordset to a
: value between 1-30 in your case. Another way is to load the
: complete recordset into memory using the GetRows method.
:
: Here is the way using AbsolutePosition:
:
: Randomize(time)
: rs.AbsolutePosition=CLng(CLng((Rnd * 30)mod 30) +1)
:
: The Randomize function assures you that the Rnd function will not
: be the same value each time that you call it.
:
: The next line of code will set the AbsolutePosition of the
: recordset 'rs' to a value between 1 and 30 (Extra code is to
: protect against rounding). Then you can read it. I have assumed
: that you have opened the recordset, etc. Also that there are 30
: records, etc.
:
: Larry Woods - MCSD, MCT
:
:
: > -----Original Message-----
: > From: Rick Lull [mailto:ricklull@h...]
: > Sent: Thursday, July 18, 2002 6:47 PM
: > To: ASP Web HowTo
: > Subject: [asp_web_howto] Selecting random values from a fields
: > collection
: >
: > I have a simple recordset of 30 records. Each record
: > only has one value.
: > How do I randomly select only one of these values
: > (I've been trying to
: > use the fields collection) each time the page is
: > refreshed? - Thanks.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Message #4 by "Larry Woods" <larry@l...> on Fri, 19 Jul 2002 04:48:33 -0700
|
|
Ken,
How do you use Rnd(Table1_PK) in Access? I can't find any info
on it in the Access documentation...other than the VBA Rnd
function. This can be used in a SQL statement? Can you give an
example?
In your SQL statement, you are saying that putting NewID in the
selection list will return a random record? And do I have to
order it by 4 to make this work? I didn't know this either.
Thanks.
Larry Woods
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Thursday, July 18, 2002 8:05 PM
> To: ASP Web HowTo
> Subject: [asp_web_howto] RE: Selecting random values
> from a fields
> collection
>
>
> Probably a better way would be to just select a
> single, random, record from
> the database in the first place.
>
> For SQL Server:
>
> SELECT
> TOP 1 Field1,
> Field2,
> Field3,
> NewID()
> FROM
> Table1
> ORDER BY
> 4
>
> You can use the Rnd(Table1_PK) function in Access to
> accomplish the same
> thing
>
> Cheers
> Ken
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Larry Woods" <larry@l...>
> Subject: [asp_web_howto] RE: Selecting random values
> from a fields
> collection
>
>
> : Rick,
> :
> : One way is to set the AbsolutePosition of the recordset to a
> : value between 1-30 in your case. Another way is to load the
> : complete recordset into memory using the GetRows method.
> :
> : Here is the way using AbsolutePosition:
> :
> : Randomize(time)
> : rs.AbsolutePosition=CLng(CLng((Rnd * 30)mod 30) +1)
> :
> : The Randomize function assures you that the Rnd
> function will not
> : be the same value each time that you call it.
> :
> : The next line of code will set the AbsolutePosition of the
> : recordset 'rs' to a value between 1 and 30 (Extra code is to
> : protect against rounding). Then you can read it. I
> have assumed
> : that you have opened the recordset, etc. Also that
> there are 30
> : records, etc.
> :
> : Larry Woods - MCSD, MCT
> :
> :
> : > -----Original Message-----
> : > From: Rick Lull [mailto:ricklull@h...]
> : > Sent: Thursday, July 18, 2002 6:47 PM
> : > To: ASP Web HowTo
> : > Subject: [asp_web_howto] Selecting random values
> from a fields
> : > collection
> : >
> : > I have a simple recordset of 30 records. Each record
> : > only has one value.
> : > How do I randomly select only one of these values
> : > (I've been trying to
> : > use the fields collection) each time the page is
> : > refreshed? - Thanks.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>
>
> ---
>
> Improve your web design skills with these new books
> from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=n
osim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/thepr
ogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/thepr
ogramme
r-20
Message #5 by "Ken Schaefer" <ken@a...> on Mon, 22 Jul 2002 13:28:07 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Larry Woods" <larry@l...>
Subject: [asp_web_howto] RE: Selecting random values from a fields
collection
: How do you use Rnd(Table1_PK) in Access? I can't find any info
: on it in the Access documentation...other than the VBA Rnd
: function. This can be used in a SQL statement? Can you give an
: example?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT
Table1ID, Field1, Rnd(Table1ID) AS Expr1
FROM
Table1
ORDER BY
3;
You can use many VBA functions inside Access SQL statements (Date() to
insert the curret date is one that is commonly used). This is why validating
user input is important (so that people don't pass ||, pipe characters, into
your SQL statement).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: In your SQL statement, you are saying that putting NewID in the
: selection list will return a random record?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
No, it'll return a SQL Server unique identifier. Try this in Query Analyser:
SELECT NEWID()
to see what it does. In your SQL query, you return each record as a column
/plus/ another, appended, column containing unique identifiers (which will
be random, and based on the current time). Unique Identifiers are like GUIDs
in this respect.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: And do I have to
: order it by 4 to make this work? I didn't know this either.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
You will need to order by the ordinal position of the column that contains
the unique identifiers. Try over and over in QA:
USE Northwind
SELECT
CategoryID,
CategoryName,
NewID()
FROM
Categories
ORDER BY
3
GO
Cheers
Ken
Message #6 by "Larry Woods" <larrywoods@c...> on Mon, 22 Jul 2002 05:07:35 -0700
|
|
Ken,
I think that the original requestor wanted to be able to randomly
select a record from a table. In his example he had 30 records
in a table and he wanted to be able to request one of these
records randomly. That is what I was showing him how to do.
Your examples address another situation.
Larry
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Sunday, July 21, 2002 8:28 PM
> To: ASP Web HowTo
> Subject: [asp_web_howto] RE: Selecting random values
> from a fields
> collection
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Larry Woods" <larry@l...>
> Subject: [asp_web_howto] RE: Selecting random values
> from a fields
> collection
>
>
> : How do you use Rnd(Table1_PK) in Access? I can't
> find any info
> : on it in the Access documentation...other than the VBA Rnd
> : function. This can be used in a SQL statement? Can
> you give an
> : example?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> SELECT
> Table1ID, Field1, Rnd(Table1ID) AS Expr1
> FROM
> Table1
> ORDER BY
> 3;
>
> You can use many VBA functions inside Access SQL
> statements (Date() to
> insert the curret date is one that is commonly used).
> This is why validating
> user input is important (so that people don't pass ||,
> pipe characters, into
> your SQL statement).
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : In your SQL statement, you are saying that putting
> NewID in the
> : selection list will return a random record?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> No, it'll return a SQL Server unique identifier. Try
> this in Query Analyser:
>
> SELECT NEWID()
>
> to see what it does. In your SQL query, you return
> each record as a column
> /plus/ another, appended, column containing unique
> identifiers (which will
> be random, and based on the current time). Unique
> Identifiers are like GUIDs
> in this respect.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : And do I have to
> : order it by 4 to make this work? I didn't know this either.
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> You will need to order by the ordinal position of the
> column that contains
> the unique identifiers. Try over and over in QA:
>
> USE Northwind
>
> SELECT
> CategoryID,
> CategoryName,
> NewID()
> FROM
> Categories
> ORDER BY
> 3
>
> GO
>
> Cheers
> Ken
>
>
>
>
>
> ---
>
> Improve your web design skills with these new books
> from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=n
osim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/thepr
ogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/thepr
ogramme
r-20
Message #7 by "Ken Schaefer" <ken@a...> on Tue, 23 Jul 2002 12:14:02 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Larry Woods" <larrywoods@c...>
Subject: [asp_web_howto] RE: Selecting random values from a fields
collection
: Ken,
:
: I think that the original requestor wanted to be able to randomly
: select a record from a table. In his example he had 30 records
: in a table and he wanted to be able to request one of these
: records randomly. That is what I was showing him how to do.
: Your examples address another situation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
<sigh>
My example addresses *exactly* what the original poster wanted - being able
to select a random record from a table containing an arbitrary number of
records
</sigh>
SELECT
TOP 1 Field1, -- LOOK, note use of TOP 1
NewID()
FROM
Table1
ORDER BY
2
will:
a) construct a resultset in the database
b) order the resultset by the random number
c) return *only* the first record of the result set.
I have answered this question many times on various lists that I'm on - I do
know what I'm talking about! :-)
Cheers
Ken
:
: > -----Original Message-----
: > From: Ken Schaefer [mailto:ken@a...]
: > Sent: Sunday, July 21, 2002 8:28 PM
: > To: ASP Web HowTo
: > Subject: [asp_web_howto] RE: Selecting random values
: > from a fields
: > collection
: >
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: "Larry Woods" <larry@l...>
: > Subject: [asp_web_howto] RE: Selecting random values
: > from a fields
: > collection
: >
: >
: > : How do you use Rnd(Table1_PK) in Access? I can't
: > find any info
: > : on it in the Access documentation...other than the VBA Rnd
: > : function. This can be used in a SQL statement? Can
: > you give an
: > : example?
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > SELECT
: > Table1ID, Field1, Rnd(Table1ID) AS Expr1
: > FROM
: > Table1
: > ORDER BY
: > 3;
: >
: > You can use many VBA functions inside Access SQL
: > statements (Date() to
: > insert the curret date is one that is commonly used).
: > This is why validating
: > user input is important (so that people don't pass ||,
: > pipe characters, into
: > your SQL statement).
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : In your SQL statement, you are saying that putting
: > NewID in the
: > : selection list will return a random record?
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > No, it'll return a SQL Server unique identifier. Try
: > this in Query Analyser:
: >
: > SELECT NEWID()
: >
: > to see what it does. In your SQL query, you return
: > each record as a column
: > /plus/ another, appended, column containing unique
: > identifiers (which will
: > be random, and based on the current time). Unique
: > Identifiers are like GUIDs
: > in this respect.
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : And do I have to
: > : order it by 4 to make this work? I didn't know this either.
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > You will need to order by the ordinal position of the
: > column that contains
: > the unique identifiers. Try over and over in QA:
: >
: > USE Northwind
: >
: > SELECT
: > CategoryID,
: > CategoryName,
: > NewID()
: > FROM
: > Categories
: > ORDER BY
: > 3
: >
: > GO
: >
: > Cheers
: > Ken
Message #8 by "Larry Woods" <larry@l...> on Mon, 22 Jul 2002 20:08:48 -0700
|
|
Ken,
I'm not questioning your abilities. I was trying to understand
how your Access statement worked. Please give us an example.
Larry Woods
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Monday, July 22, 2002 7:14 PM
> To: ASP Web HowTo
> Subject: [asp_web_howto] RE: Selecting random values
> from a fields
> collection
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Larry Woods" <larrywoods@c...>
> Subject: [asp_web_howto] RE: Selecting random values
> from a fields
> collection
>
>
> : Ken,
> :
> : I think that the original requestor wanted to be
> able to randomly
> : select a record from a table. In his example he had
> 30 records
> : in a table and he wanted to be able to request one of these
> : records randomly. That is what I was showing him how to do.
> : Your examples address another situation.
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> <sigh>
> My example addresses *exactly* what the original
> poster wanted - being able
> to select a random record from a table containing an
> arbitrary number of
> records
> </sigh>
>
> SELECT
> TOP 1 Field1, -- LOOK, note use of TOP 1
> NewID()
> FROM
> Table1
> ORDER BY
> 2
>
> will:
> a) construct a resultset in the database
> b) order the resultset by the random number
> c) return *only* the first record of the result set.
>
> I have answered this question many times on various
> lists that I'm on - I do
> know what I'm talking about! :-)
>
> Cheers
> Ken
> :
> : > -----Original Message-----
> : > From: Ken Schaefer [mailto:ken@a...]
> : > Sent: Sunday, July 21, 2002 8:28 PM
> : > To: ASP Web HowTo
> : > Subject: [asp_web_howto] RE: Selecting random values
> : > from a fields
> : > collection
> : >
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > From: "Larry Woods" <larry@l...>
> : > Subject: [asp_web_howto] RE: Selecting random values
> : > from a fields
> : > collection
> : >
> : >
> : > : How do you use Rnd(Table1_PK) in Access? I can't
> : > find any info
> : > : on it in the Access documentation...other than
> the VBA Rnd
> : > : function. This can be used in a SQL statement? Can
> : > you give an
> : > : example?
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > SELECT
> : > Table1ID, Field1, Rnd(Table1ID) AS Expr1
> : > FROM
> : > Table1
> : > ORDER BY
> : > 3;
> : >
> : > You can use many VBA functions inside Access SQL
> : > statements (Date() to
> : > insert the curret date is one that is commonly used).
> : > This is why validating
> : > user input is important (so that people don't pass ||,
> : > pipe characters, into
> : > your SQL statement).
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : In your SQL statement, you are saying that putting
> : > NewID in the
> : > : selection list will return a random record?
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > No, it'll return a SQL Server unique identifier. Try
> : > this in Query Analyser:
> : >
> : > SELECT NEWID()
> : >
> : > to see what it does. In your SQL query, you return
> : > each record as a column
> : > /plus/ another, appended, column containing unique
> : > identifiers (which will
> : > be random, and based on the current time). Unique
> : > Identifiers are like GUIDs
> : > in this respect.
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : And do I have to
> : > : order it by 4 to make this work? I didn't know
> this either.
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > You will need to order by the ordinal position of the
> : > column that contains
> : > the unique identifiers. Try over and over in QA:
> : >
> : > USE Northwind
> : >
> : > SELECT
> : > CategoryID,
> : > CategoryName,
> : > NewID()
> : > FROM
> : > Categories
> : > ORDER BY
> : > 3
> : >
> : > GO
> : >
> : > Cheers
> : > Ken
>
>
>
>
> ---
>
> Improve your web design skills with these new books
> from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=n
osim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/thepr
ogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/thepr
ogramme
r-20
Message #9 by "Ken Schaefer" <ken@a...> on Tue, 23 Jul 2002 13:39:35 +1000
|
|
SELECT
TOP 1 Field1,
Field2,
Field3,
RND(Table1PK)
FROM
Table1
ORDER BY
4
I posted the code before. It's a simple matter of adding TOP XX to limit the
number of records you want returned from the database. If you want 5
records, then use TOP 5. This is simple SQL.
The only "twist" is the addition of a column that does not exist in the
database - you use the RND() function to populate this column with random
values, and order by this column so that the actual records are randomly
ordered each time you run the query.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Larry Woods" <larry@l...>
Subject: [asp_web_howto] RE: Selecting random values from a fields
collection
: Ken,
:
: I'm not questioning your abilities. I was trying to understand
: how your Access statement worked. Please give us an example.
:
: Larry Woods
:
:
: > -----Original Message-----
: > From: Ken Schaefer [mailto:ken@a...]
: > Sent: Monday, July 22, 2002 7:14 PM
: > To: ASP Web HowTo
: > Subject: [asp_web_howto] RE: Selecting random values
: > from a fields
: > collection
: >
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: "Larry Woods" <larrywoods@c...>
: > Subject: [asp_web_howto] RE: Selecting random values
: > from a fields
: > collection
: >
: >
: > : Ken,
: > :
: > : I think that the original requestor wanted to be
: > able to randomly
: > : select a record from a table. In his example he had
: > 30 records
: > : in a table and he wanted to be able to request one of these
: > : records randomly. That is what I was showing him how to do.
: > : Your examples address another situation.
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > <sigh>
: > My example addresses *exactly* what the original
: > poster wanted - being able
: > to select a random record from a table containing an
: > arbitrary number of
: > records
: > </sigh>
: >
: > SELECT
: > TOP 1 Field1, -- LOOK, note use of TOP 1
: > NewID()
: > FROM
: > Table1
: > ORDER BY
: > 2
: >
: > will:
: > a) construct a resultset in the database
: > b) order the resultset by the random number
: > c) return *only* the first record of the result set.
: >
: > I have answered this question many times on various
: > lists that I'm on - I do
: > know what I'm talking about! :-)
: >
: > Cheers
: > Ken
: > :
: > : > -----Original Message-----
: > : > From: Ken Schaefer [mailto:ken@a...]
: > : > Sent: Sunday, July 21, 2002 8:28 PM
: > : > To: ASP Web HowTo
: > : > Subject: [asp_web_howto] RE: Selecting random values
: > : > from a fields
: > : > collection
: > : >
: > : >
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : > From: "Larry Woods" <larry@l...>
: > : > Subject: [asp_web_howto] RE: Selecting random values
: > : > from a fields
: > : > collection
: > : >
: > : >
: > : > : How do you use Rnd(Table1_PK) in Access? I can't
: > : > find any info
: > : > : on it in the Access documentation...other than
: > the VBA Rnd
: > : > : function. This can be used in a SQL statement? Can
: > : > you give an
: > : > : example?
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : >
: > : > SELECT
: > : > Table1ID, Field1, Rnd(Table1ID) AS Expr1
: > : > FROM
: > : > Table1
: > : > ORDER BY
: > : > 3;
: > : >
: > : > You can use many VBA functions inside Access SQL
: > : > statements (Date() to
: > : > insert the curret date is one that is commonly used).
: > : > This is why validating
: > : > user input is important (so that people don't pass ||,
: > : > pipe characters, into
: > : > your SQL statement).
: > : >
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : > : In your SQL statement, you are saying that putting
: > : > NewID in the
: > : > : selection list will return a random record?
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : >
: > : > No, it'll return a SQL Server unique identifier. Try
: > : > this in Query Analyser:
: > : >
: > : > SELECT NEWID()
: > : >
: > : > to see what it does. In your SQL query, you return
: > : > each record as a column
: > : > /plus/ another, appended, column containing unique
: > : > identifiers (which will
: > : > be random, and based on the current time). Unique
: > : > Identifiers are like GUIDs
: > : > in this respect.
: > : >
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : > : And do I have to
: > : > : order it by 4 to make this work? I didn't know
: > this either.
: > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > : >
: > : > You will need to order by the ordinal position of the
: > : > column that contains
: > : > the unique identifiers. Try over and over in QA:
: > : >
: > : > USE Northwind
: > : >
: > : > SELECT
: > : > CategoryID,
: > : > CategoryName,
: > : > NewID()
: > : > FROM
: > : > Categories
: > : > ORDER BY
: > : > 3
: > : >
: > : > GO
: > : >
: > : > Cheers
: > : > Ken
: >
: >
: >
: >
: > ---
: >
: > Improve your web design skills with these new books
: > from Glasshaus.
: >
: > Usable Web Menus
: > http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=n
: osim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/thepr
: ogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/thepr
: ogramme
: r-20
:
:
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
Message #10 by Lauralyn.Ninow@c... on Tue, 23 Jul 2002 11:23:48 -0500
|
|
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C23265.532D5240
Content-Type: text/plain;
charset="iso-8859-1"
Hello.
I thought this query was pretty clever. so I created one, and each time I
run the query in the database, it gives me a new record.
However, when I call it from my ASP page, it always returns the same record.
Any idea why?
Is there a seed I can set for RND?
LN
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Monday, July 22, 2002 10:40 PM
> To: ASP Web HowTo
> Subject: [asp_web_howto] RE: Selecting random values from a fields
> collection
> Importance: High
>
>
> SELECT
> TOP 1 Field1,
> Field2,
> Field3,
> RND(Table1PK)
> FROM
> Table1
> ORDER BY
> 4
>
> I posted the code before. It's a simple matter of adding TOP
> XX to limit the
> number of records you want returned from the database. If you want 5
> records, then use TOP 5. This is simple SQL.
>
> The only "twist" is the addition of a column that does not
> exist in the
> database - you use the RND() function to populate this column
> with random
> values, and order by this column so that the actual records
> are randomly
> ordered each time you run the query.
>
> Cheers
> Ken
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: "Larry Woods" <larry@l...>
> Subject: [asp_web_howto] RE: Selecting random values from a fields
> collection
>
>
> : Ken,
> :
> : I'm not questioning your abilities. I was trying to understand
> : how your Access statement worked. Please give us an example.
> :
> : Larry Woods
> :
> :
> : > -----Original Message-----
> : > From: Ken Schaefer [mailto:ken@a...]
> : > Sent: Monday, July 22, 2002 7:14 PM
> : > To: ASP Web HowTo
> : > Subject: [asp_web_howto] RE: Selecting random values
> : > from a fields
> : > collection
> : >
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > From: "Larry Woods" <larrywoods@c...>
> : > Subject: [asp_web_howto] RE: Selecting random values
> : > from a fields
> : > collection
> : >
> : >
> : > : Ken,
> : > :
> : > : I think that the original requestor wanted to be
> : > able to randomly
> : > : select a record from a table. In his example he had
> : > 30 records
> : > : in a table and he wanted to be able to request one of these
> : > : records randomly. That is what I was showing him how to do.
> : > : Your examples address another situation.
> : >
> : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : >
> : > <sigh>
> : > My example addresses *exactly* what the original
> : > poster wanted - being able
> : > to select a random record from a table containing an
> : > arbitrary number of
> : > records
> : > </sigh>
> : >
> : > SELECT
> : > TOP 1 Field1, -- LOOK, note use of TOP 1
> : > NewID()
> : > FROM
> : > Table1
> : > ORDER BY
> : > 2
> : >
> : > will:
> : > a) construct a resultset in the database
> : > b) order the resultset by the random number
> : > c) return *only* the first record of the result set.
> : >
> : > I have answered this question many times on various
> : > lists that I'm on - I do
> : > know what I'm talking about! :-)
> : >
> : > Cheers
> : > Ken
> : > :
> : > : > -----Original Message-----
> : > : > From: Ken Schaefer [mailto:ken@a...]
> : > : > Sent: Sunday, July 21, 2002 8:28 PM
> : > : > To: ASP Web HowTo
> : > : > Subject: [asp_web_howto] RE: Selecting random values
> : > : > from a fields
> : > : > collection
> : > : >
> : > : >
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : > From: "Larry Woods" <larry@l...>
> : > : > Subject: [asp_web_howto] RE: Selecting random values
> : > : > from a fields
> : > : > collection
> : > : >
> : > : >
> : > : > : How do you use Rnd(Table1_PK) in Access? I can't
> : > : > find any info
> : > : > : on it in the Access documentation...other than
> : > the VBA Rnd
> : > : > : function. This can be used in a SQL statement? Can
> : > : > you give an
> : > : > : example?
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : >
> : > : > SELECT
> : > : > Table1ID, Field1, Rnd(Table1ID) AS Expr1
> : > : > FROM
> : > : > Table1
> : > : > ORDER BY
> : > : > 3;
> : > : >
> : > : > You can use many VBA functions inside Access SQL
> : > : > statements (Date() to
> : > : > insert the curret date is one that is commonly used).
> : > : > This is why validating
> : > : > user input is important (so that people don't pass ||,
> : > : > pipe characters, into
> : > : > your SQL statement).
> : > : >
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : > : In your SQL statement, you are saying that putting
> : > : > NewID in the
> : > : > : selection list will return a random record?
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : >
> : > : > No, it'll return a SQL Server unique identifier. Try
> : > : > this in Query Analyser:
> : > : >
> : > : > SELECT NEWID()
> : > : >
> : > : > to see what it does. In your SQL query, you return
> : > : > each record as a column
> : > : > /plus/ another, appended, column containing unique
> : > : > identifiers (which will
> : > : > be random, and based on the current time). Unique
> : > : > Identifiers are like GUIDs
> : > : > in this respect.
> : > : >
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : > : And do I have to
> : > : > : order it by 4 to make this work? I didn't know
> : > this either.
> : > : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> : > : >
> : > : > You will need to order by the ordinal position of the
> : > : > column that contains
> : > : > the unique identifiers. Try over and over in QA:
> : > : >
> : > : > USE Northwind
> : > : >
> : > : > SELECT
> : > : > CategoryID,
> : > : > CategoryName,
> : > : > NewID()
> : > : > FROM
> : > : > Categories
> : > : > ORDER BY
> : > : > 3
> : > : >
> : > : > GO
> : > : >
> : > : > Cheers
> : > : > Ken
> : >
> : >
> : >
> : >
> : > ---
> : >
> : > Improve your web design skills with these new books
> : > from Glasshaus.
> : >
> : > Usable Web Menus
> : > http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=n
> : osim/theprogramme
> : r-20
> : Constructing Accessible Web Sites
> : http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/thepr
> : ogramme
> : r-20
> : Practical JavaScript for the Usable Web
> : http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/thepr
> : ogramme
> : r-20
> :
> :
> :
> : ---
> :
> : Improve your web design skills with these new books from Glasshaus.
> :
> : Usable Web Menus
> :
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/th
eprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #11 by "Ken Schaefer" <ken@a...> on Wed, 24 Jul 2002 12:23:15 +1000
|
|
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Lauralyn.Ninow@c...>
Subject: [asp_web_howto] RE: Selecting random values from a fields col
lection
: I thought this query was pretty clever. so I created one, and each time I
: run the query in the database, it gives me a new record.
:
: However, when I call it from my ASP page, it always returns the same
record.
: Any idea why?
:
: Is there a seed I can set for RND?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
It doesn't really matter what you use as the seed, but just calling Rnd()
will always return the same value for a given query...You could use the
value of your Primary Key (not to heavy on the DB), or the value of
Date()...
SELECT
Rnd(Table1_PrimaryKeyFieldNameHere),
SecondField,
ThirdField
FROM
Table1
ORDER BY
1
should seed the RND() function with the primary key value of the current
record.
Are you sure the ASP page isn't being cached someplace?
Cheers
Ken
Message #12 by Lauralyn.Ninow@c... on Wed, 24 Jul 2002 11:36:40 -0500
|
|
Ken, This is my query
SELECT TOP 1 quotes.Quote, Rnd(quotes.id) AS Expr1
FROM quotes
WHERE (((quotes.isCurrent)=True))
ORDER BY 2;
objCmd.CommandText = "WEB_Quote_Random"
set objRS = objCmd.execute
if not objRS.eof then
response.write objRS("quote")
end if
objRS.close
set objRS = nothing
I'm pretty sure that my page isn't cached, because my old method worked:
1) Find the count of records I'm dealing with.
2) get a random number in the range from 1 to count of recs
3) return all the rows
4) go to the specific random rowcount
objCmd.CommandText = "WEB_Quotes_count_all"
set objRS = objCmd.execute()
Randomize()
intRandomRow = int((objRS("Counted") - 1 + 1) * Rnd + 1) - 1
objRS.close
objCmd.CommandText = "WEB_Quotes_all"
set objRS = objCmd.execute
if not objRS.eof then
arrData = objRS.GetRows()
end if
objRS.close
if isarray(arrData) then
response.write arrData(0, intRandomRow)
end if
I'd really prefer not to do all that work. Any suggestions?
Lauralyn Ninow
webmaster @ Comtrol Corporation
http://www.comtrol.com
> -----Original Message-----
> From: Ken Schaefer [mailto:ken@a...]
> Sent: Tuesday, July 23, 2002 9:23 PM
> To: ASP Web HowTo
> Subject: [asp_web_howto] RE: Selecting random values from a fields col
> lection
>
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> From: <Lauralyn.Ninow@c...>
> Subject: [asp_web_howto] RE: Selecting random values from a fields col
> lection
>
>
> : I thought this query was pretty clever. so I created one,
> and each time I
> : run the query in the database, it gives me a new record.
> :
> : However, when I call it from my ASP page, it always returns the same
> record.
> : Any idea why?
> :
> : Is there a seed I can set for RND?
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> It doesn't really matter what you use as the seed, but just
> calling Rnd()
> will always return the same value for a given query...You
> could use the
> value of your Primary Key (not to heavy on the DB), or the value of
> Date()...
>
> SELECT
> Rnd(Table1_PrimaryKeyFieldNameHere),
> SecondField,
> ThirdField
> FROM
> Table1
> ORDER BY
> 1
>
> should seed the RND() function with the primary key value of
> the current
> record.
>
> Are you sure the ASP page isn't being cached someplace?
>
> Cheers
> Ken
>
>
>
>
> ---
>
> Improve your web design skills with these new books from Glasshaus.
>
> Usable Web Menus
> http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/th
eprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #13 by "Ken Schaefer" <ken@a...> on Thu, 25 Jul 2002 15:58:49 +1000
|
|
Yikes,
It seems like Jet is somehow caching the output of the SQL statement. When
you run it inside Access it recomputes the random numbers.
When you run it from an ASP page, it always outputs the same value BUT BUT
BUT, if you run the same SQL statement twice in a row, you get different
values...
strSQL = _
"SELECT Table1ID, Table1Field1, RND(Table1ID) " & _
"FROM Table1 " & _
"ORDER BY 3"
objRS.Open strSQL, objConn
' now write out objRS field values
objRS.Close
objRS.Open strSQL objConn
' now you get differnet values!
...this is very odd.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Lauralyn.Ninow@c...>
Subject: [asp_web_howto] RE: Selecting random values from a fields col
lection
: Ken, This is my query
:
: SELECT TOP 1 quotes.Quote, Rnd(quotes.id) AS Expr1
: FROM quotes
: WHERE (((quotes.isCurrent)=True))
: ORDER BY 2;
:
: objCmd.CommandText = "WEB_Quote_Random"
: set objRS = objCmd.execute
:
: if not objRS.eof then
: response.write objRS("quote")
: end if
: objRS.close
: set objRS = nothing
:
:
: I'm pretty sure that my page isn't cached, because my old method worked:
:
: 1) Find the count of records I'm dealing with.
: 2) get a random number in the range from 1 to count of recs
: 3) return all the rows
: 4) go to the specific random rowcount
:
: objCmd.CommandText = "WEB_Quotes_count_all"
: set objRS = objCmd.execute()
:
: Randomize()
: intRandomRow = int((objRS("Counted") - 1 + 1) * Rnd + 1) - 1
: objRS.close
: objCmd.CommandText = "WEB_Quotes_all"
: set objRS = objCmd.execute
:
: if not objRS.eof then
: arrData = objRS.GetRows()
: end if
: objRS.close
:
: if isarray(arrData) then
: response.write arrData(0, intRandomRow)
: end if
:
: I'd really prefer not to do all that work. Any suggestions?
:
: Lauralyn Ninow
: webmaster @ Comtrol Corporation
: http://www.comtrol.com
:
: > -----Original Message-----
: > From: Ken Schaefer [mailto:ken@a...]
: > Sent: Tuesday, July 23, 2002 9:23 PM
: > To: ASP Web HowTo
: > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: > lection
: >
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: <Lauralyn.Ninow@c...>
: > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: > lection
: >
: >
: > : I thought this query was pretty clever. so I created one,
: > and each time I
: > : run the query in the database, it gives me a new record.
: > :
: > : However, when I call it from my ASP page, it always returns the same
: > record.
: > : Any idea why?
: > :
: > : Is there a seed I can set for RND?
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > It doesn't really matter what you use as the seed, but just
: > calling Rnd()
: > will always return the same value for a given query...You
: > could use the
: > value of your Primary Key (not to heavy on the DB), or the value of
: > Date()...
: >
: > SELECT
: > Rnd(Table1_PrimaryKeyFieldNameHere),
: > SecondField,
: > ThirdField
: > FROM
: > Table1
: > ORDER BY
: > 1
: >
: > should seed the RND() function with the primary key value of
: > the current
: > record.
: >
: > Are you sure the ASP page isn't being cached someplace?
: >
: > Cheers
: > Ken
Message #14 by "Giovanni Salucci" <g.salucci@n...> on Mon, 29 Jul 2002 14:36:44 +0200
|
|
BUT BUT BUT second time values are always the same!!
is there a solution, or should we randomize
AFTER extracting recordset?
thanks.
G.
-----Messaggio originale-----
Da: Ken Schaefer [mailto:ken@a...]
Inviato: giovedì 25 luglio 2002 7.59
A: ASP Web HowTo
Oggetto: [asp_web_howto] RE: Selecting random values from a fields col
lection
Yikes,
It seems like Jet is somehow caching the output of the SQL statement. When
you run it inside Access it recomputes the random numbers.
When you run it from an ASP page, it always outputs the same value BUT BUT
BUT, if you run the same SQL statement twice in a row, you get different
values...
strSQL = _
"SELECT Table1ID, Table1Field1, RND(Table1ID) " & _
"FROM Table1 " & _
"ORDER BY 3"
objRS.Open strSQL, objConn
' now write out objRS field values
objRS.Close
objRS.Open strSQL objConn
' now you get differnet values!
...this is very odd.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: <Lauralyn.Ninow@c...>
Subject: [asp_web_howto] RE: Selecting random values from a fields col
lection
: Ken, This is my query
:
: SELECT TOP 1 quotes.Quote, Rnd(quotes.id) AS Expr1
: FROM quotes
: WHERE (((quotes.isCurrent)=True))
: ORDER BY 2;
:
: objCmd.CommandText = "WEB_Quote_Random"
: set objRS = objCmd.execute
:
: if not objRS.eof then
: response.write objRS("quote")
: end if
: objRS.close
: set objRS = nothing
:
:
: I'm pretty sure that my page isn't cached, because my old method worked:
:
: 1) Find the count of records I'm dealing with.
: 2) get a random number in the range from 1 to count of recs
: 3) return all the rows
: 4) go to the specific random rowcount
:
: objCmd.CommandText = "WEB_Quotes_count_all"
: set objRS = objCmd.execute()
:
: Randomize()
: intRandomRow = int((objRS("Counted") - 1 + 1) * Rnd + 1) - 1
: objRS.close
: objCmd.CommandText = "WEB_Quotes_all"
: set objRS = objCmd.execute
:
: if not objRS.eof then
: arrData = objRS.GetRows()
: end if
: objRS.close
:
: if isarray(arrData) then
: response.write arrData(0, intRandomRow)
: end if
:
: I'd really prefer not to do all that work. Any suggestions?
:
: Lauralyn Ninow
: webmaster @ Comtrol Corporation
: http://www.comtrol.com
:
: > -----Original Message-----
: > From: Ken Schaefer [mailto:ken@a...]
: > Sent: Tuesday, July 23, 2002 9:23 PM
: > To: ASP Web HowTo
: > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: > lection
: >
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: > From: <Lauralyn.Ninow@c...>
: > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: > lection
: >
: >
: > : I thought this query was pretty clever. so I created one,
: > and each time I
: > : run the query in the database, it gives me a new record.
: > :
: > : However, when I call it from my ASP page, it always returns the same
: > record.
: > : Any idea why?
: > :
: > : Is there a seed I can set for RND?
: >
: > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: >
: > It doesn't really matter what you use as the seed, but just
: > calling Rnd()
: > will always return the same value for a given query...You
: > could use the
: > value of your Primary Key (not to heavy on the DB), or the value of
: > Date()...
: >
: > SELECT
: > Rnd(Table1_PrimaryKeyFieldNameHere),
: > SecondField,
: > ThirdField
: > FROM
: > Table1
: > ORDER BY
: > 1
: >
: > should seed the RND() function with the primary key value of
: > the current
: > record.
: >
: > Are you sure the ASP page isn't being cached someplace?
: >
: > Cheers
: > Ken
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
Message #15 by "Ken Schaefer" <ken@a...> on Wed, 31 Jul 2002 12:00:51 +1000
|
|
I'm working on this with various people - there seems to be a problem with
Access always using the same seed when you first connect to Access. Since
each "connection" in the page is seen as a new connection, the values end up
being the same for subsequent calls.
At this stage, it's looking a bit messy. One thought that I had would be to
create another column containing random values, and update this on each
page, then use it to seed the subsequent call to select the records.
However, the problem may be that each connection is seen as "opening Access
again".
Maybe a COM component might need to be written (there is a KB article
showing how this can be done using DAO). I've never seen DAO used in an ASP
page, so I might try to whip up a quick-n-nasty VB component to use DAO and
return something back to ASP.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Giovanni Salucci" <g.salucci@n...>
Subject: [asp_web_howto] RE: Selecting random values from a fields col
lection
: BUT BUT BUT second time values are always the same!!
: is there a solution, or should we randomize
: AFTER extracting recordset?
:
: thanks.
: G.
:
:
: -----Messaggio originale-----
: Da: Ken Schaefer [mailto:ken@a...]
: Inviato: giovedì 25 luglio 2002 7.59
: A: ASP Web HowTo
: Oggetto: [asp_web_howto] RE: Selecting random values from a fields col
: lection
:
:
: Yikes,
:
: It seems like Jet is somehow caching the output of the SQL statement. When
: you run it inside Access it recomputes the random numbers.
:
: When you run it from an ASP page, it always outputs the same value BUT BUT
: BUT, if you run the same SQL statement twice in a row, you get different
: values...
:
: strSQL = _
: "SELECT Table1ID, Table1Field1, RND(Table1ID) " & _
: "FROM Table1 " & _
: "ORDER BY 3"
:
: objRS.Open strSQL, objConn
: ' now write out objRS field values
:
: objRS.Close
:
: objRS.Open strSQL objConn
: ' now you get differnet values!
:
: ...this is very odd.
:
: Cheers
: Ken
:
:
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <Lauralyn.Ninow@c...>
: Subject: [asp_web_howto] RE: Selecting random values from a fields col
: lection
:
:
: : Ken, This is my query
: :
: : SELECT TOP 1 quotes.Quote, Rnd(quotes.id) AS Expr1
: : FROM quotes
: : WHERE (((quotes.isCurrent)=True))
: : ORDER BY 2;
: :
: : objCmd.CommandText = "WEB_Quote_Random"
: : set objRS = objCmd.execute
: :
: : if not objRS.eof then
: : response.write objRS("quote")
: : end if
: : objRS.close
: : set objRS = nothing
: :
: :
: : I'm pretty sure that my page isn't cached, because my old method worked:
: :
: : 1) Find the count of records I'm dealing with.
: : 2) get a random number in the range from 1 to count of recs
: : 3) return all the rows
: : 4) go to the specific random rowcount
: :
: : objCmd.CommandText = "WEB_Quotes_count_all"
: : set objRS = objCmd.execute()
: :
: : Randomize()
: : intRandomRow = int((objRS("Counted") - 1 + 1) * Rnd + 1) - 1
: : objRS.close
: : objCmd.CommandText = "WEB_Quotes_all"
: : set objRS = objCmd.execute
: :
: : if not objRS.eof then
: : arrData = objRS.GetRows()
: : end if
: : objRS.close
: :
: : if isarray(arrData) then
: : response.write arrData(0, intRandomRow)
: : end if
: :
: : I'd really prefer not to do all that work. Any suggestions?
: :
: : Lauralyn Ninow
: : webmaster @ Comtrol Corporation
: : http://www.comtrol.com
: :
: : > -----Original Message-----
: : > From: Ken Schaefer [mailto:ken@a...]
: : > Sent: Tuesday, July 23, 2002 9:23 PM
: : > To: ASP Web HowTo
: : > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: : > lection
: : >
: : >
: : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: : > From: <Lauralyn.Ninow@c...>
: : > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: : > lection
: : >
: : >
: : > : I thought this query was pretty clever. so I created one,
: : > and each time I
: : > : run the query in the database, it gives me a new record.
: : > :
: : > : However, when I call it from my ASP page, it always returns the same
: : > record.
: : > : Any idea why?
: : > :
: : > : Is there a seed I can set for RND?
: : >
: : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: : >
: : > It doesn't really matter what you use as the seed, but just
: : > calling Rnd()
: : > will always return the same value for a given query...You
: : > could use the
: : > value of your Primary Key (not to heavy on the DB), or the value of
: : > Date()...
: : >
: : > SELECT
: : > Rnd(Table1_PrimaryKeyFieldNameHere),
: : > SecondField,
: : > ThirdField
: : > FROM
: : > Table1
: : > ORDER BY
: : > 1
: : >
: : > should seed the RND() function with the primary key value of
: : > the current
: : > record.
: : >
: : > Are you sure the ASP page isn't being cached someplace?
: : >
: : > Cheers
: : > Ken
:
:
:
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
:
:
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
Message #16 by "TomMallard" <mallard@s...> on Wed, 31 Jul 2002 07:57:47 -0700
|
|
Hmmmm, pretty certain closing the connection is mandatory in this case using
ADO because of query caching, a "feature" which mostly makes it as fast as
COM in a web application setting. This explains why it runs OK inside access
but not from the page.
The connection object is still there tied to the page's ASPSESSIONID until
you set it to nothing or it times out.
tom mallard
seattle
-----Original Message-----
From: Ken Schaefer [mailto:ken@a...]
Sent: Tuesday, July 30, 2002 7:01 PM
To: ASP Web HowTo
Subject: [asp_web_howto] RE: Selecting random values from a fields col
lection
I'm working on this with various people - there seems to be a problem with
Access always using the same seed when you first connect to Access. Since
each "connection" in the page is seen as a new connection, the values end up
being the same for subsequent calls.
At this stage, it's looking a bit messy. One thought that I had would be to
create another column containing random values, and update this on each
page, then use it to seed the subsequent call to select the records.
However, the problem may be that each connection is seen as "opening Access
again".
Maybe a COM component might need to be written (there is a KB article
showing how this can be done using DAO). I've never seen DAO used in an ASP
page, so I might try to whip up a quick-n-nasty VB component to use DAO and
return something back to ASP.
Cheers
Ken
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Giovanni Salucci" <g.salucci@n...>
Subject: [asp_web_howto] RE: Selecting random values from a fields col
lection
: BUT BUT BUT second time values are always the same!!
: is there a solution, or should we randomize
: AFTER extracting recordset?
:
: thanks.
: G.
:
:
: -----Messaggio originale-----
: Da: Ken Schaefer [mailto:ken@a...]
: Inviato: giovedì 25 luglio 2002 7.59
: A: ASP Web HowTo
: Oggetto: [asp_web_howto] RE: Selecting random values from a fields col
: lection
:
:
: Yikes,
:
: It seems like Jet is somehow caching the output of the SQL statement. When
: you run it inside Access it recomputes the random numbers.
:
: When you run it from an ASP page, it always outputs the same value BUT BUT
: BUT, if you run the same SQL statement twice in a row, you get different
: values...
:
: strSQL = _
: "SELECT Table1ID, Table1Field1, RND(Table1ID) " & _
: "FROM Table1 " & _
: "ORDER BY 3"
:
: objRS.Open strSQL, objConn
: ' now write out objRS field values
:
: objRS.Close
:
: objRS.Open strSQL objConn
: ' now you get differnet values!
:
: ...this is very odd.
:
: Cheers
: Ken
:
:
:
: ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: From: <Lauralyn.Ninow@c...>
: Subject: [asp_web_howto] RE: Selecting random values from a fields col
: lection
:
:
: : Ken, This is my query
: :
: : SELECT TOP 1 quotes.Quote, Rnd(quotes.id) AS Expr1
: : FROM quotes
: : WHERE (((quotes.isCurrent)=True))
: : ORDER BY 2;
: :
: : objCmd.CommandText = "WEB_Quote_Random"
: : set objRS = objCmd.execute
: :
: : if not objRS.eof then
: : response.write objRS("quote")
: : end if
: : objRS.close
: : set objRS = nothing
: :
: :
: : I'm pretty sure that my page isn't cached, because my old method worked:
: :
: : 1) Find the count of records I'm dealing with.
: : 2) get a random number in the range from 1 to count of recs
: : 3) return all the rows
: : 4) go to the specific random rowcount
: :
: : objCmd.CommandText = "WEB_Quotes_count_all"
: : set objRS = objCmd.execute()
: :
: : Randomize()
: : intRandomRow = int((objRS("Counted") - 1 + 1) * Rnd + 1) - 1
: : objRS.close
: : objCmd.CommandText = "WEB_Quotes_all"
: : set objRS = objCmd.execute
: :
: : if not objRS.eof then
: : arrData = objRS.GetRows()
: : end if
: : objRS.close
: :
: : if isarray(arrData) then
: : response.write arrData(0, intRandomRow)
: : end if
: :
: : I'd really prefer not to do all that work. Any suggestions?
: :
: : Lauralyn Ninow
: : webmaster @ Comtrol Corporation
: : http://www.comtrol.com
: :
: : > -----Original Message-----
: : > From: Ken Schaefer [mailto:ken@a...]
: : > Sent: Tuesday, July 23, 2002 9:23 PM
: : > To: ASP Web HowTo
: : > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: : > lection
: : >
: : >
: : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: : > From: <Lauralyn.Ninow@c...>
: : > Subject: [asp_web_howto] RE: Selecting random values from a fields col
: : > lection
: : >
: : >
: : > : I thought this query was pretty clever. so I created one,
: : > and each time I
: : > : run the query in the database, it gives me a new record.
: : > :
: : > : However, when I call it from my ASP page, it always returns the same
: : > record.
: : > : Any idea why?
: : > :
: : > : Is there a seed I can set for RND?
: : >
: : > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
: : >
: : > It doesn't really matter what you use as the seed, but just
: : > calling Rnd()
: : > will always return the same value for a given query...You
: : > could use the
: : > value of your Primary Key (not to heavy on the DB), or the value of
: : > Date()...
: : >
: : > SELECT
: : > Rnd(Table1_PrimaryKeyFieldNameHere),
: : > SecondField,
: : > ThirdField
: : > FROM
: : > Table1
: : > ORDER BY
: : > 1
: : >
: : > should seed the RND() function with the primary key value of
: : > the current
: : > record.
: : >
: : > Are you sure the ASP page isn't being cached someplace?
: : >
: : > Cheers
: : > Ken
:
:
:
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
:
:
:
: ---
:
: Improve your web design skills with these new books from Glasshaus.
:
: Usable Web Menus
: http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
: r-20
: Constructing Accessible Web Sites
: http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
: r-20
: Practical JavaScript for the Usable Web
: http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
: r-20
---
Improve your web design skills with these new books from Glasshaus.
Usable Web Menus
http://www.amazon.com/exec/obidos/ASIN/1904151027/ref=nosim/theprogramme
r-20
Constructing Accessible Web Sites
http://www.amazon.com/exec/obidos/ASIN/1904151000/ref=nosim/theprogramme
r-20
Practical JavaScript for the Usable Web
http://www.amazon.com/exec/obidos/ASIN/1904151051/ref=nosim/theprogramme
r-20
|
|
|
View Cart
