random record with SELECT TOP does NOT work

Discussion in 'ASP General' started by Jimmy, Sep 9, 2006.

  1. Jimmy

    Jimmy Guest

    thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:

    <%
    Dim oConn, oRS, randNum
    Randomize()
    randNum = (CInt(1000 * Rnd) + 1) * -1
    Set oConn=Server.CreateObject("ADODB.Connection")
    Set oRS=Server.CreateObject("ADODB.recordset")
    oConn.Provider="Microsoft.Jet.OLEDB.4.0"
    oConn.Open Server.MapPath("temp.mdb")

    oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

    Response.Write oRS("EMAIL_ADDRESS")

    oRS.close
    oConn.close
    Set oConn = nothing
    Set oRS = nothing
    %>

    this gives the error: "No value given for one or more required parameters".

    what i would really LOVE is for someone to fill in whatever required parameter im missing, but more important i would like to understand whats going on. there are people who go through life dumb and happy once something "works", but i need to understand how and why its working. even if this code did work, im confused with the SELECT statement (no, i dont have much SQL experience aside from basic queries). what is the "r = Rnd" line doing in the middle of the querie? how am i ordering by r?? also, i dont have an indexed, primary "ID" field in this databae. is that ok for this code to work?

    thank you for your help
    Jimmy, Sep 9, 2006
    #1
    1. Advertising

  2. >> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    >> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly


    Do you have adovbs.inc included? Otherwise, where are adOpenStatic and
    adLockReadyOnly defined?

    How about

    Set oRS = oConn.Execute("SELECT ... ORDER BY r")

    > what is the "r = Rnd" line doing in the middle of the querie?


    It's generating a new random number within Access, seeded by the one you
    created in the ASP code.

    > how am i ordering by r??


    You're applying a random number to each row. TOP 1 ... ORDER BY r will give
    you whatever row happened to get the lowest random number. If you don't use
    ORDER BY, then you will likely get the same row over and over again.

    A
    Aaron Bertrand [SQL Server MVP], Sep 9, 2006
    #2
    1. Advertising

  3. Jimmy

    Jimmy Guest

    ok so...

    is there a difference in the way you open the recordset, with the Execute
    statement and the way i do it with oRS.Open? will they both accomplish the
    same thing for the purpose of this piece of code?

    yes i do include adovbs.inc

    and im still confused.... what is wrong with the statement as i have it
    right here:

    oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1
    ORDER BY r", oConn, adOpenStatic, adLockReadOnly

    it seems like just what youre doing, but it doesnt work.


    "Aaron Bertrand [SQL Server MVP]" <> wrote in message
    news:...
    >>> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    >>> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

    >
    > Do you have adovbs.inc included? Otherwise, where are adOpenStatic and
    > adLockReadyOnly defined?
    >
    > How about
    >
    > Set oRS = oConn.Execute("SELECT ... ORDER BY r")
    >
    >> what is the "r = Rnd" line doing in the middle of the querie?

    >
    > It's generating a new random number within Access, seeded by the one you
    > created in the ASP code.
    >
    >> how am i ordering by r??

    >
    > You're applying a random number to each row. TOP 1 ... ORDER BY r will
    > give you whatever row happened to get the lowest random number. If you
    > don't use ORDER BY, then you will likely get the same row over and over
    > again.
    >
    > A
    >
    Jimmy, Sep 9, 2006
    #3
  4. Jimmy

    Jimmy Guest

    that was MY question.

    and i still dont have this working :(

    anyone?


    "Dave Anderson" <> wrote in message
    news:...
    > [please don't toppost on USENET]
    >
    > Jimmy wrote:
    >> is there a difference in the way you open the recordset,
    >> with the Execute statement and the way i do it with
    >> oRS.Open?

    >
    > Yes. His method is more readable, and reflects an understanding that you
    > don't ever want to find yourself worrying about which cursor to use
    > because you ought not be using anything but the static forward readonly
    > type in a stateless application anyway.
    >
    >
    >
    >> will they both accomplish the same thing for the purpose
    >> of this piece of code?

    >
    > Yes, and so would 100 million other things. Aaron is offering you a best
    > practice based on years of experience working with ASP and ADO. He has a
    > whole site full of valuable information for ASP developers, both new and
    > experienced.
    >
    >
    >
    >> and im still confused.... what is wrong with the statement as i have
    >> it right here:
    >>
    >> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    >> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >>
    >> it seems like just what youre doing, but it doesnt work.

    >
    > You didn't answer Aaron's question. What is r=Rnd() doing in the middle of
    > your SQL query?
    >
    >
    >
    >
    > --
    > Dave Anderson
    >
    > Unsolicited commercial email will be read at a cost of $500 per message.
    > Use of this email address implies consent to these terms.
    >
    Jimmy, Sep 10, 2006
    #4
  5. Jimmy

    Evertjan. Guest

    Jimmy wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:

    > and i still dont have this working :(
    >


    No, it hat true?
    Don't you know that topposting is frowned upon by many?
    Because I like topposting.
    Why don't you change to a more aggreable way of posting?
    Because I toppost.
    Why cann't others easily follow your thread?

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Sep 10, 2006
    #5
  6. Jimmy

    Guest Guest

    what the hell are you talking about?



    "Evertjan." <> wrote in message
    news:Xns983A6597D4419eejj99@194.109.133.242...
    > Jimmy wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:
    >
    >> and i still dont have this working :(
    >>

    >
    > No, it hat true?
    > Don't you know that topposting is frowned upon by many?
    > Because I like topposting.
    > Why don't you change to a more aggreable way of posting?
    > Because I toppost.
    > Why cann't others easily follow your thread?
    >
    > --
    > Evertjan.
    > The Netherlands.
    > (Please change the x'es to dots in my emailaddress)
    Guest, Sep 10, 2006
    #6
  7. Jimmy

    Evertjan. Guest

    wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:

    > "Evertjan." <> wrote in message
    > news:Xns983A6597D4419eejj99@194.109.133.242...
    >> Jimmy wrote on 10 sep 2006 in microsoft.public.inetserver.asp.general:
    >>
    >>> and i still dont have this working :(
    >>>

    >>
    >> No, it hat true?
    >> Don't you know that topposting is frowned upon by many?
    >> Because I like topposting.
    >> Why don't you change to a more aggreable way of posting?
    >> Because I toppost.
    >> Why cann't others easily follow your thread?


    [topposting corrected]

    > what the hell are you talking about?


    Please reread, and be polite, hgive a name.

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Sep 10, 2006
    #7
  8. "Jimmy" <> wrote in message
    news:ukG$...
    > ok so...
    >
    > is there a difference in the way you open the recordset, with the Execute
    > statement and the way i do it with oRS.Open? will they both accomplish the
    > same thing for the purpose of this piece of code?
    >
    > yes i do include adovbs.inc
    >
    > and im still confused.... what is wrong with the statement as i have it
    > right here:
    >
    > oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1
    > ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >
    > it seems like just what youre doing, but it doesnt work.


    Did you mean:-

    oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM TABLE1
    ORDER BY r", oConn, adOpenStatic, adLockReadOnly

    ??

    >
    >
    > "Aaron Bertrand [SQL Server MVP]" <> wrote in

    message
    > news:...
    > >>> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    > >>> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly

    > >
    > > Do you have adovbs.inc included? Otherwise, where are adOpenStatic and
    > > adLockReadyOnly defined?
    > >
    > > How about
    > >
    > > Set oRS = oConn.Execute("SELECT ... ORDER BY r")
    > >
    > >> what is the "r = Rnd" line doing in the middle of the querie?

    > >
    > > It's generating a new random number within Access, seeded by the one you
    > > created in the ASP code.
    > >
    > >> how am i ordering by r??

    > >
    > > You're applying a random number to each row. TOP 1 ... ORDER BY r will
    > > give you whatever row happened to get the lowest random number. If you
    > > don't use ORDER BY, then you will likely get the same row over and over
    > > again.
    > >
    > > A
    > >

    >
    >
    Anthony Jones, Sep 11, 2006
    #8
  9. Anthony Jones wrote:
    > "Jimmy" <> wrote in message
    > news:ukG$...
    >> ok so...
    >>
    >> is there a difference in the way you open the recordset, with the
    >> Execute statement and the way i do it with oRS.Open? will they both
    >> accomplish the same thing for the purpose of this piece of code?
    >>
    >> yes i do include adovbs.inc
    >>
    >> and im still confused.... what is wrong with the statement as i have
    >> it right here:
    >>
    >> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    >> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >>
    >> it seems like just what youre doing, but it doesnt work.

    >
    > Did you mean:-
    >
    > oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
    > TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >


    You are not allowed to order by a column alias in JetSQL. However, you can
    order by the ordinal position of a column:
    > TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly


    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Sep 11, 2006
    #9
  10. "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Anthony Jones wrote:
    > > "Jimmy" <> wrote in message
    > > news:ukG$...
    > >> ok so...
    > >>
    > >> is there a difference in the way you open the recordset, with the
    > >> Execute statement and the way i do it with oRS.Open? will they both
    > >> accomplish the same thing for the purpose of this piece of code?
    > >>
    > >> yes i do include adovbs.inc
    > >>
    > >> and im still confused.... what is wrong with the statement as i have
    > >> it right here:
    > >>
    > >> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    > >> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    > >>
    > >> it seems like just what youre doing, but it doesnt work.

    > >
    > > Did you mean:-
    > >
    > > oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
    > > TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    > >

    >
    > You are not allowed to order by a column alias in JetSQL. However, you can
    > order by the ordinal position of a column:
    > > TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly

    >


    You see this is why I don't answer Jet based questions there are always
    nuances I miss. Should've stuck to my usual policy ;)

    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    Anthony Jones, Sep 11, 2006
    #10
  11. Jimmy

    Evertjan. Guest

    Bob Barrows [MVP] wrote on 11 sep 2006 in
    microsoft.public.inetserver.asp.general:

    > You are not allowed to order by a column alias in JetSQL. However, you
    > can order by the ordinal position of a column:
    >


    Regarding JetSQL, Bob, do you mean that:

    "SELECT * FROM TABLE1 ORDER BY 0"

    orders by the first field in the database, as set up in Access,
    and

    "SELECT p,q,r FROM TABLE1 ORDER BY 0"

    orders by field p, even if p stands 3rd in the Access setup order?


    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Sep 11, 2006
    #11
  12. Jimmy

    Jimmy Guest

    i wish so much that someone could make sense of this and make it work...

    here is the query i have, which many people from this group have copied and
    pasted from a website where i also found the query, and it does not work:

    "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TBL_SECRETS ORDER
    BY r"

    can anyone see why this doesnt work and possibly make it work?




    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Anthony Jones wrote:
    >> "Jimmy" <> wrote in message
    >> news:ukG$...
    >>> ok so...
    >>>
    >>> is there a difference in the way you open the recordset, with the
    >>> Execute statement and the way i do it with oRS.Open? will they both
    >>> accomplish the same thing for the purpose of this piece of code?
    >>>
    >>> yes i do include adovbs.inc
    >>>
    >>> and im still confused.... what is wrong with the statement as i have
    >>> it right here:
    >>>
    >>> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    >>> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >>>
    >>> it seems like just what youre doing, but it doesnt work.

    >>
    >> Did you mean:-
    >>
    >> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
    >> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >>

    >
    > You are not allowed to order by a column alias in JetSQL. However, you can
    > order by the ordinal position of a column:
    >> TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly

    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    Jimmy, Sep 11, 2006
    #12
  13. Evertjan. wrote:
    > Bob Barrows [MVP] wrote on 11 sep 2006 in
    > microsoft.public.inetserver.asp.general:
    >
    >> You are not allowed to order by a column alias in JetSQL. However,
    >> you can order by the ordinal position of a column:
    >>

    >
    > Regarding JetSQL, Bob, do you mean that:
    >
    > "SELECT * FROM TABLE1 ORDER BY 0"
    >
    > orders by the first field in the database, as set up in Access,
    > and
    >
    > "SELECT p,q,r FROM TABLE1 ORDER BY 0"
    >
    > orders by field p, even if p stands 3rd in the Access setup order?



    I'm pretty sure the ordinal position in this context starts at 1, not 0. I'd
    have to try it to be sure.
    The ordinal position refers to the columns that appear in the select list,
    not to the columns as they appear in the table. Of course, selstar makes the
    select list equal to the Access setup order.

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Sep 11, 2006
    #13
  14. Jimmy

    Evertjan. Guest

    Bob Barrows [MVP] wrote on 11 sep 2006 in
    microsoft.public.inetserver.asp.general:

    >> "SELECT p,q,r FROM TABLE1 ORDER BY 0"
    >>
    >> orders by field p, even if p stands 3rd in the Access setup order?

    >
    >
    > I'm pretty sure the ordinal position in this context starts at 1, not
    > 0. I'd have to try it to be sure.
    > The ordinal position refers to the columns that appear in the select
    > list, not to the columns as they appear in the table. Of course,
    > selstar makes the select list equal to the Access setup order.
    >


    You are right:

    Microsoft JET Database Engine error '80040e14'

    The Microsoft Jet database engine does not recognize '0' as a valid field
    name or expression.

    [Still a bit strange that field 0 in the star sense is field 1.]

    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Sep 11, 2006
    #14
  15. Evertjan. wrote:

    >
    > [Still a bit strange that field 0 in the star sense is field 1.]
    >

    It's znzlogous to the Ubound and LBound functions: the dimension
    identifiers start at 1, not 0.
    --
    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], Sep 11, 2006
    #15
  16. Bob Barrows [MVP] wrote:
    > Evertjan. wrote:
    >
    >>
    >> [Still a bit strange that field 0 in the star sense is field 1.]
    >>

    > It's znzlogous


    Errmmm, that should, of course, be "analogous"
    --
    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], Sep 11, 2006
    #16
  17. "Jimmy" <> wrote in message
    news:...
    > i wish so much that someone could make sense of this and make it work...
    >
    > here is the query i have, which many people from this group have copied

    and
    > pasted from a website where i also found the query, and it does not work:
    >
    > "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TBL_SECRETS

    ORDER
    > BY r"
    >
    > can anyone see why this doesnt work and possibly make it work?
    >


    Hello, Hello... is this mic on??

    Have you read any of the responses to your question??

    >
    >
    >
    > "Bob Barrows [MVP]" <> wrote in message
    > news:...
    > > Anthony Jones wrote:
    > >> "Jimmy" <> wrote in message
    > >> news:ukG$...
    > >>> ok so...
    > >>>
    > >>> is there a difference in the way you open the recordset, with the
    > >>> Execute statement and the way i do it with oRS.Open? will they both
    > >>> accomplish the same thing for the purpose of this piece of code?
    > >>>
    > >>> yes i do include adovbs.inc
    > >>>
    > >>> and im still confused.... what is wrong with the statement as i have
    > >>> it right here:
    > >>>
    > >>> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    > >>> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    > >>>
    > >>> it seems like just what youre doing, but it doesnt work.
    > >>
    > >> Did you mean:-
    > >>
    > >> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
    > >> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    > >>

    > >
    > > You are not allowed to order by a column alias in JetSQL. However, you

    can
    > > order by the ordinal position of a column:
    > >> TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly

    > >
    > > --
    > > Microsoft MVP - ASP/ASP.NET
    > > Please reply to the newsgroup. This email account is my spam trap so I
    > > don't check it very often. If you must reply off-line, then remove the
    > > "NO SPAM"
    > >

    >
    >
    Anthony Jones, Sep 11, 2006
    #17
  18. Jimmy

    Jimmy Guest

    yes and none have given a clear answer.
    its funny but the code i originally found on the aspfaq site is apparently
    what everyone uses to answer a question regarding random records in access,
    however it doesnt work!! LOL... pretty funny actually. but yes, i have
    confirmed that it does not work as written, and all of the "guru's" that
    were so quick to have an attitude in the beginning have since shut up
    because they cant look at the SQL query and figure out whats wrong with it.
    oh well, ill continue to use the function that i wrote which works fine. and
    if anyone else would like to point out how inefficient my function is, and
    offer another solution, have the sense to test it first!


    "Anthony Jones" <> wrote in message
    news:...
    >
    > "Jimmy" <> wrote in message
    > news:...
    >> i wish so much that someone could make sense of this and make it work...
    >>
    >> here is the query i have, which many people from this group have copied

    > and
    >> pasted from a website where i also found the query, and it does not work:
    >>
    >> "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TBL_SECRETS

    > ORDER
    >> BY r"
    >>
    >> can anyone see why this doesnt work and possibly make it work?
    >>

    >
    > Hello, Hello... is this mic on??
    >
    > Have you read any of the responses to your question??
    >
    >>
    >>
    >>
    >> "Bob Barrows [MVP]" <> wrote in message
    >> news:...
    >> > Anthony Jones wrote:
    >> >> "Jimmy" <> wrote in message
    >> >> news:ukG$...
    >> >>> ok so...
    >> >>>
    >> >>> is there a difference in the way you open the recordset, with the
    >> >>> Execute statement and the way i do it with oRS.Open? will they both
    >> >>> accomplish the same thing for the purpose of this piece of code?
    >> >>>
    >> >>> yes i do include adovbs.inc
    >> >>>
    >> >>> and im still confused.... what is wrong with the statement as i have
    >> >>> it right here:
    >> >>>
    >> >>> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
    >> >>> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >> >>>
    >> >>> it seems like just what youre doing, but it doesnt work.
    >> >>
    >> >> Did you mean:-
    >> >>
    >> >> oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, Rnd(" & randNum & ") As r FROM
    >> >> TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >> >>
    >> >
    >> > You are not allowed to order by a column alias in JetSQL. However, you

    > can
    >> > order by the ordinal position of a column:
    >> >> TABLE1 ORDER BY 2", oConn, adOpenStatic, adLockReadOnly
    >> >
    >> > --
    >> > Microsoft MVP - ASP/ASP.NET
    >> > Please reply to the newsgroup. This email account is my spam trap so I
    >> > don't check it very often. If you must reply off-line, then remove the
    >> > "NO SPAM"
    >> >

    >>
    >>

    >
    >
    Jimmy, Sep 11, 2006
    #18
  19. Jimmy

    Larry Bud Guest

    Jimmy wrote:
    > thanks to everyone that helped, unfortunately the code samples people gave me don't work. here is what i have so far:
    >
    > <%
    > Dim oConn, oRS, randNum
    > Randomize()
    > randNum = (CInt(1000 * Rnd) + 1) * -1
    > Set oConn=Server.CreateObject("ADODB.Connection")
    > Set oRS=Server.CreateObject("ADODB.recordset")
    > oConn.Provider="Microsoft.Jet.OLEDB.4.0"
    > oConn.Open Server.MapPath("temp.mdb")
    >
    > oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
    >
    > Response.Write oRS("EMAIL_ADDRESS")
    >
    > oRS.close
    > oConn.close
    > Set oConn = nothing
    > Set oRS = nothing
    > %>


    That SQL definitely does NOT work in Access 2003. It ends up giving
    the column R the same value for each row.

    Here's the real answer

    SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;

    where mytable is your table that contains email_address and ID. ID is
    important, as it's a unique integer for each row. It doesn't matter
    how the rows are numbered, but as long as each one is unique, you get a
    different value for the 2nd column on each row. ORDER BY 2 orders by
    the 2nd column listed.

    NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to a
    message BEFORE or ABOVE or ON TOP of the previous messages. The
    problem is that if someone needs to read the whole posted thread, they
    have to start at the bottom and work their way to the top. It'd be
    like reading a newspaper column a paragraph at a time, starting with
    the last paragraph.

    Hope this helps.
    Larry Bud, Sep 11, 2006
    #19
  20. Jimmy wrote:
    > yes and none have given a clear answer.
    > its funny but the code i originally found on the aspfaq site is
    > apparently what everyone uses to answer a question regarding random
    > records in access, however it doesnt work!! LOL... pretty funny
    > actually. but yes, i have confirmed that it does not work as written,
    > and all of the "guru's" that were so quick to have an attitude in the
    > beginning have since shut up because they cant look at the SQL query
    > and figure out whats wrong with it. oh well, ill continue to use the
    > function that i wrote which works fine. and if anyone else would like
    > to point out how inefficient my function is, and offer another
    > solution, have the sense to test it first!


    We have shut up because you spend more time whining that "it doesn't work"
    than giving useful details that would help us help you.

    I have to say I lost interest in helping you when you admitted on your
    *12th* message that you still didn't have a DB up and running with which to
    test all of the advice you were getting.

    Meet us halfway, and we can be an accommodating bunch. Make us work to give
    you advice, and you will get what you paid for: nothing.



    --
    Dave Anderson

    Unsolicited commercial email will be read at a cost of $500 per message. Use
    of this email address implies consent to these terms.
    Dave Anderson, Sep 11, 2006
    #20
    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. Lars-Erik Aabech
    Replies:
    8
    Views:
    821
    Lars-Erik Aabech
    Apr 28, 2005
  2. Frederic HOUDE
    Replies:
    4
    Views:
    586
    Frederic HOUDE
    Aug 31, 2004
  3. globalrev
    Replies:
    4
    Views:
    741
    Gabriel Genellina
    Apr 20, 2008
  4. Pranjal Jain
    Replies:
    3
    Views:
    142
    saras
    Apr 10, 2008
  5. VK
    Replies:
    15
    Views:
    1,113
    Dr J R Stockton
    May 2, 2010
Loading...

Share This Page