PDA

View Full Version : Comparing a RecordSet with an Array?



RTMac
13 Oct 2006, 12:21 PM
Hello Everyone;

Here's my goal: 1) to set up a 7 column table containing a list of 147 numbers from a recordset (StNumberRS) and 2) to compare those numbers to a separate recordset containing a partial list of those numbers (completedStores). If one of the numbers in the completedStores list matches a number on the StNumberRS list show the 'icon_yes.gif' image, otherwise show the 'icon_no.gif'.

I can loop through the StNumberRS and set up the 7 column table, but I'm stuck trying to loop through the completedStores list while inside the StNumberRS loop.

Could someone look through my code and see where I've gone wrong?

Thank you!


<%
Dim StoreRS, Query, AltRow, ProjectQuery, ProjectRS, CompletedStores_variable, strValue, CompletedStores_Array

ProjectQuery = "SELECT * FROM Projects WHERE Id =" & Request("Id")
Set ProjectRS = Connect.Execute(ProjectQuery)
CompletedStores_variable = ProjectRS("CompletedStores")
CompletedStores_Array = Split(CompletedStores_variable,",")

Dim StNumber_variable, StoreList_Array
Query = "SELECT StNumber FROM Stores ORDER BY StNumber"
Set StoreRS = Connect.Execute(Query)
StNumber_variable = StoreRS("StNumber")
StoreList_Array = Split(StNumber_variable,",")

%>
<table width="100%" border="0" cellspacing="0" cellpadding="10">
<tr>
<td>
<%
If Not StoreRS.EOF Then
%>
<!-- Display store list in a table, showing 7 stores in each row: -->
<TABLE width="800" cellspacing="0" cellpadding="2">
<tr><td colspan="14" align="center" bgcolor="#FFFF00" class="subtitle">PROJECT STATUS</td></tr>
<tr class="required">
<td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td><td>STORE #</td><td>DONE</td></tr>
<tr bgcolor="#CCCCCC"><td colspan="14"><img src="../images/spacer.gif" width="1" height="1"></td></tr>


<%
Dim i, theCount, remainder

theCount = 0
i = 0
Do While Not StoreRS.EOF
' The StoreList_Array contains 147 numbers
For i = LBound(StoreList_Array) TO UBound(StoreList_Array)
' The CompletedStores_Array can vary from zero to 147 elements. Each strValue is one element of the array.
'For Each strValue In CompletedStores_Array
' As long as theCount equals 7, create an empty row separaating the table rows...
remainder = theCount Mod 7
If remainder = 0 then
If theCount <> 0 then
Response.Write "<tr><td colspan='14'><img src='../images/spacer.gif' width='1' height='1'></td></tr>"
Response.Write "<TR>"
End If
End If
'... and create a table row populated with 7 elements from the StoreRS("StNumber").
If StoreRS("StNumber") = CompletedStores_Array(i) Then
Response.Write "<TD align='right' valign=top>" & StoreRS("StNumber") & "</td><TD align='center' valign=top><img src='../images/icon_yes.gif' width='13' height='13'></TD>"

Else
' Output value of current CompletedStores_Array element for debugging
Response.Write "<TD align='right' valign=top>" & StoreRS("StNumber") & "</td><TD align='center' valign=top><img src='../images/icon_no.gif' width='13' height='13'>" & CompletedStores_Array(i) & "</TD>"
End If

If remainder = 6 then
Response.Write "</TR>" & VbCrLf
End If

theCount = theCount + 1
i = i + 1
StoreRS.MoveNext
'next
next
Loop

' close out the last table row if its not already
If remainder = 0 then
Response.Write "<TD colspan='12'>&nbsp;</TD></TR>" & VbCrLf
End If

If remainder = 1 then
Response.Write "<TD>&nbsp;</TD></TR>" & VbCrLf
End If

%>

</TABLE>

<%
Else

Response.Write "<p>There currently is no data for this project.</p>"

End If
%>
</td>
</tr>
</table>