Recursive Function Problem in ASP - Manohar Kamath

[JP SIngh]

Thanks to Manohar for writing the basic code for displaying the managers and
the employees in a tree like structure.

I have adapted the code below but it gives me an error "exception occcured"
after the first recursion.

Any ideas what can be done to make the following code work.

Thanks



<!--#include file="conn.asp"-->
<!-- #include file="adovbs.in c" -->

<%

strUserId = session("UserId ")
set rs = Server.createob ject ("adodb.records et")
SQL = " SELECT HolidayEntitlem ent, EmpName, ManagerName, EmployeeNumber,
FirstName, LastName, left FROM EMPProfile;"

rs.Open SQL, conn, adOpenKeyset, adLockOptimisti c, adCmdText

Set empRs = Rs.Clone()
' Get the top level manager
Rs.Filter = "ManagerNam e = '" & session("userna me") & "'"

' Loop through this recordset
Do While Not Rs.EOF
Response.Write( Rs("FirstName" ) & " " & Rs("LastName") ) & "<br>"

' Recurse for every person directly under this manager
RecurseEmp Rs("EmpName"), 1

Rs.MoveNext
Loop

' The recursion continues until an employee is not
' a manager.

Sub RecurseEmp (ManagerName, level)
' Filter the employee records for people who work
' for this manager directly

empRs.Filter = "ManagerNam e='" & ManagerName & "'"

Do While Not empRs.EOF

' Print spaces for this level
Response.Write "&nbsp;&nbsp;&n bsp;&nbsp;&nbsp ;&nbsp;&nbsp; "

' Print this person's name
Response.Write( empRs("FirstNam e") & " " & empRs("LastName ")) & "<br>"

' Recurse for this employee -- check if there are employees
' under this person
' THIS IS WHERE I AM HAVING THE PROBLEM
' IF I COMMENT THE LINE BELOW IT WORKS ALL
' OKAY AND DOES DISPLAY THE EMPLOYEES 1
' LEVEL DOWN FROM THE MAIN

RecurseEmp empRs("EmpName" ), Level + 1

empRs.MoveNext
Loop
End Sub
%>

[Manohar Kamath [MVP]]

Re: Recursive Function Problem in ASP - Manohar Kamath

One thing I notice, is that the cursor gets moved around when you recurse --
since there is only one recordset to move around. Here's slightly modified
version, let us know if this works:

Sub RecurseEmp (ManagerName, level)

Dim cursorPosition

' Filter the employee records for people who work
' for this manager directly

empRs.Filter = "ManagerNam e='" & ManagerName & "'"

Do While Not empRs.EOF

' Print spaces for this level
Response.Write "&nbsp;&nbsp;&n bsp;&nbsp;&nbsp ;&nbsp;&nbsp; "

' Print this person's name
Response.Write( empRs("FirstNam e") & " " & empRs("LastName ")) & "<br>"


' Note the cursor position
cursorPosition = empRs.AbsoluteP osition

' Recurse for this employee -- check if there are employees
' under this person
' THIS IS WHERE I AM HAVING THE PROBLEM
' IF I COMMENT THE LINE BELOW IT WORKS ALL
' OKAY AND DOES DISPLAY THE EMPLOYEES 1
' LEVEL DOWN FROM THE MAIN

RecurseEmp empRs("EmpName" ), Level + 1

' Set the cursor back to where you were before recursing
empRs.Filter = "ManagerNam e='" & ManagerName & "'"
empRs.AbsoluteP osition = cursorPosition

empRs.MoveNext
Loop

End Sub


--
Manohar Kamath
Editor, .netWire

https://www.dotnetwire.com

