473,324 Members | 2,254 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Using an ARRAY to format retrieved records

4
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

Expand|Select|Wrap|Line Numbers
  1. <%
  2. Dim ar, CurrentDesigner, NextDesigner
  3. Dim sOutPut
  4.  
  5. If Not RS_designerLetter.EOF Then
  6. 'Get all rows and 4 specific columns from query
  7. ar = RS_designerLetter.GetRows(, , Array("designer", "season", "collectionYear", "url"))
  8.  
  9. If isarray(ar) Then
  10. CurrentDesigner = ar(0,0)
  11. sOutPut = "<p><strong>" & CurrentDesigner & "</strong><br>"
  12.  
  13. ' Gets total number of rows in the array
  14. For i = 0 to ubound(ar,2)
  15.  
  16. NextDesigner = ar(0,i)
  17. If CurrentDesigner < NextDesigner then
  18. CurrentDesigner = NextDesigner
  19. sOutPut = sOutPut & "<br><strong>" & CurrentDesigner & "</strong><br>"
  20. End if
  21. sOutPut = sOutPut & "<a href=""" & ar(3,i) & """>" & ar(1,i) & ar(2,i) & "</a>&nbsp;&nbsp;|&nbsp;&nbsp;"
  22.  
  23. Next
  24. 'Using Len here only removes the divider on the last record of the last designer 
  25. 'Need it to remove the last divider on the last record of each desginer
  26. sOutPut = Left(sOutPut,Len(sOutPut)-25)
  27. Response.Write(sOutPut)
  28.  
  29. Response.Write("<br><br>End of Records Found</p>")
  30. Else
  31. Response.Write("<p>No Records Were Retrieved</p>")
  32. End if
  33.  
  34. End if
  35. %>
Jul 5 '07 #1
3 1837
jhardman
3,406 Expert 2GB
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
Jul 5 '07 #2
Jejune
4
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_designersByCatCity 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.


Expand|Select|Wrap|Line Numbers
  1.   <table width="400" border="0" cellspacing="8" cellpadding="0">
  2.     <tr>
  3.       <td><%=(RS_designersByCatCity.Fields.Item("designer").Value)%></td>
  4.     </tr>
  5.     <tr>
  6.     <td>
  7.     <%
  8.               FilterParam = RS_designersByCatCity.Fields.Item("designerID").Value
  9.               RS_designersSecond.Filter = "designerID = " & FilterParam
  10.               While (NOT RS_designersSecond.EOF)
  11.      %>
  12.      <%=(RS_designersSecond.Fields.Item("seasonYear").Value)%>&nbsp;&nbsp;
  13.       <%
  14.     RS_designersSecond.MoveNext()
  15.     Wend
  16.        %>      
  17.       </td>
  18.       </tr>
  19.   </table>
  20.   <% 
  21.   Repeat2__index=Repeat2__index+1
  22.   Repeat2__numRows=Repeat2__numRows-1
  23.   RS_designersByCatCity.MoveNext()
  24.   Wend
  25. %>

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

Expand|Select|Wrap|Line Numbers
  1. 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"
Jul 6 '07 #3
jhardman
3,406 Expert 2GB
well I'm glad you found a solution, even though it wasn't what I suggested. Thanks for sharing what you found.

Jared
Jul 6 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Rvenkatesh | last post by:
How to use array string in C#.Net to compare and replace characters between strings using function.
4
by: rafeekb | last post by:
Hello All, Is there any way to export data in the IXF version 1 format from a DB2 v8.2 database ? The problem I am facing is to get the data from a v8.2 database which produces a version 2...
1
by: nel | last post by:
hi.. i want to know how to insert data using array in mysql where the data stored in field given? this field_name i taken from database.... thanks a lot...
0
by: sachin10 | last post by:
hi i m sachin, i m new to VB programming.i face some problem regarding the sorting of text file contents using arrays.text file have data in the format as below seq...
12
by: astri | last post by:
i`m doing my thesis comparing CORDIC with polynomial in counting arctan with fixed point. I`m using Q15 format now. I`m using this site CORDIC arctan as a referenced when making with floating...
2
by: palani12kumar | last post by:
Hi i've implemented a linear Queue using array. But i've been struck up with a doubt!!!!! Let me tell my problem with an example so that it can be clear. Size of the Queue(array) is 5. im...
1
shoonya
by: shoonya | last post by:
i am using array as a data type in my db .... ( sequence integer ) ; and it stores a value let {1,2,3,4,5}
2
by: berrylthird | last post by:
This question was inspired by scripting languages such as JavaScript. In JavaScript, I can access members of a class using array syntax as in the following example: var myInstance:myClass = new...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.