Nested Repeat Region - ASP VBScript - Dreamweaver MX

Hello.  I am using Dreamweaver MX with ASP VBScript (not .NET).

I am trying to create a Nested Repeat Region for a couple of tables:  <products> <productchampions>
(The "product champion" is the expert of a particular product).

Their field layouts are:
<products> {prodNumb, prodName, pcID}
<productchampions> {pcID, pcFirstName, pcLastName}

I can create a "repeating region" to pull the "pcLastName" and "prodNumb", but it will list the name on every row of the product numbers.  Example below:

Last Name:  Product Number
Last Name:  Product Number
Last Name:  Product Number
Last Name:  Product Number


I am trying to get it to only display the last name once, and then list all associated product numbers.  When it has reached the end of all its related product numbers, it will move to the next Last Name and display all of those related product numbers.  Example below:

Last Name: Product Number
                 Product Number
                 Product Number
                 Product Number
Last Name: Product Number
                 Product Number
                 Product Number
                 Product Number

Could someone, please, help me accomplish this?  This is for my team at work.

Thank you,
lshane
lshaneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RaheelHannanCommented:

Their field layouts are:
<products> {prodName, pcID, prodNumb}
<productchampions> {pcID, pcFirstName, pcLastName}

Here is the code

<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%      Option Explicit      %>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<%
      Dim objConn
      Set objConn = Server.CreateObject("ADODB.Connection")
      Dim sConnString
      sConnString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=" & Server.MapPath("./test.mdb")
%>
<table width="75%" border="0">
  <%
            Dim q_numrs
            Set q_numrs = Server.CreateObject("ADODB.Recordset")
            Dim q_sql
            q_sql = "Select * FROM productchampions"
            q_numrs.open q_sql, sConnString
            While not q_numrs.EOF
%>
  <tr bgcolor="#EFEFEF">
    <td colspan="3">First Name : <%=q_numrs("pcfirstname")%></td>
<%
'                  response.Write("First Name : " & q_numrs("pcfirstname") & "<br />")
                        Dim q_num
                        Set q_num = Server.CreateObject("ADODB.Recordset")
                        Dim q_sqli
                        q_sqli = "Select * FROM products Where pcid = " & q_numrs("pcid")& ""
                        q_num.open q_sqli, sConnString
                        While not q_num.EOF
%>
  <tr>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
    <td><%=q_num("proname")%></td>
  </tr>
<%
'                              response.Write("Product Name : " & q_num("proname") & "<br />")
                              q_num.MoveNext
                        Wend
                        q_num.Close
                        set q_num = nothing
                  q_numrs.MoveNext
%>
  </tr>
<%
            Wend
            q_numrs.Close
            set q_numrs = nothing
%>
</table>
</body>
</html>

Hope this will solve ur problem.

Raheel
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ashley BryantSenior Software EngineerCommented:
Personally, I would use a SHAPE command in this instance...  Basically this creates two recordsets out of one sql command.  ADO handles keeping it separate.  This will probably look confusing if you haven't used shape before, but the syntax should be prettymuch spot on based on the table structure you provided.  You'll need to change the Access file name.

<table>
<%
Dim strConn : strConn = "Provider=MSDataShape;Data Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("./test.mdb")

Dim strSql : strSql = "SHAPE{SELECT pcID, pcLastName FROM productchampions ORDER BY pcLastName} " & _
                              "APPEND({SELECT pcID, prodNumb} AS rs2 " & _
                              "RELATE pcID TO pcID)"
dim rs : Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSql, strConn
Do While Not rs.EOF
     Set rs2 = rs("rs2").Value
%>
<tr>
     <td valign="top"><%= rs("pcLastName") %></td>
     <td>
%>
     Do While Not rs2.EOF
          Response.Write rs2("prodNumb") &"<br />"
          rs2.movenext
     Loop
<%
     </td>
</tr>
<%
     rs.movenext
Loop
rs.close
Set rs2 = Nothing
Set rs = Nothing
%>
</table>
0
lshaneAuthor Commented:
Hi, RaheelHannan.  That worked great.  Thank you so much.  I appreciate it.
0
cgustafCommented:
Nested Repeat Regions in Dreamweaver MX

Looks like Ash's solution worked well for Ishane.

I simply wonder if a similar solution is available in PHP as well?

cgustaf
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.