"JP SIngh" <none@none.co m> wrote in message
news:upFE30JGEH A.1156@TK2MSFTN GP12.phx.gbl...[color=blue]
> Thanks to Manohar for writing the basic code for displaying the managers[/color]
and[color=blue]
> the employees in a tree like structure.
>
> I have adapted the code below but it gives me an error "exception[/color]
occcured"[color=blue]
> after the first recursion.
>
> Any ideas what can be done to make the following code work.
>
> Thanks
>
>
>
> <!--#include file="conn.asp"-->
> <!-- #include file="adovbs.in c" -->
>
> <%
>
> strUserId = session("UserId ")
> set rs = Server.createob ject ("adodb.records et")
> SQL = " SELECT HolidayEntitlem ent, EmpName, ManagerName, EmployeeNumber,
> FirstName, LastName, left FROM EMPProfile;"
>
> rs.Open SQL, conn, adOpenKeyset, adLockOptimisti c, adCmdText
>
> Set empRs = Rs.Clone()
> ' Get the top level manager
> Rs.Filter = "ManagerNam e = '" & session("userna me") & "'"
>
> ' Loop through this recordset
> Do While Not Rs.EOF
> Response.Write( Rs("FirstName" ) & " " & Rs("LastName") ) & "<br>"
>
> ' Recurse for every person directly under this manager
> RecurseEmp Rs("EmpName"), 1
>
> Rs.MoveNext
> Loop
>
> ' The recursion continues until an employee is not
> ' a manager.
>
> Sub RecurseEmp (ManagerName, level)
> ' Filter the employee records for people who work
> ' for this manager directly
>
> empRs.Filter = "ManagerNam e='" & ManagerName & "'"
>
> Do While Not empRs.EOF
>
> ' Print spaces for this level
> Response.Write "&nbsp;&nbsp;&n bsp;&nbsp;&nbsp ;&nbsp;&nbsp; "
>
> ' Print this person's name
> Response.Write( empRs("FirstNam e") & " " & empRs("LastName ")) & "<br>"
>
> ' Recurse for this employee -- check if there are employees
> ' under this person
> ' THIS IS WHERE I AM HAVING THE PROBLEM
> ' IF I COMMENT THE LINE BELOW IT WORKS ALL
> ' OKAY AND DOES DISPLAY THE EMPLOYEES 1
> ' LEVEL DOWN FROM THE MAIN
>
> RecurseEmp empRs("EmpName" ), Level + 1
>
> empRs.MoveNext
> Loop
> End Sub
> %>
>
>[/color]

[JP SIngh]

Re: Recursive Function Problem in ASP - Manohar Kamath

Steve Grease
Pater Carter
Debbie Graham

ADODB.Recordset error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested
operation requires a current record.

/teamholidays.as p, line 65

Line 65 is empRs.MoveNext

If I put an if condition around the line

if not empRs.eof then
empRs.MoveNext
end if

Then it does display the above error but only shows one employee for each
manager and goes only one level down

Thanks for your efforts and help, Much appreciated.





