I need some help creating a nested repeat region in ASP. I tried posting in the ASP forum, but was told to look elsewhere. 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
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
