Basic Looping Question

Discussion in 'ASP General' started by JP SIngh, Aug 3, 2005.

  1. JP SIngh

    JP SIngh Guest

    Hi All

    I have threee tables like the one below and i would like to ask if it is
    possible to display the data from all three tables using a single recordset
    in ASP.

    TABLE - tblList

    id - Number
    ListName - Text

    TABLE - tblListMembers

    ListId - Number
    Username - Text

    TABLE - tblEmployees

    UserName - Text
    FirstName - Text
    LastName - Text
    Email - Text

    This is to store data like Distribution lists in outlook.

    What I want to display on one page is to display all the list and the name
    of the members? but the poor code opens two records sets and is very slow

    This is what I am doing at the moment. (not exact code but just a general
    idea)

    SQL = "Select Id, Listname from tblListMembers"
    RS.Open SQL,conn
    Do while not rs.eof
    Response.write "Listname" & "<br>"
    stSQL = "Select * from tblListMembers where listid = " & rs("id")
    rs1.open stSQL, conn
    Do while not RS1.eof
    Response.write "- --- -- " & rs("username") & "<br>"
    rs1.movenext
    loop
    rs1.close
    rs.movenext
    loop


    I am certain opening a record set within a loop continuously is a bad
    practice. Can someone please let me know how can I get this information in
    single recordset and how can I display that.

    Many thanks
    Jas
     
    JP SIngh, Aug 3, 2005
    #1
    1. Advertising

  2. JP SIngh

    Phill. W Guest

    "JP SIngh" <> wrote in message
    news:...
    > I have threee tables like the one below and i would like to ask if it is
    > possible to display the data from all three tables using a single

    recordset
    > in ASP.


    <snip>

    > What I want to display on one page is to display all the list and the name
    > of the members?


    the query :

    select t.ListName
    , e.Username, e.Firstname, e.LastName, e.Email
    from tblList t
    , tlbListMembers lm
    , tblEmployees e
    where t.id = lm.ListId
    and lm.Username = e.Username
    order by t.Listname, e.LastName, e.Firstname

    the skeleton code :

    rs.MoveFirst
    Do While Not rs.EOF
    sPrevList = rs("ListName")
    ' Generate List "heading" and start member list
    Do While Not rs.EOF And rs("ListName") = sPrevList
    ' generate member entry
    rs.MoveNext
    Loop
    ' Close List section
    Loop

    HTH,
    Phill W.
     
    Phill. W, Aug 3, 2005
    #2
    1. Advertising

  3. JP SIngh

    CJM Guest

    "JP SIngh" <> wrote in message
    news:...
    > Hi All
    >
    > I have threee tables like the one below and i would like to ask if it is
    > possible to display the data from all three tables using a single
    > recordset
    > in ASP.
    >


    It is possible. You can UNION similar groups of records to produce a larger
    recordset, and you can JOIN tables together and query them via their
    relationships. It looks like you will need to query these tables using a
    Join.

    > TABLE - tblList
    >
    > id - Number
    > ListName - Text
    >
    > TABLE - tblListMembers
    >
    > ListId - Number
    > Username - Text
    >
    > TABLE - tblEmployees
    >
    > UserName - Text
    > FirstName - Text
    > LastName - Text
    > Email - Text
    >
    > This is to store data like Distribution lists in outlook.
    >
    > What I want to display on one page is to display all the list and the name
    > of the members? but the poor code opens two records sets and is very slow
    >


    In the 3 mins I have spent looking at this, it wasn't clear exactly what you
    are trying to do. Can you explain in some more detail?

    > This is what I am doing at the moment. (not exact code but just a general
    > idea)
    >
    > SQL = "Select Id, Listname from tblListMembers"
    > RS.Open SQL,conn
    > Do while not rs.eof
    > Response.write "Listname" & "<br>"
    > stSQL = "Select * from tblListMembers where listid = " & rs("id")
    > rs1.open stSQL, conn
    > Do while not RS1.eof
    > Response.write "- --- -- " & rs("username") & "<br>"
    > rs1.movenext
    > loop
    > rs1.close
    > rs.movenext
    > loop
    >
    >
    > I am certain opening a record set within a loop continuously is a bad
    > practice. Can someone please let me know how can I get this information in
    > single recordset and how can I display that.


    You right that this isn't the best way to go about it.

    See: http://www.aspfaq.com/show.asp?id=2241

    When we have a better idea about what you are trying to achieve, we'll be
    able to help build better code..

    Chris
     
    CJM, Aug 3, 2005
    #3
    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. Doug
    Replies:
    10
    Views:
    7,433
    teeg01
    Dec 5, 2008
  2. Pekka Niiranen
    Replies:
    5
    Views:
    351
    Pekka Niiranen
    Oct 31, 2004
  3. Engineer
    Replies:
    6
    Views:
    648
    Jeremy Bowers
    May 1, 2005
  4. Replies:
    0
    Views:
    458
  5. Replies:
    5
    Views:
    294
Loading...

Share This Page