Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    New to the CF scene
    Join Date
    Jul 2002
    Location
    Leeds, UK
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Empty Array, hierachical tree, SQL

    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

  • #2
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    can you post some more of your code? I am not sure if I see what you are doing.

  • #3
    New to the CF scene
    Join Date
    Jul 2002
    Location
    Leeds, UK
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  • #4
    Regular Coder
    Join Date
    Jun 2002
    Location
    Cincinnati, OH
    Posts
    545
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you can always use

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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •