simple retrieving value using array

[Fary4u]

attach file contains database table,

retrieving value using multiple array, no problem retrieving 1st values but when i'm trying to retrieving 2nd array value giving errer.

here is my coding

<%
s1 = "S"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString
SQL2 = "SELECT stock, color_id FROM PRODUCT_COLOURS WHERE prod_id = '" & sear & "' and sizes = '" & s1 & "'" 
set rsSizes = Server.CreateObject("ADODB.RecordSet")
set rsSizes = Conn.Execute(SQL2)
countS = 0
dim strsize()
while not rsSizes.EOF
if countS = 0 then
    redim strsize(0)
    strsize(countS) = rsSizes("stock")
    strsize(countS) = rsSizes("color_id")
else
    redim preserve strsize(countS)
    strsize(countS) = rsSizes("stock")
    strsize(countS) = rsSizes("color_id")
end if
rsSizes.MoveNext
countS = countS + 1
Wend
ts = ubound(strsize)
%>    
<tr>
<td class="style5"><b>Small :</b></td>
<td><input type="text" name="black_small" value="<%
        for Si = 0 to ts
        If strsize(Si) = "Black" then
            response.write strsize(Si)
        End if
        next
        %>"/>
        </td>
<td><input type="text" name="blue_small" value="<%
        for Si = 0 to ts
        If strsize(Si) = "Blue" then
            response.write strsize(Si)
        End if
        next
        %>"/></td>
<td><input type="text" name="green_small" value="<%
        for Si = 0 to ts
        If strsize(Si) = "Green" then
            response.write strsize(Si)
        End if
        next
        %>"/></td>
</td></tr></table>
 

[Fary4u]

latest changes word fine again but value not cross ponding.

s1 = "S"
Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open ConString
' SELECT DISTINCT color_id FROM PRODUCT_COLOURS WHERE prod_id = '" & sear & "'"
SQL2 = "SELECT stock FROM PRODUCT_COLOURS WHERE prod_id = '" & sear & "' and sizes = '" & s1 & "'"
SQL3 = "SELECT color_id FROM PRODUCT_COLOURS WHERE prod_id = '" & sear & "' and sizes = '" & s1 & "'"
set rsSizes = Server.CreateObject("ADODB.RecordSet")
set rsSizes = Conn.Execute(SQL2)
set rsSizes3 = Server.CreateObject("ADODB.RecordSet")
set rsSizes3 = Conn.Execute(SQL3)
countS = 0
countT = 0
dim strsize()
dim strsizeT()
 
while not rsSizes.EOF
if countS = 0 then
    redim strsize(0)
    strsize(countS) = rsSizes("stock")
else
    redim preserve strsize(countS)
    strsize(countS) = rsSizes("stock")
end if
rsSizes.MoveNext
countS = countS + 1
Wend
 
while not rsSizes3.EOF
if countT = 0 then
    redim strsizeT(0)
    strsizeT(countT) = rsSizes3("color_id")
else
    redim preserve strsizeT(countT)
    strsizeT(countT) = rsSizes3("color_id")
end if
rsSizes3.MoveNext
countT = countT + 1
Wend
 
ts = ubound(strsize)
tT = ubound(strsizeT)
%>    
    <tr>
        <td class="style5" style="text-align: right"><b>Small :</b></td>
        <td class="style5"><input type="text" name="black_small" size="3" value="<%
        for Si = 0 to tT
        If strsizeT(Si) = "Black" then
        response.write strsize(Si)
            for T = 0 to ts
                response.write strsize(ts)
            next
        End if
        next
        %>"/>
        </td>
        <td class="style5"><input type="text" name="blue_small" size="3" value="<%
        for Si = 0 to tT
            for T = 0 to ts
        If strsizeT(Si) = "Blue" then
        response.write strsize(Si)
                response.write strsize(ts)
        End if
            next
        next
        %>"/></td>
        <td class="style5"><input type="text" name="green_small" size="3" value="<%
        for Si = 0 to tT
        If strsizeT(Si) = "Green" then
        response.write strsize(Si)
            for T = 0 to ts
                response.write strsize(ts)
            next
        End if
        next
        %>"/>
 

[Fary4u]

any possibility to work out ?

[Nicodemas]

Will this help? My example uses a FOR loop after converting your Record Set into a 2d array (for better performance). My example might not order the results as you prefer, so I suggest that if you cannot use my suggestion as is, that you augment your SQL to perform an ORDER BY so as to order it explicitly how you want.

<%
dim s1, Conn, SQL2, rsSizes, countS, arrSizes
 
s1 = "S" 
Set Conn = Server.CreateObject("ADODB.Connection") 
Conn.Open ConString 
SQL2 = "SELECT stock, color_id FROM PRODUCT_COLOURS WHERE prod_id = '" & sear & "' and sizes = '" & s1 & "'"  
set rsSizes = Server.CreateObject("ADODB.RecordSet") 
set rsSizes = Conn.Execute(SQL2) 
countS = 0 
 
'convert the recordset to a 2d array for lower system overhead
if not rsSizes.EOF then
   arrSizes = rsSizes.getrows
   set rsSizes = nothing%>
 
   <tr> 
      <td class="style5"><b>Small :</b></td><%
      'use a FOR loop to cycle through the values returned by the query
      for countS = 0 to ubound(arrSizes, 2)%>     
 
         intStock = arrSizes(0, countS)
         strColor = arrSizes(1, countS)
 
         response.write "<td>" & strColor & ": "
         response.write "<input type=""text"" name="""& lcase(strColor) &"_small"" value="""& intStock &""" />"
         response.write "</td>"  
      next%>
   </tr><%
end if
 
Conn.close
set Conn = nothing
%>