Array questions.

[Sri2003]

I have a rs which I display it in my output page like this.
With objRs
strSQL = "SELECT DealerMainID, DealerID, " & _
"DealerLongNm, DealerCity, DealerState, DealerCountryID, " & _ "DealerCustomerRvwContactEMailAddr, CirusStatusIndicator, " & _ "(SELECT CASE WHEN COUNT(*) > 0 THEN RIGHT(' ' + CAST(ROUND(COUNT(IPContactFullNm) * 1000/COUNT(*), -1)/10 AS VARCHAR(6)), 3) + '%' ELSE '---' END " & _ " FROM dbo.tmpSampleCustomerDealerReview s " & _
" WHERE s.CustDealerID = d.DealerID AND IPTranFlag = 1) AS [IPContactPct], " & _ "(SELECT CASE WHEN COUNT(*) > 0 THEN RIGHT(' ' + CAST(ROUND(COUNT(PSContactFullNm) * 1000/COUNT(*), -1)/10 AS VARCHAR(6)), 3) + '%' ELSE '---' END " & _ " FROM dbo.tmpSampleCustomerDealerReview s " & _
" WHERE s.CustDealerID = d.DealerID AND PSTranFlag = 1) AS [PSContactPct] " & _ " FROM dbo.lkuDealer d " & _
strWhereClause & " AND (ISNUMERIC(SUBSTRING(DealerID, 3, 4)) = 1)" & _ strOrderClause
.Open strSQL, objConn
If Not .EOF Then .MoveFirst
Do While Not .EOF
Response.Write "<TD align=left>=TEXT(" & DQ & Mid(objRs.Fields("DealerMainID"), 3) & DQ & ", " & DQ & "000000" & DQ & ")</TD>"
Response.Write "<TD align=left>=TEXT(" & DQ & Mid(objRs.Fields("DealerID"), 3) & DQ & ", " & DQ & "000000" & DQ & ")</TD>"
Response.Write "<TD align=left>" & objRs.Fields("DealerLongNm") & "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("DealerCity") & "</TD>"
If Request.QueryString("DealerRegion") = "NA" Then
Response.Write "<TD align=left>" & objRs.Fields("DealerState") & "</TD>"
Else
Response.Write "<TD align=left>" & objRs.Fields("DealerCountryID") & "</TD>"
End If
Response.Write "<TD align=left>" & objRs.Fields("DealerCustomerRvwContactEMailAddr") & "</TD>" newCirusStatusIndicator = TRIM(CSTR(objRs.Fields ("CirusStatusIndicator").Value))
If newCirusStatusIndicator= "9" Then
newCirusStatusIndicator = "0"
Response.Write "<TD align=left>" & newCirusStatusIndicator & "</TD>"
Else
Response.Write "<TD align=left>" & objRs.Fields("CirusStatusIndicator") & "</TD>"
End if

status = TRIM(CSTR(objRs.Fields ("CirusStatusIndicator").Value))
Response.Write "<TD align=left>" & Right(" " & objRs.Fields("IPContactPct"), 4) & "</TD>"
Response.Write "<TD align=left>" & Right(" " & objRs.Fields("PSContactPct"), 4) & "</TD>"
Response.Write "</TR>"
Response.Write status
.MoveNext
'Response.Write status
Loop
.Close
End With

Every thing wortks fine so far. I need to take this record alione and display it in one more table - the record is
Response.Write "<TD align=left>" & objRs.Fields("CirusStatusIndicator") & "</TD>"

I tried to assign this as a variable but not able to retrieve all the instances because it is in the loop. I am getting only the last record. How do I pass this recordset as a array to be used in the other table?
Thanks
Sri

[davebat]

outside loop

dim A_STATUS()
redim A_STATUS(RS - 1)


instead of a do loop use this.


for I = 0 to rc - 1

A_STATUS(I) = TRIM(CSTR(objRs.Fields ("CirusStatusIndicator").Value))

next


and just place al your other code inside the above loop, this creates an array that is a big as your cursor. You can display using another for next loop or individually using the array items position

response.write A_STATUS(1)

let me know if you need more help

[Sri2003]

