Can multi queries be done in ASP pages?

Discussion in 'ASP General' started by Lord Merlin, May 10, 2004.

  1. Lord Merlin

    Lord Merlin Guest

    The following statement works fine in SQL Query Analyser: SELECT count(*) as
    a from NEWUSERS WHERE activated='Y'; SELECT count(*) as b from NEWUSERS
    WHERE activated='N' -- It returns two values, yet when I have it in a .asp
    file, it only displays the first value. Is there anyway around this, without
    making two connections to the DB, and running two seperate queries?

    --

    Kind Regards
    Rudi Ahlers
    +27 (82) 926 1689

    Greater love has no one than this, that he lay down his life for his friends
    (John 15:13).
    Lord Merlin, May 10, 2004
    #1
    1. Advertising

  2. Sure. Assuming your recordset variable is "rs" then try this:

    dim firstvalue, secondvalue
    firstvalue = rs("a")
    set rs = rs.nextRecordset
    secondvalue = rs("b")
    rs.close
    set rs = nothing

    - Wm

    --
    William Morris
    Product Development, Seritas LLC
    Kansas City, Missouri

    "Lord Merlin" <SP4M_Rudi@SP4M_Bonzai.org.za_SP4M> wrote in message
    news:...
    > The following statement works fine in SQL Query Analyser: SELECT count(*)

    as
    > a from NEWUSERS WHERE activated='Y'; SELECT count(*) as b from NEWUSERS
    > WHERE activated='N' -- It returns two values, yet when I have it in a .asp
    > file, it only displays the first value. Is there anyway around this,

    without
    > making two connections to the DB, and running two seperate queries?
    >
    > --
    >
    > Kind Regards
    > Rudi Ahlers
    > +27 (82) 926 1689
    >
    > Greater love has no one than this, that he lay down his life for his

    friends
    > (John 15:13).
    >
    >
    William Morris, May 10, 2004
    #2
    1. Advertising

  3. Lord Merlin wrote:
    > The following statement works fine in SQL Query Analyser: SELECT
    > count(*) as a from NEWUSERS WHERE activated='Y'; SELECT count(*) as b
    > from NEWUSERS WHERE activated='N' -- It returns two values, yet when
    > I have it in a .asp file, it only displays the first value. Is there
    > anyway around this, without making two connections to the DB, and
    > running two seperate queries?


    Why not combine them into a single query?

    SELECT activated,Count(*) from NEWUSERS
    GROUP BY activated

    Bob Barrows
    --
    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], May 10, 2004
    #3
  4. Not that this is a deterrent from your method, but I would bet that
    activated allows NULLs, so you might want to add WHERE activated IN ('Y',
    'N') or WHERE activated IS NOT NULL or something along those lines. Or,
    don't allow NULLs in the column in the first place. ;-)

    (Otherwise, you will get three rows in your resultset.)

    --
    Aaron Bertrand
    SQL Server MVP
    http://www.aspfaq.com/




    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Lord Merlin wrote:
    > > The following statement works fine in SQL Query Analyser: SELECT
    > > count(*) as a from NEWUSERS WHERE activated='Y'; SELECT count(*) as b
    > > from NEWUSERS WHERE activated='N' -- It returns two values, yet when
    > > I have it in a .asp file, it only displays the first value. Is there
    > > anyway around this, without making two connections to the DB, and
    > > running two seperate queries?

    >
    > Why not combine them into a single query?
    >
    > SELECT activated,Count(*) from NEWUSERS
    > GROUP BY activated
    >
    > Bob Barrows
    > --
    > 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.
    >
    >
    Aaron Bertrand - MVP, May 10, 2004
    #4
    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. Richard K Bethell

    IXXSO queries and asp.net search pages

    Richard K Bethell, Nov 21, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    393
    Richard K Bethell
    Nov 24, 2003
  2. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,474
    Crutcher
    Feb 26, 2006
  3. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    340
    Aaron [SQL Server MVP]
    Aug 6, 2004
  4. Zhidian Du
    Replies:
    0
    Views:
    143
    Zhidian Du
    Feb 21, 2004
  5. Ignoramus8495

    Tracing SQL queries done via DBI (mod_perl)

    Ignoramus8495, Apr 21, 2006, in forum: Perl Misc
    Replies:
    6
    Views:
    226
Loading...

Share This Page