473,372 Members | 818 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

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 2784
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Steve Goldman | last post by:
Hi, I am trying to come up with a way to develop all n-length permutations of a given list of values. The short function below seems to work, but I can't help thinking there's a better way. ...
2
by: | last post by:
OK: Purpose: Using user's input and 3 recursive functions, construct an hour glass figure. Main can only have user input, loops and function calls. Recursive function 1 takes input and displays...
9
by: Csaba Gabor | last post by:
Inside a function, I'd like to know the call stack. By this I mean that I'd like to know the function that called this one, that one's caller and so on. So I thought to do: <script...
0
by: Michael L | last post by:
Hi Guys(I apologize for the lengty post - Im trying to explain it as best i can) I've been cracking my head on this one for the past 24+ hours and i have tried creating the function in ten...
41
by: Harry | last post by:
Hi all, 1)I need your help to solve a problem. I have a function whose prototype is int reclen(char *) This function has to find the length of the string passed to it.But the conditions...
6
by: RandomElle | last post by:
Hi there I'm hoping someone can help me out with the use of the Eval function. I am using Access2003 under WinXP Pro. I can successfully use the Eval function and get it to call any function with...
9
by: pereges | last post by:
Hello I need some ideas for designing a recursive function for my ray tracing program. The idea behind ray tracing is to follow the electromagnetic rays from the source, as they hit the...
3
dlite922
by: dlite922 | last post by:
I need to get a list of employees out of a database table. I need to end up with an array of ids (primary keys) such as Array(03,9,2,5,1) The employee table has a self reference so that...
3
by: from.future.import | last post by:
Hi, I encountered garbage collection behaviour that I didn't expect when using a recursive function inside another function: the definition of the inner function seems to contain a circular...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.