Hi Dave
Thanks a zillion. SInce I am not sitting in front of the Server to try this - I am going to run to my office to try this in an hour - Just that I understodd what you said. I am re arranging the way the diplay will be -
My new copde per your suggewstion will be something like this:
With objRs
strSQL = "SELECT DealerMainID, DealerID, " & _
"DealerLongNm, DealerCity, DealerState, DealerCountryID, " & _
"DealerCustomerRvwContactEMailAddr, CirusStatusIndicator, " & _
"(SELECT CASE WHEN COUNT(*) > 0 THEN RIGHT(' ' + CAST(ROUND(COUNT(IPContactFullNm) * 1000/COUNT(*), -1)/10 AS VARCHAR(6)), 3) + '%' ELSE '---' END " & _
" FROM dbo.tmpSampleCustomerDealerReview s " & _
" WHERE s.CustDealerID = d.DealerID AND IPTranFlag = 1) AS [IPContactPct], " & _
"(SELECT CASE WHEN COUNT(*) > 0 THEN RIGHT(' ' + CAST(ROUND(COUNT(PSContactFullNm) * 1000/COUNT(*), -1)/10 AS VARCHAR(6)), 3) + '%' ELSE '---' END " & _
" FROM dbo.tmpSampleCustomerDealerReview s " & _
" WHERE s.CustDealerID = d.DealerID AND PSTranFlag = 1) AS [PSContactPct] " & _
" FROM dbo.lkuDealer d " & _
strWhereClause & " AND (ISNUMERIC(SUBSTRING(DealerID, 3, 4)) = 1)" & _
strOrderClause
.Open strSQL, objConn
If Not .EOF Then .MoveFirst
Do While Not .EOF
Response.Write "<TD align=left>=TEXT(" & DQ & Mid(objRs.Fields("DealerMainID"), 3) & DQ & ", " & DQ & "000000" & DQ & ")</TD>"
Response.Write "<TD align=left>=TEXT(" & DQ & Mid(objRs.Fields("DealerID"), 3) & DQ & ", " & DQ & "000000" & DQ & ")</TD>"
Response.Write "<TD align=left>" & objRs.Fields("DealerLongNm") & "</TD>"
Response.Write "<TD align=left>" & objRs.Fields("DealerCity") & "</TD>"
If Request.QueryString("DealerRegion") = "NA" Then
Response.Write "<TD align=left>" & objRs.Fields("DealerState") & "</TD>"
Else
Response.Write "<TD align=left>" & objRs.Fields("DealerCountryID") & "</TD>"
End If
Response.Write "<TD align=left>" & objRs.Fields("DealerCustomerRvwContactEMailAddr") & "</TD>"
newCirusStatusIndicator = TRIM(CSTR(objRs.Fields ("CirusStatusIndicator").Value))
If newCirusStatusIndicator= "9" Then
newCirusStatusIndicator = "0"
Response.Write "<TD align=left>" & newCirusStatusIndicator & "</TD>"
Else
Response.Write "<TD align=left>" & objRs.Fields("CirusStatusIndicator") & "</TD>"
End if

Response.Write "<TD align=left>" & Right(" " & objRs.Fields("IPContactPct"), 4) & "</TD>"
Response.Write "<TD align=left>" & Right(" " & objRs.Fields("PSContactPct"), 4) & "</TD>"
Response.Write "</TR>"
Response.Write status
.MoveNext
dim A_STATUS()
redim A_STATUS(RS - 1)


instead of a do loop use this.


for I = 0 to rc - 1

A_STATUS(I) = TRIM(CSTR(objRs.Fields ("CirusStatusIndicator").Value))

next


Loop
'Response.Write "</TABLE>"
Response.Write " <BR> "
Response.Write" <BR> "
Response.Write" <BR> "
'Response.Write" <TABLE BORDER=""1""> "
Response.Write" <TR>"
Response.Write" <TD align=center><B>Status</B></TD>"
Response.Write" <TD align=center><B>Count</B></TD>"
Response.Write" <TD align=center><B>Percent (%)</B></TD></TR>"
Response.Write" <TR><TD align=left>" & status & "</TD></TR>"
Response.Write" <TR><TD align=left>" & objRs.Fields("CirusStatusIndicator") & "</TD>"%>


</TR>
<%Response.Write " </TR> "

.Close
End With
Set objRs = Nothing
Set objConn = Nothing %>

</TABLE>
</BODY>
</HTML>
- My question is that I need to place the oce in the loop after the .movenext of the rs correct?
Thanks a zillion again!

[davebat]

yes sorry, to create the array you will need the movenext, like this:

for I = 0 to rc - 1

A_STATUS(I) = TRIM(CSTR(objRs.Fields ("CirusStatusIndicator").Value))

objRS.movenext

next

you could even create the table in the loop above like this:

<table>

for I = 0 to rc - 1

A_STATUS(I) = TRIM(CSTR(objRs.Fields ("CirusStatusIndicator").Value))
<tr>
<td>
<%response.write A_STATUS(I)%>
</td>
</tr>

objRS.movenext

next


</table>