Using an ARRAY to format retrieved records

[Jejune]

I'd like to thank snavebelac for his help so far in this problem.

I'm am working with ASP and I'm am filtering results from an Access DB.

I have the season, year and designer stored in the Database.

Example:
Designer | Season | Year | Url
Paul Smith | AW | 07 | /aw07/menswear/paul_smith
Paul Smith | SS | 07 | /ss07/menswear/paul_smith
Paul Smith | AW | 06 | /aw06/menswear/paul_smith
Paul Smith | SS | 06 | /ss06/menswear/paul_smith
...and so on back to SS85

Paco Rabanne | AW | 07 | /aw07/menswear/paco_rabanne
Paco Rabanne | SS | 07 | /aw07/menswear/paco_rabanne
...and so on


My query looks for all Fashion Designers in Menswear Where the first letter of their name has been specified by the user. e.g. search = P

I would like to format thes returned results grouped by designer and to display the most recent 4 seasons out of a long list. e.g. AW07, SS07, AW06, SS06. Each season is a link.

I can group the results but I only want my dividers ' | ' to show between the seasons and not after the last result.

Currently my results return like this:

Paul Smith
aw07 | ss07 | aw06 | ss06 |

Paco Rabanne
aw07 | ss07 | aw06 | ss06 |

Pierre Henri Mattout
aw07 | ss07 | aw06 | ss06

and I would like it to look like this...

Paul Smith
aw07 | ss07 | aw06 | ss06

Paco Rabanne
aw07 | ss07 | aw06 | ss06

Pierre Henri Mattout
aw07 | ss07 | aw06 | ss06


Where do I need to edit within my array to recifiy this?


My Code

<%
Dim ar, CurrentDesigner, NextDesigner
Dim sOutPut
 
If Not RS_designerLetter.EOF Then
'Get all rows and 4 specific columns from query
ar = RS_designerLetter.GetRows(, , Array("designer", "season", "collectionYear", "url"))
 
If isarray(ar) Then
CurrentDesigner = ar(0,0)
sOutPut = "<p><strong>" & CurrentDesigner & "</strong><br>"
 
' Gets total number of rows in the array
For i = 0 to ubound(ar,2)
 
NextDesigner = ar(0,i)
If CurrentDesigner < NextDesigner then
CurrentDesigner = NextDesigner
sOutPut = sOutPut & "<br><strong>" & CurrentDesigner & "</strong><br>"
End if
sOutPut = sOutPut & "<a href=""" & ar(3,i) & """>" & ar(1,i) & ar(2,i) & "</a>&nbsp;&nbsp;|&nbsp;&nbsp;"
 
Next
'Using Len here only removes the divider on the last record of the last designer 
'Need it to remove the last divider on the last record of each desginer
sOutPut = Left(sOutPut,Len(sOutPut)-25)
Response.Write(sOutPut)
 
Response.Write("<br><br>End of Records Found</p>")
Else
Response.Write("<p>No Records Were Retrieved</p>")
End if
 
End if
%>

[jhardman]

wow, that is totally not how I would have done it, but it is very succinct and well-done. There are a couple ways you could fix this, you could remove the last character of the string every time you start a new line (this appears to be how snavebelac removes the last | of the last line) or you could count how many records you have listed, and on the fourth, don't put in a "|"

Jared

[Jejune]

I have applied a technique that does not use an array. But this resulted in returning the number of returned season x the designer name.

E.G.
If there are 4 seasons for Paul Smith then Paul Smith repeats 4 times will all 4 season underneath the designer name.

Paul Smith
aw07 ss07 aw06 ss06

and then repeats 3 more times same as above


If I limit the recordset RS_designersByC atCity to select where seasonYear = aw07 then it returns the designer name only once with the all seasons underneath.

However, designers who do not have a aw07 show (have ss07,aw06,etc), do not show up in the return results.

Paul Smith
aw07 ss07 aw06 ss06

Designers like Petra Petrov will not appear
Petra Petrov
ss07 aw06




This attempt uses 2 recordsets, one within the other.

  <table width="400" border="0" cellspacing="8" cellpadding="0">
    <tr>
      <td><%=(RS_designersByCatCity.Fields.Item("designer").Value)%></td>
    </tr>
    <tr>
    <td>
    <%
              FilterParam = RS_designersByCatCity.Fields.Item("designerID").Value
              RS_designersSecond.Filter = "designerID = " & FilterParam
              While (NOT RS_designersSecond.EOF)
     %>
     <%=(RS_designersSecond.Fields.Item("seasonYear").Value)%>&nbsp;&nbsp;
      <%
    RS_designersSecond.MoveNext()
    Wend
       %>      
      </td>
      </tr>
  </table>
  <% 
  Repeat2__index=Repeat2__index+1
  Repeat2__numRows=Repeat2__numRows-1
  RS_designersByCatCity.MoveNext()
  Wend
%>

Recordset query if I filter by season 'desSeasonCurr' contains 'aw07' filters from the column 'seasonYear'

RS_designersByCatCity.Source = "SELECT designer, seasonYear, url, designerID, ID_url  FROM QY_designerURLS  WHERE categoryID = " + Replace(RS_designersByCatCity__desCategoryID, "'", "''") + " AND cityID = " + Replace(RS_designersByCatCity__desCityID, "'", "''") + " AND seasonYear = '" + Replace(RS_designersByCatCity__desSeasonCurr, "'", "''") + "'  ORDER BY designer, collectionYear DESC, season ASC"

[jhardman]

well I'm glad you found a solution, even though it wasn't what I suggested. Thanks for sharing what you found.

Jared