"Manohar Kamath [MVP]" <mkamath@TAKETH ISOUTkamath.com > wrote in message
news:%23R9089KG EHA.3908@TK2MSF TNGP12.phx.gbl. ..[color=blue]
> One thing I notice, is that the cursor gets moved around when you[/color]
recurse --[color=blue]
> since there is only one recordset to move around. Here's slightly modified
> version, let us know if this works:
>
> Sub RecurseEmp (ManagerName, level)
>
> Dim cursorPosition
>
> ' Filter the employee records for people who work
> ' for this manager directly
>
> empRs.Filter = "ManagerNam e='" & ManagerName & "'"
>
> Do While Not empRs.EOF
>
> ' Print spaces for this level
> Response.Write "&nbsp;&nbsp;&n bsp;&nbsp;&nbsp ;&nbsp;&nbsp; "
>
> ' Print this person's name
> Response.Write( empRs("FirstNam e") & " " & empRs("LastName ")) & "<br>"
>
>
> ' Note the cursor position
> cursorPosition = empRs.AbsoluteP osition
>
> ' Recurse for this employee -- check if there are employees
> ' under this person
> ' THIS IS WHERE I AM HAVING THE PROBLEM
> ' IF I COMMENT THE LINE BELOW IT WORKS ALL
> ' OKAY AND DOES DISPLAY THE EMPLOYEES 1
> ' LEVEL DOWN FROM THE MAIN
>
> RecurseEmp empRs("EmpName" ), Level + 1
>
> ' Set the cursor back to where you were before recursing
> empRs.Filter = "ManagerNam e='" & ManagerName & "'"
> empRs.AbsoluteP osition = cursorPosition
>
> empRs.MoveNext
> Loop
>
> End Sub
>
>
> --
> Manohar Kamath
> Editor, .netWire
> www.dotnetwire.com
>
>
> "JP SIngh" <none@none.co m> wrote in message
> news:upFE30JGEH A.1156@TK2MSFTN GP12.phx.gbl...[color=green]
> > Thanks to Manohar for writing the basic code for displaying the managers[/color]
> and[color=green]
> > the employees in a tree like structure.
> >
> > I have adapted the code below but it gives me an error "exception[/color]
> occcured"[color=green]
> > after the first recursion.
> >
> > Any ideas what can be done to make the following code work.
> >
> > Thanks
> >
> >
> >
> > <!--#include file="conn.asp"-->
> > <!-- #include file="adovbs.in c" -->
> >
> > <%
> >
> > strUserId = session("UserId ")
> > set rs = Server.createob ject ("adodb.records et")
> > SQL = " SELECT HolidayEntitlem ent, EmpName, ManagerName, EmployeeNumber,
> > FirstName, LastName, left FROM EMPProfile;"
> >
> > rs.Open SQL, conn, adOpenKeyset, adLockOptimisti c, adCmdText
> >
> > Set empRs = Rs.Clone()
> > ' Get the top level manager
> > Rs.Filter = "ManagerNam e = '" & session("userna me") & "'"
> >
> > ' Loop through this recordset
> > Do While Not Rs.EOF
> > Response.Write( Rs("FirstName" ) & " " & Rs("LastName") ) & "<br>"
> >
> > ' Recurse for every person directly under this manager
> > RecurseEmp Rs("EmpName"), 1
> >
> > Rs.MoveNext
> > Loop
> >
> > ' The recursion continues until an employee is not
> > ' a manager.
> >
> > Sub RecurseEmp (ManagerName, level)
> > ' Filter the employee records for people who work
> > ' for this manager directly
> >
> > empRs.Filter = "ManagerNam e='" & ManagerName & "'"
> >
> > Do While Not empRs.EOF
> >
> > ' Print spaces for this level
> > Response.Write "&nbsp;&nbsp;&n bsp;&nbsp;&nbsp ;&nbsp;&nbsp; "
> >
> > ' Print this person's name
> > Response.Write( empRs("FirstNam e") & " " & empRs("LastName ")) & "<br>"
> >
> > ' Recurse for this employee -- check if there are employees
> > ' under this person
> > ' THIS IS WHERE I AM HAVING THE PROBLEM
> > ' IF I COMMENT THE LINE BELOW IT WORKS ALL
> > ' OKAY AND DOES DISPLAY THE EMPLOYEES 1
> > ' LEVEL DOWN FROM THE MAIN
> >
> > RecurseEmp empRs("EmpName" ), Level + 1
> >
> > empRs.MoveNext
> > Loop
> > End Sub
> > %>
> >
> >[/color]
>
>[/color]

[Manohar Kamath [MVP]]

Re: Recursive Function Problem in ASP - Manohar Kamath

Ok, I wrote my own little program... the key is when you recurse, send in
the Value of the field, and not just the reference. Looks like, the
references were "trapped". The following program works perfectly. I changed
it to work with a single recordset, instead of a cloned one.

<html>
<body>
<%
Dim loConn
Dim empRs
Dim Rs
Dim cursorPos

Set loConn = Server.Createob ject("adodb.con nection")
loConn.Open "Provider=sqlol edb;Data source=localhos t;Initial
Catalog=Test1;u id=test;pwd=tes t;"
loConn.CursorLo cation = 3

Set empRs = Server.Createob ject("adodb.rec ordset")

' Retrieve the employee records
Set empRs = loConn.Execute( "select * from Emp")

' Disconnect the recordset
Set empRs.ActiveCon nection = Nothing
loConn.Close
Set loConn = Nothing

' Filter for top-level employees
empRs.Filter = "MgrID = Null"


' Loop through top level employees
Do While Not empRs.EOF
' Mark the position in the recordset
cursorPos = empRs.AbsoluteP osition

' Print the names of top level employees
Response.Write( "<br>" & empRs("EmpName" ))

' Recurse to employees below
' !!!NOTE: The "value" is important!!!
Recurse empRs("EmpID"). value, 1

' Reset the cursor to where we left off
empRs.Filter = "MgrID = Null"
empRs.AbsoluteP osition = cursorPos

empRs.MoveNext
Loop

Set empRs = Nothing


Sub Recurse (empID, Level)
Dim cursorPos

' Filter for the people under this employee
empRs.Filter = "MgrID=" & empID

' Loop through subordinates
Do While Not empRs.EOF
' Mark the position in the recordset
cursorPos = empRs.AbsoluteP osition

