Recursive function help

[JP SIngh]

Hi All

I am trying to write a recursive function to list the managers and his
employees in a tree like sctructure

Manager 1
Emp1
Emp1.1
Emp 1.2
Emp 2
Emp 3
Emp 3.1
Emp 3.2

Have written the following code but don't seems to be getting anywhere

It gives me this error

Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'dbRS.Open'
/admin/man.asp, line 20
Someone please help

Regards

Jas

<%
set conn = Server.Createobject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "D:\Applications\Holidays12004\Includes\holidays.mdb"
Getparents()

Function GetParents()
Set dbRS = Server.CreateObject("ADODB.Recordset")
' ADO code to load all the posts with a null parent
dbRS.Open "SELECT * FROM empprofile order by managername", conn
Do While Not dbRS.EOF
GetReplies(dbRS.Fields("empname"))
dbRS.MoveNext
Loop
End Function

Function GetReplies(parentID)
Set dbRS = Server.CreateObject("ADODB.Recordset")
dbRS.Open "SELECT * FROM empprofile WHERE managername = '" & parentid &
"'", conn
Do While Not dbRS.EOF
Response.write "mangername = " & dbRS.Fields("managername") & "
empname = " & dbRS.Fields("empname") & "<br>"
' <-- Your recursive call. Walks down each tree branch until
' it hits the last reply in a tree (dbRS returns no rows), then backs
out.
GetReplies(dbRS.Fields("empname"))
dbRS.MoveNext
Loop
End Function
%>

 

[Ray at]

Not that this is an answer to your question, but are you trying to just
create a list of employees and who they report to or something along those
lines? You don't need to execute multiple queries for this. You could do
something like this:



Table: Emps:
EmpID EmpName ReportsTo
1 Ray 2
2 Karol 3
3 Dean 0
4 Gary 3
5 Robert 4
6 Tina 4
7 Chuck 2
8 Bradley 2


Query:
select a.empid as [ID], a.empname as [Emp], b.empname as [Boss] from emps a
left outer join emps b on a.reportsto=b.empid

Results:
ID Emp Boss
-- ------ ------
1 Ray Karol
2 Karol Dean
3 Dean
4 Gary Dean
5 Robert Gary
6 Tina Gary
7 Chuck Karol
8 Brian Karol

Ray at work

 

[JP SIngh]

Hi Ray

You are correct I am looking to create a tree like structure for all
employees who report to thier manager.

I appreciate any help with a bit of code which can do something like that.

I want the tree to look like this

Dean
Karol
Ray
Chuck
Bradley
Gary
Robert
Tina

Can you help. We have been trying to do this for ages but no luck

thanks a lot

Not that this is an answer to your question, but are you trying to just
create a list of employees and who they report to or something along those
lines? You don't need to execute multiple queries for this. You could do
something like this:



Table: Emps:
EmpID EmpName ReportsTo
1 Ray 2
2 Karol 3
3 Dean 0
4 Gary 3
5 Robert 4
6 Tina 4
7 Chuck 2
8 Bradley 2


Query:
select a.empid as [ID], a.empname as [Emp], b.empname as [Boss] from emps a
left outer join emps b on a.reportsto=b.empid

Results:
ID Emp Boss
-- ------ ------
1 Ray Karol
2 Karol Dean
3 Dean
4 Gary Dean
5 Robert Gary
6 Tina Gary
7 Chuck Karol
8 Brian Karol

Ray at work

Hi All

I am trying to write a recursive function to list the managers and his
employees in a tree like sctructure

Manager 1
Emp1
Emp1.1
Emp 1.2
Emp 2
Emp 3
Emp 3.1
Emp 3.2

Have written the following code but don't seems to be getting anywhere

It gives me this error

Microsoft VBScript runtime error '800a01fb'
An exception occurred: 'dbRS.Open'
/admin/man.asp, line 20
Someone please help

Regards

Jas

<%
set conn = Server.Createobject("ADODB.Connection")
conn.Provider = "Microsoft.Jet.OLEDB.4.0"
conn.Open "D:\Applications\Holidays12004\Includes\holidays.mdb"
Getparents()

Function GetParents()
Set dbRS = Server.CreateObject("ADODB.Recordset")
' ADO code to load all the posts with a null parent
dbRS.Open "SELECT * FROM empprofile order by managername", conn
Do While Not dbRS.EOF
GetReplies(dbRS.Fields("empname"))
dbRS.MoveNext
Loop
End Function

Function GetReplies(parentID)
Set dbRS = Server.CreateObject("ADODB.Recordset")
dbRS.Open "SELECT * FROM empprofile WHERE managername = '" & parentid
&
"'", conn
Do While Not dbRS.EOF
Response.write "mangername = " & dbRS.Fields("managername") & "
empname = " & dbRS.Fields("empname") & "<br>"
' <-- Your recursive call. Walks down each tree branch until
' it hits the last reply in a tree (dbRS returns no rows), then backs
out.
GetReplies(dbRS.Fields("empname"))
dbRS.MoveNext
Loop
End Function
%>

 

[Chris Hohmann]

Hi Ray

You are correct I am looking to create a tree like structure for all
employees who report to thier manager.

I appreciate any help with a bit of code which can do something like that.

