469,133 Members | 998 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

Recursive function help

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.m db"
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
%>
Jul 19 '05 #1
9 2506
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" <no**@none.com> wrote in message
news:uk**************@TK2MSFTNGP12.phx.gbl...
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.m db"
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
%>

Jul 19 '05 #2
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
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:%2****************@TK2MSFTNGP09.phx.gbl...
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" <no**@none.com> wrote in message
news:uk**************@TK2MSFTNGP12.phx.gbl...
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.m db"
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
%>


Jul 19 '05 #3
"JP SIngh" <no**@none.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
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'>
<xsl:output 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>

Jul 19 '05 #4
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

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:ue**************@TK2MSFTNGP12.phx.gbl...
"JP SIngh" <no**@none.com> wrote in message
news:%2****************@tk2msftngp13.phx.gbl...
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'>
<xsl:output 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>

Jul 19 '05 #5
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" <no****@thankyou.com> wrote in message
news:ue**************@TK2MSFTNGP12.phx.gbl...

[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'>
<xsl:output 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>

Jul 19 '05 #6
"JP SIngh" <no**@none.com> wrote in message
news:OY*************@TK2MSFTNGP11.phx.gbl...
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.
Jul 19 '05 #7
"Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OF**************@TK2MSFTNGP10.phx.gbl...
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.
Jul 19 '05 #8
The max depth needed is 5 or less at anytime

Would that make the solution at any time

"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Ol**************@TK2MSFTNGP12.phx.gbl...
"JP SIngh" <no**@none.com> wrote in message
news:OY*************@TK2MSFTNGP11.phx.gbl...
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.

Jul 19 '05 #9
"J P Singh" <no@mno.com> wrote in message
news:uN**************@TK2MSFTNGP12.phx.gbl...
"Chris Hohmann" <no****@thankyou.com> wrote in message
news:Ol**************@TK2MSFTNGP12.phx.gbl...
"JP SIngh" <no**@none.com> wrote in message
news:OY*************@TK2MSFTNGP11.phx.gbl...
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.

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?
Jul 19 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Steve Goldman | last post: by
2 posts views Thread by | last post: by
9 posts views Thread by Csaba Gabor | last post: by
reply views Thread by Michael L | last post: by
41 posts views Thread by Harry | last post: by
9 posts views Thread by pereges | last post: by
3 posts views Thread by from.future.import | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.