' Write out the employee name
Response.Write( "<br>" & String(Level, "-") & empRs("empName" ))

' Recurse to one level below this person
' !!!NOTE: The "value" is important!!!
Recurse empRs("empID"). value, Level + 1

' This is where recursion returns, filter the recordset back
empRs.Filter = "MgrID=" & empID

' Set the cursor position to where it was before we recursed
empRs.AbsoluteP osition = cursorPos

empRs.MoveNext
Loop
End Sub
%>
</body>
</html>

--
Manohar Kamath
Editor, .netWire

https://www.dotnetwire.com

"JP SIngh" <none@none.co m> wrote in message
news:uM%23tIRLG EHA.4044@TK2MSF TNGP10.phx.gbl. ..[color=blue]
> Steve Grease
> Pater Carter
> Debbie Graham
>
> ADODB.Recordset error '800a0bcd'
>
> Either BOF or EOF is True, or the current record has been deleted.[/color]
Requested[color=blue]
> operation requires a current record.
>
> /teamholidays.as p, line 65
>
> Line 65 is empRs.MoveNext
>
> If I put an if condition around the line
>
> if not empRs.eof then
> empRs.MoveNext
> end if
>
> Then it does display the above error but only shows one employee for each
> manager and goes only one level down
>
> Thanks for your efforts and help, Much appreciated.
>
>
>
>
>
> "Manohar Kamath [MVP]" <mkamath@TAKETH ISOUTkamath.com > wrote in message
> news:%23R9089KG EHA.3908@TK2MSF TNGP12.phx.gbl. ..[color=green]
> > One thing I notice, is that the cursor gets moved around when you[/color]
> recurse --[color=green]
> > since there is only one recordset to move around. Here's slightly[/color][/color]
modified[color=blue][color=green]
> > version, let us know if this works:
> >
> > Sub RecurseEmp (ManagerName, level)
> >
> > Dim cursorPosition
> >
> > ' Filter the employee records for people who work
> > ' for this manager directly
> >
> > empRs.Filter = "ManagerNam e='" & ManagerName & "'"
> >
> > Do While Not empRs.EOF
> >
> > ' Print spaces for this level
> > Response.Write "&nbsp;&nbsp;&n bsp;&nbsp;&nbsp ;&nbsp;&nbsp; "
> >
> > ' Print this person's name
> > Response.Write( empRs("FirstNam e") & " " & empRs("LastName ")) & "<br>"
> >
> >
> > ' Note the cursor position
> > cursorPosition = empRs.AbsoluteP osition
> >
> > ' Recurse for this employee -- check if there are employees
> > ' under this person
> > ' THIS IS WHERE I AM HAVING THE PROBLEM
> > ' IF I COMMENT THE LINE BELOW IT WORKS ALL
> > ' OKAY AND DOES DISPLAY THE EMPLOYEES 1
> > ' LEVEL DOWN FROM THE MAIN
> >
> > RecurseEmp empRs("EmpName" ), Level + 1
> >
> > ' Set the cursor back to where you were before recursing
> > empRs.Filter = "ManagerNam e='" & ManagerName & "'"
> > empRs.AbsoluteP osition = cursorPosition
> >
> > empRs.MoveNext
> > Loop
> >
> > End Sub
> >
> >
> > --
> > Manohar Kamath
> > Editor, .netWire
> > www.dotnetwire.com
> >
> >
> > "JP SIngh" <none@none.co m> wrote in message
> > news:upFE30JGEH A.1156@TK2MSFTN GP12.phx.gbl...[color=darkred]
> > > Thanks to Manohar for writing the basic code for displaying the[/color][/color][/color]
managers[color=blue][color=green]
> > and[color=darkred]
> > > the employees in a tree like structure.
> > >
> > > I have adapted the code below but it gives me an error "exception[/color]
> > occcured"[color=darkred]
> > > after the first recursion.
> > >
> > > Any ideas what can be done to make the following code work.
> > >
> > > Thanks
> > >
> > >
> > >
> > > <!--#include file="conn.asp"-->
> > > <!-- #include file="adovbs.in c" -->
> > >
> > > <%
> > >
> > > strUserId = session("UserId ")
> > > set rs = Server.createob ject ("adodb.records et")
> > > SQL = " SELECT HolidayEntitlem ent, EmpName, ManagerName,[/color][/color][/color]
EmployeeNumber,[color=blue][color=green][color=darkred]
> > > FirstName, LastName, left FROM EMPProfile;"
> > >
> > > rs.Open SQL, conn, adOpenKeyset, adLockOptimisti c, adCmdText
> > >
> > > Set empRs = Rs.Clone()
> > > ' Get the top level manager
> > > Rs.Filter = "ManagerNam e = '" & session("userna me") & "'"
> > >
> > > ' Loop through this recordset
> > > Do While Not Rs.EOF
> > > Response.Write( Rs("FirstName" ) & " " & Rs("LastName") ) & "<br>"
> > >
> > > ' Recurse for every person directly under this manager
> > > RecurseEmp Rs("EmpName"), 1
> > >
> > > Rs.MoveNext
> > > Loop
> > >
> > > ' The recursion continues until an employee is not
> > > ' a manager.
> > >
> > > Sub RecurseEmp (ManagerName, level)
> > > ' Filter the employee records for people who work
> > > ' for this manager directly
> > >
> > > empRs.Filter = "ManagerNam e='" & ManagerName & "'"
> > >
> > > Do While Not empRs.EOF
> > >
> > > ' Print spaces for this level
> > > Response.Write "&nbsp;&nbsp;&n bsp;&nbsp;&nbsp ;&nbsp;&nbsp; "
> > >
> > > ' Print this person's name
> > > Response.Write( empRs("FirstNam e") & " " & empRs("LastName ")) & "<br>"
> > >
> > > ' Recurse for this employee -- check if there are employees
> > > ' under this person
> > > ' THIS IS WHERE I AM HAVING THE PROBLEM
> > > ' IF I COMMENT THE LINE BELOW IT WORKS ALL
> > > ' OKAY AND DOES DISPLAY THE EMPLOYEES 1
> > > ' LEVEL DOWN FROM THE MAIN
> > >
> > > RecurseEmp empRs("EmpName" ), Level + 1
> > >
> > > empRs.MoveNext
> > > Loop
> > > End Sub
> > > %>
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]

