Basic Looping Question

[JP SIngh]

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

[Phill. W]

"JP SIngh" <no**@none.com> wrote in message
news:OC**************@TK2MSFTNGP12.phx.gbl...

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.

[CJM]

"JP SIngh" <no**@none.com> wrote in message
news:OC**************@TK2MSFTNGP12.phx.gbl...

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