473,289 Members | 2,155 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

classic asp - returning records from access in a random order

Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on my website which allows
users to define parameters and return results accordingly. The problem
i have is a need to return these results in a random order each time.
With SQLServer i know NEWID() would do the trick - used this many times
before but how do you acheive this with access?
After searching google for ages and trying many different things i came
upon this solution.
module called randomizer with the following code:
Public Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

Then i created a view with a simular sql statement as below
SELECT col1,
col2,
col3,
Rnd(isNull([Accomm_data].[Accomm_id]) * 0 + 1) AS ID
FROM accomm_data
ORDER BY member, rnd(isnull([accomm_data].[accomm_id])*0+1);

When i open this view in access each time it returns the results in a
different order. However when i use an sql statement in access with an
adodb object to pull the results from the access query it returns the
same results everytime :S Any idea's of what could be going on here?
If not anyone have any other ways of returning results in a random
order.
To clarify i do not want to select a random 3 records - i want to
return all records that match the criteria but in a random order all
into the same ADODB.Recordset object.
Regards
James Brand

Oct 23 '06 #1
6 5445
"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.com...
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on my website which allows
users to define parameters and return results accordingly. The problem
i have is a need to return these results in a random order each time.
With SQLServer i know NEWID() would do the trick - used this many times
before but how do you acheive this with access?
After searching google for ages and trying many different things i came
upon this solution.
module called randomizer with the following code:
Public Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

Then i created a view with a simular sql statement as below
SELECT col1,
col2,
col3,
Rnd(isNull([Accomm_data].[Accomm_id]) * 0 + 1) AS ID
FROM accomm_data
ORDER BY member, rnd(isnull([accomm_data].[accomm_id])*0+1);

When i open this view in access each time it returns the results in a
different order. However when i use an sql statement in access with an
adodb object to pull the results from the access query it returns the
same results everytime :S Any idea's of what could be going on here?
If not anyone have any other ways of returning results in a random
order.
To clarify i do not want to select a random 3 records - i want to
return all records that match the criteria but in a random order all
into the same ADODB.Recordset object.
Regards
James Brand
If you can get the result you want from running that statement within
Access, then save it as a query (eg qryRandomOrder) and call that using ADO.

Set rs = Server.CreateObject("ADODB.Recordset")
conn.qryRandomOrder rs

See if that works.

--
Mike Brind
Oct 23 '06 #2
Hi mike thanks for the suggestion but unfortunatly i need to be able to
query the access query using an sql statement as the SQL parameters
could change with each query and there are an infinite number of
possibilities so i can't just save them as multiple access queries etc.
Mike Brind wrote:
"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.com...
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on my website which allows
users to define parameters and return results accordingly. The problem
i have is a need to return these results in a random order each time.
With SQLServer i know NEWID() would do the trick - used this many times
before but how do you acheive this with access?
After searching google for ages and trying many different things i came
upon this solution.
module called randomizer with the following code:
Public Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

Then i created a view with a simular sql statement as below
SELECT col1,
col2,
col3,
Rnd(isNull([Accomm_data].[Accomm_id]) * 0 + 1) AS ID
FROM accomm_data
ORDER BY member, rnd(isnull([accomm_data].[accomm_id])*0+1);

When i open this view in access each time it returns the results in a
different order. However when i use an sql statement in access with an
adodb object to pull the results from the access query it returns the
same results everytime :S Any idea's of what could be going on here?
If not anyone have any other ways of returning results in a random
order.
To clarify i do not want to select a random 3 records - i want to
return all records that match the criteria but in a random order all
into the same ADODB.Recordset object.
Regards
James Brand

If you can get the result you want from running that statement within
Access, then save it as a query (eg qryRandomOrder) and call that using ADO.

Set rs = Server.CreateObject("ADODB.Recordset")
conn.qryRandomOrder rs

See if that works.

--
Mike Brind
Oct 24 '06 #3
I'm confused. Where do parameters fit in your original SQL? There's no
"Where" clause? And you don't need to save mutliple hard-coded queries. If
you are using parameters, you can create a saved parameter query.

--
Mike Brind
"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
Hi mike thanks for the suggestion but unfortunatly i need to be able to
query the access query using an sql statement as the SQL parameters
could change with each query and there are an infinite number of
possibilities so i can't just save them as multiple access queries etc.
Mike Brind wrote:
>"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11*********************@m7g2000cwm.googlegro ups.com...
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on my website which allows
users to define parameters and return results accordingly. The problem
i have is a need to return these results in a random order each time.
With SQLServer i know NEWID() would do the trick - used this many times
before but how do you acheive this with access?
After searching google for ages and trying many different things i came
upon this solution.
module called randomizer with the following code:
Public Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

Then i created a view with a simular sql statement as below
SELECT col1,
col2,
col3,
Rnd(isNull([Accomm_data].[Accomm_id]) * 0 + 1) AS ID
FROM accomm_data
ORDER BY member, rnd(isnull([accomm_data].[accomm_id])*0+1);

When i open this view in access each time it returns the results in a
different order. However when i use an sql statement in access with an
adodb object to pull the results from the access query it returns the
same results everytime :S Any idea's of what could be going on here?
If not anyone have any other ways of returning results in a random
order.
To clarify i do not want to select a random 3 records - i want to
return all records that match the criteria but in a random order all
into the same ADODB.Recordset object.
Regards
James Brand

If you can get the result you want from running that statement within
Access, then save it as a query (eg qryRandomOrder) and call that using
ADO.

Set rs = Server.CreateObject("ADODB.Recordset")
conn.qryRandomOrder rs

See if that works.

--
Mike Brind