I want the tree to look like this

Dean
Karol
Ray
Chuck
Bradley
Gary
Robert
Tina

Can you help. We have been trying to do this for ages but no luck

[showtree.asp]
<%
Dim cn, rs, xmldoc, xsldoc
Set cn = CreateObject("ADODB.Connection")
cn.Open gstrConn '<--Your DSN-Less OLEDB Connection String Here
Set rs = cn.Execute("SELECT empname, managername FROM empprofile",1)
Set xmldoc = CreateObject("MSXML2.DOMDocument.4.0")
rs.Save xmldoc, 1 'adPersistXML
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Set xsldoc = CreateObject("MSXML2.DOMDocument.4.0")
xsldoc.load Server.MapPath("rs2tree.xsl")
xmldoc.transformNodeToObject xsldoc, Response
Set xsldoc = Nothing
Set xmldoc = Nothing
%>

[rs2tree.xsl]
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<xslutput method="html" version="4.0" indent="yes"/>
<xsl:template match="rs:data">
<html>
<body>
<ul>
<xsl:apply-templates select="z:row[@empname=@managername]"/>
</ul>
</body>
</html>
</xsl:template>

<xsl:template match="z:row">
<li>
<xsl:value-of select="@empname"/>
<xsl:if test="count(../z:row[@managername=current()/@empname and
@empname!=current()/@empname])>0">
<ul>
<xsl:apply-templates select="../z:row[@managername=current()/@empname
and @empname!=current()/@empname]"/>
</ul>
</xsl:if>
</li>
</xsl:template>
</xsl:stylesheet>

 

[JP SIngh]

WOW!!! Chris thanks a lot, works like a charm.

Though slight problem. We are planning to use this code in our project. I
have no experience in XML hence have not been able to adapt the code.

How difficult it is to convert the code to write the values in a table in
HTML rather than xml.

Would be extremly pleased if you can provide code in pure asp without using
xml file. By asking this I am not being lazy simply the fact that I don't
have required skills for this

thanks

Hi Ray

You are correct I am looking to create a tree like structure for all
employees who report to thier manager.

I appreciate any help with a bit of code which can do something like that.

I want the tree to look like this

Dean
Karol
Ray
Chuck
Bradley
Gary
Robert
Tina

Can you help. We have been trying to do this for ages but no luck

[showtree.asp]
<%
Dim cn, rs, xmldoc, xsldoc
Set cn = CreateObject("ADODB.Connection")
cn.Open gstrConn '<--Your DSN-Less OLEDB Connection String Here
Set rs = cn.Execute("SELECT empname, managername FROM empprofile",1)
Set xmldoc = CreateObject("MSXML2.DOMDocument.4.0")
rs.Save xmldoc, 1 'adPersistXML
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Set xsldoc = CreateObject("MSXML2.DOMDocument.4.0")
xsldoc.load Server.MapPath("rs2tree.xsl")
xmldoc.transformNodeToObject xsldoc, Response
Set xsldoc = Nothing
Set xmldoc = Nothing
%>

[rs2tree.xsl]
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<xslutput method="html" version="4.0" indent="yes"/>
<xsl:template match="rs:data">
<html>
<body>
<ul>
<xsl:apply-templates select="z:row[@empname=@managername]"/>
</ul>
</body>
</html>
</xsl:template>

<xsl:template match="z:row">
<li>
<xsl:value-of select="@empname"/>
<xsl:if test="count(../z:row[@managername=current()/@empname and
@empname!=current()/@empname])>0">
<ul>
<xsl:apply-templates select="../z:row[@managername=current()/@empname
and @empname!=current()/@empname]"/>
</ul>
</xsl:if>
</li>
</xsl:template>
</xsl:stylesheet>

 

[Ray at]

I've just finally started reading XML books figuring I should know a bit
about XML, XSLT, XPath, etc. and not until this post was I able to see
something useful in action. :] Thanks Chris!

Ray at work

 

[Chris Hohmann]

WOW!!! Chris thanks a lot, works like a charm.

Though slight problem. We are planning to use this code in our project. I
have no experience in XML hence have not been able to adapt the code.

How difficult it is to convert the code to write the values in a table in
HTML rather than xml.

Would be extremly pleased if you can provide code in pure asp without using
xml file. By asking this I am not being lazy simply the fact that I don't
have required skills for this

Hmm...I'm not sure tables are well suited to the presentation of hierachical
data. As the tree could have any number of levels, there's no way to
determine how many columns the table should have to accommodate the tree
without doing a depth first walk of the tree. Are you just talking about a
table with a single row with a single column containing the tree. If so that
would be very simple. Could you describe what the table should look like? Or
even better, provide me with a sample of the desired HTML output.

 

[Chris Hohmann]

I've just finally started reading XML books figuring I should know a bit
about XML, XSLT, XPath, etc. and not until this post was I able to see
something useful in action. :] Thanks Chris!

My pleasure.

 

[J P Singh]

The max depth needed is 5 or less at anytime

Would that make the solution at any time

 

[Chris Hohmann]

project. like?
The max depth needed is 5 or less at anytime

Would that make the solution at any time

If you know what the max depth is why not just self join the empprofile
table to itself 5 times?