classic asp - returning records from access in a random order

Discussion in 'ASP General' started by InnoCreate, Oct 23, 2006.

  1. InnoCreate

    InnoCreate Guest

    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
     
    InnoCreate, Oct 23, 2006
    #1
    1. Advertising

  2. InnoCreate

    Mike Brind Guest

    "InnoCreate" <> wrote in message
    news:...
    > 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
     
    Mike Brind, Oct 23, 2006
    #2
    1. Advertising

  3. InnoCreate

    InnoCreate Guest

    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" <> wrote in message
    > news:...
    > > 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
     
    InnoCreate, Oct 24, 2006
    #3
  4. InnoCreate

    Mike Brind Guest

    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" <> wrote in message
    news:...
    > 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" <> wrote in message
    >> news:...
    >> > 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

    >
     
    Mike Brind, Oct 24, 2006
    #4
  5. InnoCreate

    InnoCreate Guest

    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" <> wrote in message
    > news:...
    > > 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" <> wrote in message
    > >> news:...
    > >> > 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

    > >
     
    InnoCreate, Oct 25, 2006
    #5
  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/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e


    > 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&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=


    --
    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.
     
    Bob Barrows [MVP], Oct 25, 2006
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Kas_aspnet
    Replies:
    1
    Views:
    861
    Eliyahu Goldin
    Nov 15, 2005
  2. Kevin
    Replies:
    19
    Views:
    1,158
    Tris Orendorff
    Feb 13, 2006
  3. globalrev
    Replies:
    4
    Views:
    814
    Gabriel Genellina
    Apr 20, 2008
  4. Steve Kuhn
    Replies:
    2
    Views:
    378
    majiofpersia
    Jul 29, 2003
  5. VK
    Replies:
    15
    Views:
    1,323
    Dr J R Stockton
    May 2, 2010
Loading...

Share This Page