Oct 24 '06 #4
Displaying a Recordset in Random Order

http://www.4guysfromrolla.com/webtech/110800-1.3.shtml
Oct 25 '06 #5
Sorry in the above query there are no parameters - this is the access
query SQL. I am however then using an sql statement which is
dynamically created to query the MS Query.
It's the dynamic sql statement which is changing everytime.
For example "SELECT * FROM access_query1 WHERE acol = ? AND bcol = ?"
Etc etc
I know this is a bit of a long winded way of doing things but i tried
to run the access query SQL with the matching WHERE clause and the
ADODB just returned an error saying invalid function. Persumably access
doesnt allow the ADODB object to access it's custom functions.
I think i might have to use Jons suggestion. It's not really how i want
to do it though and will mean changing a ton of code.
Regards

Mike Brind wrote:
I'm confused. Where do parameters fit in your original SQL? There's no
"Where" clause? And you don't need to save mutliple hard-coded queries. If
you are using parameters, you can create a saved parameter query.

--
Mike Brind
"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11**********************@f16g2000cwb.googlegr oups.com...
Hi mike thanks for the suggestion but unfortunatly i need to be able to
query the access query using an sql statement as the SQL parameters
could change with each query and there are an infinite number of
possibilities so i can't just save them as multiple access queries etc.
Mike Brind wrote:
"InnoCreate" <Ja***@InnoCreate.comwrote in message
news:11*********************@m7g2000cwm.googlegrou ps.com...
Hi everyone.
I've recently written a classic asp website which uses an MS Access
datasource. I know this is less than an ideal data source as it has
limited functionality. I have a search form on my website which allows
users to define parameters and return results accordingly. The problem
i have is a need to return these results in a random order each time.
With SQLServer i know NEWID() would do the trick - used this many times
before but how do you acheive this with access?
After searching google for ages and trying many different things i came
upon this solution.
module called randomizer with the following code:
Public Function Randomizer() As Integer
Static AlreadyDone As Integer
If AlreadyDone = False Then Randomize: AlreadyDone = True
Randomizer = 0
End Function

Then i created a view with a simular sql statement as below
SELECT col1,
col2,
col3,
Rnd(isNull([Accomm_data].[Accomm_id]) * 0 + 1) AS ID
FROM accomm_data
ORDER BY member, rnd(isnull([accomm_data].[accomm_id])*0+1);

When i open this view in access each time it returns the results in a
different order. However when i use an sql statement in access with an
adodb object to pull the results from the access query it returns the
same results everytime :S Any idea's of what could be going on here?
If not anyone have any other ways of returning results in a random
order.
To clarify i do not want to select a random 3 records - i want to
return all records that match the criteria but in a random order all
into the same ADODB.Recordset object.
Regards
James Brand


If you can get the result you want from running that statement within
Access, then save it as a query (eg qryRandomOrder) and call that using
ADO.

Set rs = Server.CreateObject("ADODB.Recordset")
conn.qryRandomOrder rs

See if that works.

--
Mike Brind
Oct 25 '06 #6
InnoCreate wrote:
Sorry in the above query there are no parameters - this is the access
query SQL. I am however then using an sql statement which is
dynamically created to query the MS Query.
It's the dynamic sql statement which is changing everytime.
For example "SELECT * FROM access_query1 WHERE acol = ? AND bcol = ?"
See those ?s - those are parameters. Take a look at this:
http://groups-beta.google.com/group/...e36562fee7804e

Etc etc
I know this is a bit of a long winded way of doing things but i tried
to run the access query SQL with the matching WHERE clause and the
ADODB just returned an error saying invalid function. Persumably
access doesnt allow the ADODB object to access it's custom functions.
I think i might have to use Jons suggestion. It's not really how i
want to do it though and will mean changing a ton of code.
See these as well if you wish to use saved parameter queries:
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Oct 25 '06 #7

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

Similar topics

3
by: Bob Bedford | last post by:
I've a site where companies add their article. I'de like to provide a "lasts articles" table. By this, I'll show last articles inserted. But I won't always the same articles at any refresh....
4
by: Keith Griffiths | last post by:
I'm trying to do a search under a set criteria followed by a selection of random entries meeting this criteria. But I don't seem to be able to achieve this. The idea being to search on say...
26
by: Chris Lasher | last post by:
Hello, I have a rather large (100+ MB) FASTA file from which I need to access records in a random order. The FASTA format is a standard format for storing molecular biological sequences. Each...
1
by: Manton | last post by:
Access 2000. I have a field in a table (tbl_data entry) which has a field called Free and another field called SD (containing 2 options: 5 and 6) The Free field is a yes/no format. I'd like to...
22
by: Nhmiller | last post by:
Is there a way to do this? Thanks. Neil Cat Paintings At Carol Wilson Gallery http://www.carolwilsongallery.com
3
by: VMI | last post by:
How can I get the bottom N records from an Access table and store them in my DataTable? For example, in my Access table with 2000 records, if I want to display records 151-200 (with ID as PK), my...
1
by: Kas_aspnet | last post by:
Hi, I have to display a sequence of records in an Aspx page. The problem is, i have to display the records by checking conditions like Classic ASP. For example, consider an employee table. In...
10
by: connyledin | last post by:
Im trying to create a version of the game Wumpus. Mine is called Belzebub. But im STUCK! And its due tuesday 2 maj. Im panicing! Can some one help me?? here is the file:...
3
by: Cindy | last post by:
I'm trying to use the NEWID function in dynamic SQL and get an error message Incorrect syntax near the keyword 'ORDER'. Looks like I can't do an insert with an Order by clause. Here's the code:...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

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.