Empty Array, hierachical tree, SQL

[crosmill]

Hi,

I have some code that basically returns an hierachical tree structure from an access db.

It works fine but, when the tree is smaller than the number of levels of code I have it falls over.

Because:
"SELECT * FROM qryStaffList WHERE Manager LIKE '" & arrStaff2(i) & "'"
When there is no value in here it causes an error.

If I can trap the null value before the sql then it shouldn't be a problem, trouble is,
strVar = arrStaff2(i)
also causes an error if there is no value.

I also need to run the code if i = 0, so I can't trap that value.

Anyone got any thoughts?? I'm on day three with this.

Thanks

[allida77]

can you post some more of your code? I am not sure if I see what you are doing.

[crosmill]

Here it is.

Basically it creates a tree structure so that a manager can see details (via a href not added yet) of people under them, but not above them.

It falls over when the hierachy is only two deep, on the last SQL statement.

It's basically the same code four times, separated around about where the <hr> tags occur.


<%Option Explicit%>
<!-- #include file="DatabasePath.asp" -->
<%
Dim strSQL, user, intRScount, intTotalRS, intPreTotal, strConnect, StaffRS, i, x, y, z, w, a, b
Dim arrStaff(), arrStaff1(), arrStaff2(), arrStaff3()

Set StaffRS = Server.CreateObject("ADODB.Recordset")

user = "10208" 'Request.QueryString("user")

intRScount = 1

strSQL = "SELECT * FROM qryStaffList WHERE Manager = " & user
StaffRS.Open strSQL, strConnect, adOpenDynamic, adLockOptimistic, adCmdText

If Not StaffRS.BOF Then

intRScount = 0
StaffRS.MoveLast
StaffRS.MoveFirst
While Not StaffRS.EOF
intRScount = intRScount + 1
StaffRS.MoveNext
Wend

If intTotalRS <> Null Then
intPreTotal = intTotalRS
Else
intPreTotal = 0
End If

intTotalRS = (intTotalRS + intRScount) - 1
Redim arrStaff(intTotalRS)

StaffRS.MoveFirst

For i = intPreTotal to intTotalRS
arrStaff(i) = StaffRS("IndividualID")
StaffRS.MoveNext
Response.Write arrStaff(i) & " Manager = " & user & "<br>"
Next
%><hr><%
StaffRS.Close
Set StaffRS = Nothing
End If

Set StaffRS = Server.CreateObject("ADODB.Recordset")

For x = 0 to intTotalRS

intRScount = 0
intTotalRS = 0
intPreTotal = 0

strSQL = "SELECT * FROM qryStaffList WHERE Manager = " & arrStaff(x)
StaffRS.Open strSQL, strConnect, adOpenDynamic, adLockOptimistic, adCmdText
intRScount = 0

If Not StaffRS.EOF Then

StaffRS.MoveLast
StaffRS.MoveFirst
While Not StaffRS.EOF
intRScount = intRScount + 1
StaffRS.MoveNext
Wend

If intTotalRS <> Null Then
intPreTotal = intTotalRS
Else
intPreTotal = 0
End If

intTotalRS = (intTotalRS + intRScount) - 1
Redim arrStaff1(intTotalRS)

StaffRS.MoveFirst

For y = intPreTotal to intTotalRS
arrStaff1(y) = StaffRS("IndividualID")
StaffRS.MoveNext
Response.Write arrStaff1(y) & " Manager = " & arrStaff(x) & "<br>"
Next

End If
StaffRS.Close

Next
Set StaffRS = Nothing


%><hr><%

Set StaffRS = Server.CreateObject("ADODB.Recordset")

For z = 0 to intTotalRS

intRScount = 0
intTotalRS = 0
intPreTotal = 0


strSQL = "SELECT * FROM qryStaffList WHERE Manager = " & arrStaff1(z)
StaffRS.Open strSQL, strConnect, adOpenDynamic, adLockOptimistic, adCmdText
intRScount = 0

If Not StaffRS.EOF Then

StaffRS.MoveLast
StaffRS.MoveFirst
While Not StaffRS.EOF
intRScount = intRScount + 1
StaffRS.MoveNext
Wend

If intTotalRS <> Null Then
intPreTotal = intTotalRS
Else
intPreTotal = 0
End If

intTotalRS = (intTotalRS + intRScount) - 1
Redim arrStaff2(intTotalRS)

StaffRS.MoveFirst

For w = intPreTotal to intTotalRS
arrStaff2(w) = StaffRS("IndividualID")
StaffRS.MoveNext
Response.Write arrStaff2(w) & " Manager = " & arrStaff1(z) & "<br>"
Next

End If
StaffRS.Close

Next
Set StaffRS = Nothing


%><hr><%


Set StaffRS = Server.CreateObject("ADODB.Recordset")

For a = 0 to intTotalRS

intRScount = 0
intTotalRS = 0
intPreTotal = 0

strSQL = "SELECT * FROM qryStaffList WHERE Manager = " & arrStaff2(a)
StaffRS.Open strSQL, strConnect, adOpenDynamic, adLockOptimistic, adCmdText
intRScount = 0

If Not StaffRS.EOF Then

StaffRS.MoveLast
StaffRS.MoveFirst
While Not StaffRS.EOF
intRScount = intRScount + 1
StaffRS.MoveNext
Wend

If intTotalRS <> Null Then
intPreTotal = intTotalRS
Else
intPreTotal = 0
End If

intTotalRS = (intTotalRS + intRScount) - 1
Redim arrStaff3(intTotalRS)

StaffRS.MoveFirst

For w = intPreTotal to intTotalRS
arrStaff3(b) = StaffRS("IndividualID")
StaffRS.MoveNext
Response.Write arrStaff3(b) & " Manager = " & arrStaff2(a) & "<br>"
Next

End If

StaffRS.Close

Next
Set StaffRS = Nothing

%><hr>

Thanks for reading this far ;)

[allida77]

you can always use

If Err.Number <> 0 Then
there is an error stop code
End if