Nested repeat region using SQL stored proc with UNION

[elleme]

I need some help creating a nested repeat region. The parent recordset is fairly basic, selecting address information for a single address for a family with multiple addresses. The child recordset should display all the family members at that address. Because this information comes from more than 1 SQL table, I have created a SQL stored procedure to call the data using the UNION operator.

However, everything I have seen to create a nested repeat region only shows a simple select statement created in the asp code directly on the page. How can I create a nested repeat region where each repeat region shows the data by calling a SQL stored procedure? Here is the SQL code for the parent and child recordsets. The linking fields are familyID and ContactID.

**Parent recordset**

SELECT se.idDCC, c.FamilyID, ContactID, Address, city, state, zipcode, homephone, workphone,
workwho, cellphone, cellwho, MomLanguage, DadLanguage,
AltContactName, AltRelationship, AltPhone1,AltPhone2,
AltPhoneType1,
AltPhoneType2,
PhoneType1,
PhoneType2,
PhoneType3,
Phone1Who,
Alt2ContactName, Alt2Relationship, Alt2Phone1,Alt2Phone2,
Alt2PhoneType1,
Alt2PhoneType2,
Email,
EmailOK,
ContactComments

FROM tblScreenedElig se
INNER JOIN tblContact c
ON se.FamilyID = c.FamilyID AND se.SubjContactID <> c.ContactID
--Select only the "other" contact ids, i.e. non-subject contact IDs
WHERE se.idDCC = @idDCC
ORDER BY c.ContactID

**Child recordset**

SELECT se.FamilyID, SubjContactID AS ContID, se.firstname AS NameFirst, se.lastname AS NameLast,
'Study subject' AS Reln
FROM tblScreenedElig se
WHERE se.idDCC = @idDCC

UNION

SELECT se.FamilyID, MomContactID AS ContID, se.MomFirst AS NameFirst, se.MomLast AS NameLast,
'Mom' AS Reln
FROM tblScreenedElig se
WHERE se.idDCC = @idDCC

UNION

SELECT se.FamilyID, DadContactID AS ContID, se.DadFirst AS NameFirst, se.DadLast AS NameLast,
'Dad' AS Reln
FROM tblScreenedElig se
WHERE se.idDCC = @idDCC

UNION

SELECT s.idDCC, SibContactID AS ContID, s.firstname AS NameFirst, s.lastname AS NameLast,
'Sibling' AS Reln
FROM tblSibling s
INNER JOIN tblScreenedElig se
ON s.idDCC=se.FamilyID
WHERE se.idDCC = @idDCC
ORDER BY ContID

Thanks in advance for your help

 

[Sheco]

This is the ASP forum.

 

[elleme]

Yes, I know it's the ASP forum, but I thought this was an ASP question. I need to create the code in ASP on my web page. I have researched nested repeat regions in ASP and all the solutions are in ASP using very basic SELECT statements. I'm trying to learn how to do a nested repeat region in ASP that instead of using a SELECT statement, calls a SQL stored procedure. I guess I can try posting in the SQL forum as well.

 

[Sheco]

OK. Sorry. I just did a google search on "repeat region asp" and, from what I gather, the Dreamweaver program has a WYSIWYG/wizard called "repeat region" and that it is the wizard that generates some ASP code.

But now you want to do something a little more fancy that is supported by the dreamweaver program. Is that right?

We can help you write the ASP code for your two recordsets but first please explain exactly what results you want to see assuming that we are not familiar with the term "repeat region."

Just looking at your SQL I imagine your outter loop is related to the inner loop via both the FamilyID and idDCC fields so simply using adding an ORDER BY to the SQL won't be sufficient, you'll need to use the .Filter property for the inner recordset loop.

 

[elleme]

Yes, that's what I am looking for. What I want to display on the web page is all of the contact information for a specific contact ID. The combination of FamilyID and ContactID is the primary, unique key. Each contact ID within a family has several family members. So within all of the contact information, I want to list the family members who have that contact ID. However, I also want to list on the same page each contact ID.

Here is a very basic "sketch" of how it would look:

Contact ID 1
Address, etc
Family members: Mom, Grandma, Sister

Contact ID 2
Address
Family members: Dad, Brother

Hopefully that makes more sense. I'm also going to research it on the SQL side.

 

[Sheco]

There is either something wrong with the UNION queries or the table should be redesigned:

Does the table named tblScreenedElig really have these fields:
FamilyID,
SubjContactID,
firstname,
lastname,
MomContactID,
MomFirst,
MomLast,
DadContactID,
DadFirst,
DadLast



The table should probably be like this instead:
FamilyID,
ContactID,
RelationshipType, <-- value can be Mom, Dad, Sibling, Other, etc.
firstname,
lastname