Recordset Grouping

Discussion in 'ASP General' started by mstery@soon.com, Jul 7, 2006.

  1. Guest

    I am stumped as to how to do what appears to be a very simple thing.
    Want to display the results of a query containing fields ID and Item,
    grouped by the ID. I only want 1 instance of the ID to appear on the
    page as a header for each group. There are 10 IDs in the query
    results. Thought I could use <% Response.Write (rsName("Item")) WHERE
    ID=1 %>, but doesn't work. How else could I accomplish this? Thanks
    for any help--I only have very basic ASP knowledge.
     
    , Jul 7, 2006
    #1
    1. Advertising

  2. Slim Guest

    <> wrote in message
    news:...
    >I am stumped as to how to do what appears to be a very simple thing.
    > Want to display the results of a query containing fields ID and Item,
    > grouped by the ID. I only want 1 instance of the ID to appear on the
    > page as a header for each group. There are 10 IDs in the query
    > results. Thought I could use <% Response.Write (rsName("Item")) WHERE
    > ID=1 %>, but doesn't work. How else could I accomplish this? Thanks
    > for any help--I only have very basic ASP knowledge.
    >


    sql = "SELECT item FROM yourTable WHERE id = 1"

    rsName.open sql, cnName

    do until rsName.EOF

    Response.Write (rsName("Item"))
    rsName.MoveNext

    loop
     
    Slim, Jul 7, 2006
    #2
    1. Advertising

  3. wrote:
    > I am stumped as to how to do what appears to be a very simple thing.
    > Want to display the results of a query containing fields ID and Item,
    > grouped by the ID. I only want 1 instance of the ID to appear on the
    > page as a header for each group. There are 10 IDs in the query
    > results. Thought I could use <% Response.Write (rsName("Item"))
    > WHERE ID=1 %>, but doesn't work. How else could I accomplish this?
    > Thanks for any help--I only have very basic ASP knowledge.


    dim cn,rs,ar,id, sql, i
    set cn=createobject("adodb.connection")
    cn.open ...
    set rs=cn.execute("select distinct id from yourtable",, 1)
    if not rs.eof then ar=rs.getrows
    rs.close
    if isarray(ar) then
    set rs=createobject("adodb.recordset")
    rs.cursorlocation=3 'adUseClient
    sql="select ID, Item from yourtable"
    rs.open sql,cn,,,1
    set rs.activeconection=nothing
    cn.close:set cn=nothing
    for i = 0 to ubound(ar,2)
    id = ar(0,i)
    response.write id
    rs.filter = "ID=" & id
    do until rs.eof
    response.write "<div style=""margin-left:10"">"
    response.write rs(1) & "</div>"
    loop
    next
    rs.close: set rs=nothing
    else
    response.write "No data was returned"
    end if

    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], Jul 7, 2006
    #3
  4. Guest

    Slim wrote:

    >
    > sql = "SELECT item FROM yourTable WHERE id = 1"
    >
    > rsName.open sql, cnName
    >
    > do until rsName.EOF
    >
    > Response.Write (rsName("Item"))
    > rsName.MoveNext
    >
    > loop


    Thanks for the reply, but if I set the where clause in the query, I'm
    going to have to have 10 queries on my page (one for each ID--which is
    what I'm doing now) in order to display all the items under each ID. I
    had hoped for a better way to do this, since it doesn't seem like I
    should have to run all those queries just to group records by the ID #.
    But maybe I'm wrong and running all those queries is the only way I
    can do this?!?
     
    , Jul 10, 2006
    #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. David Wier
    Replies:
    0
    Views:
    443
    David Wier
    Aug 8, 2003
  2. S. Justin Gengo
    Replies:
    0
    Views:
    1,795
    S. Justin Gengo
    Aug 8, 2003
  3. JemPower
    Replies:
    0
    Views:
    673
    JemPower
    Aug 29, 2003
  4. Craig Buchanan

    Outlook-style grouping?

    Craig Buchanan, Oct 14, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    379
    Craig Buchanan
    Oct 14, 2003
  5. Hung Huynh
    Replies:
    8
    Views:
    308
    Bob Barrows
    Sep 24, 2003
Loading...

Share This Page