Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > classic asp - returning records from access in a random order

Reply
Thread Tools

classic asp - returning records from access in a random order

 
 
InnoCreate
Guest
Posts: n/a
 
      10-23-2006
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

 
Reply With Quote
 
 
 
 
Mike Brind
Guest
Posts: n/a
 
      10-23-2006
"InnoCreate" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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


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


 
Reply With Quote
 
Mike Brind
Guest
Posts: n/a
 
      10-24-2006
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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed) 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed) 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

>



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

> >


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      10-25-2006
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.


 
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
is Random Access File really "random access"? Kevin Java 19 02-13-2006 09:31 PM
Display records similar to Classic ASP using DataList or Repeater Kas_aspnet ASP .Net 1 11-15-2005 08:14 AM
Returning array from Asp.Net webservice via SOAP to classic Asp Steve Kuhn ASP .Net Web Services 2 07-29-2003 07:52 PM



Advertisments