[Chris Hohmann]

Re: Recursive Function Problem in ASP - Manohar Kamath

"JP SIngh" <none@none.co m> wrote in message
news:upFE30JGEH A.1156@TK2MSFTN GP12.phx.gbl...[color=blue]
> Thanks to Manohar for writing the basic code for displaying the[/color]
managers and[color=blue]
> the employees in a tree like structure.
>
> I have adapted the code below but it gives me an error "exception[/color]
occcured"[color=blue]
> after the first recursion.
>
> Any ideas what can be done to make the following code work.
>
> Thanks[/color]

Have you considered using an XSLT transform? Here's a proof of concept:
[list2tree.xsl]
<?xml version="1.0" encoding="utf-8"?>
<xsl:styleshe et version="1.0"
xmlns:xsl="http ://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" version="4.0" indent="yes"/>
<xsl:template match="root">
<html>
<body>
<ul>
<xsl:apply-templates select="row[@EmployeeNumber =@ManagerNumber]"/>
</ul>
</body>
</html>
</xsl:template>

<xsl:template match="row">
<li>
<xsl:value-of select="concat( @FirstName,' ',@LastName)"/>
<xsl:if test="count(../row[@ManagerNumber= current()/@EmployeeNumber
and @EmployeeNumber !=current()/@EmployeeNumber])>0">
<ul>
<xsl:apply-templates
select="../row[@ManagerNumber= current()/@EmployeeNumber and
@EmployeeNumber !=current()/@EmployeeNumber]"/>
</ul>
</xsl:if>
</li>
</xsl:template>
</xsl:stylesheet>

[showtree.asp]
<%
Dim cn : Set cn = CreateObject("A DODB.Connection ")
Dim cmd : Set cmd = CreateObject("A DODB.Command")

cn.Open "<<Your DSN-Less OLEDB connection string here>>"
Set cmd.ActiveConne ction = cn
cmd.CommandText = "SELECT EmployeeNumber, FirstName, LastName,
ManagerNumber FROM [Profile] FOR XML RAW"
cmd.Properties( "XML Root")= "root"
cmd.Properties( "XSL") = Server.MapPath( "list2tree.xsl" )
cmd.Properties( "Output Stream") = Response

cmd.Execute ,,1025 'adCmdText & adExecuteStream
Set cmd = Nothing
cn.Close : Set cn = Nothing
%>

HTH
-Chris Hohmann