Looping through an array

[emozley]

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:

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

 

[ChrisHirst]

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

 

[guitarzan]

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

Response.Write("<table>") 
For i=0 To UBOUND(DocArray, 2) 
   Response.Write("<tr>") 
   Response.Write("<td>" & DocArray(0, i) & "</td>") 

   [COLOR=blue]Response.Write("<td>")[/color]
   CurrentDocID=DocArray(0, i) 
   Do While DocArray(0, i)=CurrentDocID 
      Response.Write(DocArray(1, i) & ", ") 
      i=i+1 
   Loop 
   [COLOR=blue]Response.Write("</td>")[/color]
Next 
Response.Write("</table>")

 

[ChrisHirst]

Still no </tr> though

	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 = "&nbsp;"
	.write Replace(strItem,"[co]",strCompanyName)
	.write " &nbsp;&nbsp;..."
	.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 :-D



Chris.

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

 

[MarkSweetland]

Could do it directly in SQL too:

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