INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Looping through an array

Looping through an array

(OP)
Hi,

I have a SQL table that contains documents and a table that contains users. I have another table that allows you to link more than one user to a document.

I have a query that then returns a list of documents with the users

DocID UserName
1 Jack
1 Jane
2 Ed

Now there is only one document with a DocID of 1 but the join returns two separate rows because there are two people linked to the document.

I then want to display a table that looks like this:

DocID Users
1 Jack, Jane
2 Ed

My recordset is stored in an array so far I have tried this:

CODE

Response.Write("<table>") For i=0 To UBOUND(DocArray, 2) Response.Write("<tr>") Response.Write("<td>" & DocArray(0, i) & "</td>") CurrentDocID=DocArray(0, i) Do While DocArray(0, i)=CurrentDocID Response.Write(DocArray(1, i) & ", ") i=i+1 Loop Next Response.Write("</table>")

The problem I have is that when the name "Jane" is displayed it moves onto the next row in the array by adding 1 to the array and the condition is no longer met so it exits the loop however because it's in a For i=0 To.... Loop, 1 gets added to i again and so the next rows is skipped.

I've been looking at this problem all day and have got to the stage where I can't see the wood for the trees so I would be very greatful for any help.

Thanks very much

Ed

RE: Looping through an array

You do need to close the row with a </tr> somewhere in there.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: Looping through an array

I think you need to add a <TD> </TD> in there, something like

CODE

Response.Write("<table>") For i=0 To UBOUND(DocArray, 2) Response.Write("<tr>") Response.Write("<td>" & DocArray(0, i) & "</td>") Response.Write("<td>") CurrentDocID=DocArray(0, i) Do While DocArray(0, i)=CurrentDocID Response.Write(DocArray(1, i) & ", ") i=i+1 Loop Response.Write("</td>") Next Response.Write("</table>")

RE: Looping through an array

Still no </tr> though

CODE

with response .write "<div align='left'>" & vbCrLf iRecordsShown = 0 .write "<table width='100%' align='center' class='article'>" & vbCrLf 'if bShowThumbs = True then Do While iRecordsShown < iPageSize if objRS.RecordCount > 0 then '1 if not objRS.EOF or not objRS.BOF then .write "<tr>" & vbCrLf .write "<td width='30%' class='article'>" .write "<a href='" .write strShopFolder .write "?item=" .write objRS.Fields("id") .write "'>" if IsNull(objRS.fields("pic_caption")) then .write "Stock Code " .write Cstr(objRS.Fields("order_code")) else .write Proper(objRS.fields("pic_caption")) end if .write "</a>" & vbCrLf .write "</td>" & vbCrLf .write "<td class='article'>" & vbCrLf ' stritem = WordWrap(Proper(objRS.Fields("descript")),15) if IsNull(objRS.fields("details")) then strItem = " Sorry no description available" else if len(objRS.fields("details")) < 100 then strItem = replacemarkup(objRS.fields("details")) else strItem = replacemarkup(GetSnippet(objRS.fields("details"),100)) end if end if ' .write "<br>" & vbCrLf .write "<span class='small'>" & vbCrLf if stritem <> "" then ' strItem = " " .write Replace(strItem,"[co]",strCompanyName) .write "   ..." .write "<span class='right'>" .write "<a href='" .write strShopFolder .write "?item=" .write CInt(objRS.Fields("id")) .write "'>" ' .write "<br>" .write "Show Item ... " .write "</a>" & vbCrLf .write "</span>" & vbCrLf end if ' .write "<br>" & vbCrLf .write "</span>" & vbCrLf '.write "</div>" & vbCrLf 'end if .write "</td>" & vbCrLf iRecordsShown = iRecordsShown + 1 objRS.MoveNext if objRS.eof then flag = true end if ' EOF BOF end if if flag then exit do Loop response.write "</tr>" & vbCrLf end with end If response.write "</table>" & vbCrLf response.write "</div> <!--aligned-->" & vbCrLf
Taken from a project created long ago big smile

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum

RE: Looping through an array

Could do it directly in SQL too:

CODE

declare @tbl TABLE (DocID int, UserName varchar(10)) insert into @tbl(docId, Username) select 1, 'Jack' union all select 1, 'Jane' union all select 2, 'Ed' select * from @tbl select distinct DocId, userList = SUBSTRING( ( SELECT ', ' + a.UserName from @tbl a where a.DocID = b.DocID order by a.DocID FOR XML PATH('') ), 2, 8000 ) from @tbl b


Mark

"You guys pair up in groups of three, then line up in a circle."
- Bill Peterson, a Florida State football coach

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close