Can we use array outside if the if rs.eof block?

[ReportingAnalyst]

Hi,

Is it not possible to use an array which I populated with the recordset outside of the if statement?

If not rs2.EOF then

			'This line sees if there is value of 1 for the query above
			sub_Value = rs2("Count_Sub")
	       	If sub_Value = 1 Then
				'output a link
				returnValue = True
				Response.Write returnValue
			Else
				'do not output a link
				returnValue = False
				Response.Write returnValue
			End If

			aSubordinates = rs2.getrows()
			rs2.close
			set rs2 = nothing

		End If

If I were to use this statement, after the above code,

For iRowLoop = 0 to UBound(aSubordinates, 2)

It errs:

Error Type:
Microsoft VBScript runtime (0x800A000D)
Type mismatch: 'UBound'

So obviously it is not liking the array being used. But I thought that once we populate an array, we can call the array anytime and display the data.

Thanks.

 

[Sheco]

Is any of this inside a function or sub?

 

[ReportingAnalyst]

Yes it is inside a Function.

 

[Sheco]

If it is declared inside the function then you can only use it inside the function, if it is outside it is global.

 

[ReportingAnalyst]

But then the 2nd line is also within the function.

See this:

Function GetSubordinates(managerID, Counter)

        sub_Emp_Source = "SELECT AMP_AllMySubordinates_Postings.Member_Manager, " & _
                         "AMP_AllMySubordinates_Postings.Member_Subordinate, " & _
                         "AMP_AllMySubordinates_Postings.Owner_DateOfPost, " & _
                         "Employee.EmpID, " & _
                         "Employee.FirstName + ' ' + dbo.Employee.LastName AS Emp_Name, " & _
                         "Employee.LastName " & _
                         "FROM AMP_AllMySubordinates_Postings INNER JOIN " & _
                         "Employee ON " & _
                         "AMP_AllMySubordinates_Postings.Member_Subordinate = dbo.Employee.UniqueIdentifier " & _
                         "WHERE  (AMP_AllMySubordinates_Postings.Member_Manager = " & managerID & ") " & _
                         "AND " & _
                         "AMP_AllMySubordinates_Postings.Owner_Payee = " & strUI & " " & _
                         "AND " & _
                         "AMP_AllMySubordinates_Postings.Owner_DateOfPost = " & _
                         "(Select MAX(AMP_AllMySubordinates_Postings.Owner_DateOfPost) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = " & strUI & ") " & _
                         "ORDER BY Employee.LastName; "

		sub_Emp_Source = "SELECT Member_Manager, Member_Subordinate, " & _
		                 "Owner_DateOfPost, Employee.EmpID, " & _
		                 "Employee.FirstName + ' ' + dbo.Employee.LastName AS Emp_Name, " & _
		                 "Count(Member_Subordinate) AS Count_Sub " & _
		                 "FROM AMP_AllMySubordinates_Postings INNER JOIN " & _
		                 "Employee ON " & _
		                 "AMP_AllMySubordinates_Postings.Member_Subordinate = dbo.Employee.UniqueIdentifier " & _
		                 "WHERE  (Member_Manager = " & managerID & ") " & _
		                 "AND " & _
		                 "Owner_Payee = " & strUI & " " & _
		                 "AND " & _
		                 "Owner_DateOfPost = " & _
		                 "(Select MAX(Owner_DateOfPost) FROM Amp_AllMySubordinates_Postings WHERE owner_payee = " & strUI & ") " & _
		                 "group by Member_Manager, Member_Subordinate, Owner_DateOfPost, EmpID, (Employee.FirstName + ' ' + Employee.LastName) " & _
		                 "Order by Employee.FirstName + ' ' + Employee.LastName;"

        Dim rs2
        set rs2 = Server.CreateObject("ADODB.Recordset")
        rs2.open sub_Emp_Source, Conn

        Dim aSubordinates

        If not rs2.EOF then

			'This line sees if there is value of 1 for the query above
			sub_Value = rs2("Count_Sub")
	       	If sub_Value = 1 Then
				'output a link
				returnValue = True
				Response.Write returnValue
			Else
				'do not output a link
				returnValue = False
				Response.Write returnValue
			End If

			aSubordinates = rs2.getrows()
			rs2.close
			set rs2 = nothing

		End If


            'Declare Constants for the above SQL columns for better readability
            'Use these Constants instead of referring to the array numeric indexes
            Const c_Subordinate = 1
            Const c_EmpID = 3
            Const c_EmpName = 4

            'Ubound(MyArray,1) 'Returns the Number of Columns
            'Ubound(MyArray,2) 'Returns the Number of Rows

            Dim iRowLoop

            For iRowLoop = 0 to UBound(aSubordinates, 2)

                Dim uniqueID
                'Changed this from managerid to subordinate id because we want to see the subordinates for the member_subordinate
                uniqueID  = aSubordinates(c_Subordinate, iRowLoop)

                'If returnValue = True then
                    %>
                        <li>
                            <input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>">
                            <a href="#" class="a_style" onclick="s_Hide('<%=aSubordinates(c_Subordinate, iRowLoop)%>'); return false;"><%=aSubordinates(c_EmpName,iRowLoop)%></a><font size=1>&nbsp;(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
                            <ul id="UI_<%=aSubordinates(c_Subordinate, iRowLoop)%>" style="display: none">
                            <%
                            Counter = Counter + 1
                            Call GetSubordinates(uniqueID, Counter)
                            Counter = Counter - 1
                            %>
                            </ul>
                        </li>
                            <%
               ' Else
                            %>
						<li>
							<input type="radio" name="empid" id="empid<%=aSubordinates(c_EmpID,iRowLoop)%>" value="<%=aSubordinates(c_EmpID,iRowLoop)%>">
							<%=aSubordinates(c_EmpName,iRowLoop)%>&nbsp;<font size=1>(<%=aSubordinates(c_EmpID,iRowLoop)%>)</font>
            			</li>
                            <%
                'End if 'returnvalue = true or false
            Next 'iRowLoop
            %>
            <%
        'End If 'rs2.EOF
End Function

 

[Sheco]

In that function above, the variable aSubordinates won't get set to the recordset array if rs2.EOF = True

Also... I'd want to verify that the second parameter to UBound() is going to look at the 2 dimensional array as 1 and 2. It might be assuming 0 and 1 instead of 1 and 2.