Recursive function help

**Ray at** · Jul 19 '05, 01:25 PM

Re: Recursive function help

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.e mpid

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" <none@none.co m> wrote in message
news:ukFJKYJdEH A.2752@TK2MSFTN GP12.phx.gbl...[color=blue]
> 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.Createob ject("ADODB.Con nection")
> conn.Provider = "Microsoft.Jet. OLEDB.4.0"
> conn.Open "D:\Application s\Holidays12004 \Includes\holid ays.mdb"
> Getparents()
>
> Function GetParents()
> Set dbRS = Server.CreateOb ject("ADODB.Rec ordset")
> ' 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("empnam e"))
> dbRS.MoveNext
> Loop
> End Function
>
> Function GetReplies(pare ntID)
> Set dbRS = Server.CreateOb ject("ADODB.Rec ordset")
> dbRS.Open "SELECT * FROM empprofile WHERE managername = '" & parentid
> &
> "'", conn
> Do While Not dbRS.EOF
> Response.write "mangername = " & dbRS.Fields("ma nagername") & "
> empname = " & dbRS.Fields("em pname") & "<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("empnam e"))
> dbRS.MoveNext
> Loop
> End Function
> %>
>
>[/color]

**JP SIngh** · Jul 19 '05, 01:25 PM

Re: Recursive function help

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:%23bEfhBLd EHA.2696@TK2MSF TNGP09.phx.gbl. ..[color=blue]
> 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[/color]
a[color=blue]
> left outer join emps b on a.reportsto=b.e mpid
>
> 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" <none@none.co m> wrote in message
> news:ukFJKYJdEH A.2752@TK2MSFTN GP12.phx.gbl...[color=green]
> > 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.Createob ject("ADODB.Con nection")
> > conn.Provider = "Microsoft.Jet. OLEDB.4.0"
> > conn.Open "D:\Application s\Holidays12004 \Includes\holid ays.mdb"
> > Getparents()
> >
> > Function GetParents()
> > Set dbRS = Server.CreateOb ject("ADODB.Rec ordset")
> > ' 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("empnam e"))
> > dbRS.MoveNext
> > Loop
> > End Function
> >
> > Function GetReplies(pare ntID)
> > Set dbRS = Server.CreateOb ject("ADODB.Rec ordset")
> > dbRS.Open "SELECT * FROM empprofile WHERE managername = '" &[/color][/color]
parentid[color=blue][color=green]
> > &
> > "'", conn
> > Do While Not dbRS.EOF
> > Response.write "mangername = " & dbRS.Fields("ma nagername") &[/color][/color]
"[color=blue][color=green]
> > empname = " & dbRS.Fields("em pname") & "<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("empnam e"))
> > dbRS.MoveNext
> > Loop
> > End Function
> > %>
> >
> >[/color]
>
>[/color]

**Chris Hohmann** · Jul 19 '05, 01:25 PM

Re: Recursive function help

"JP SIngh" <none@none.co m> wrote in message
news:%23lfWVULd EHA.3016@tk2msf tngp13.phx.gbl. ..[color=blue]
> 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[/color]

[showtree.asp]
<%
Dim cn, rs, xmldoc, xsldoc
Set cn = CreateObject("A DODB.Connection ")
cn.Open gstrConn '<--Your DSN-Less OLEDB Connection String Here
Set rs = cn.Execute("SEL ECT empname, managername FROM empprofile",1)
Set xmldoc = CreateObject("M SXML2.DOMDocume nt.4.0")
rs.Save xmldoc, 1 'adPersistXML
rs.Close : Set rs = Nothing
cn.Close : Set cn = Nothing
Set xsldoc = CreateObject("M SXML2.DOMDocume nt.4.0")
xsldoc.load Server.MapPath( "rs2tree.xs l")
xmldoc.transfor mNodeToObject xsldoc, Response
Set xsldoc = Nothing
Set xmldoc = Nothing
%>

[rs2tree.xsl]
<?xml version="1.0" encoding="utf-8"?>
<xsl:styleshe et version="1.0"
xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
xmlns:rs='urn:s chemas-microsoft-com:rowset'
xmlns:z='#Rowse tSchema'>
<xsl:output method="html" version="4.0" indent="yes"/>
<xsl:template match="rs:data" >
<html>
<body>
<ul>
<xsl:apply-templates select="z:row[@empname=@manag ername]"/>
</ul>
</body>
</html>
</xsl:template>

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

**JP SIngh** · Jul 19 '05, 01:26 PM

Re: Recursive function help

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" <nospam@thankyo u.com> wrote in message
news:uew6tAPdEH A.3664@TK2MSFTN GP12.phx.gbl...[color=blue]
> "JP SIngh" <none@none.co m> wrote in message
> news:%23lfWVULd EHA.3016@tk2msf tngp13.phx.gbl. ..[color=green]
> > 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[/color][/color]
that.[color=blue][color=green]
> >
> > 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[/color]
>
> [showtree.asp]
> <%
> Dim cn, rs, xmldoc, xsldoc
> Set cn = CreateObject("A DODB.Connection ")
> cn.Open gstrConn '<--Your DSN-Less OLEDB Connection String Here
> Set rs = cn.Execute("SEL ECT empname, managername FROM empprofile",1)
> Set xmldoc = CreateObject("M SXML2.DOMDocume nt.4.0")
> rs.Save xmldoc, 1 'adPersistXML
> rs.Close : Set rs = Nothing
> cn.Close : Set cn = Nothing
> Set xsldoc = CreateObject("M SXML2.DOMDocume nt.4.0")
> xsldoc.load Server.MapPath( "rs2tree.xs l")
> xmldoc.transfor mNodeToObject xsldoc, Response
> Set xsldoc = Nothing
> Set xmldoc = Nothing
> %>
>
> [rs2tree.xsl]
> <?xml version="1.0" encoding="utf-8"?>
> <xsl:styleshe et version="1.0"
> xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
> xmlns:rs='urn:s chemas-microsoft-com:rowset'
> xmlns:z='#Rowse tSchema'>
> <xsl:output method="html" version="4.0" indent="yes"/>
> <xsl:template match="rs:data" >
> <html>
> <body>
> <ul>
> <xsl:apply-templates select="z:row[@empname=@manag ername]"/>
> </ul>
> </body>
> </html>
> </xsl:template>
>
> <xsl:template match="z:row">
> <li>
> <xsl:value-of select="@empnam e"/>
> <xsl:if test="count(../z:row[@managername=cu rrent()/@empname and
> @empname!=curre nt()/@empname])>0">
> <ul>
> <xsl:apply-templates select="../z:row[@managername=cu rrent()/@empname
> and @empname!=curre nt()/@empname]"/>
> </ul>
> </xsl:if>
> </li>
> </xsl:template>
> </xsl:stylesheet>
>
>
>[/color]

**Ray at** · Jul 19 '05, 01:26 PM

Re: Recursive function help

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" <nospam@thankyo u.com> wrote in message
news:uew6tAPdEH A.3664@TK2MSFTN GP12.phx.gbl...
[color=blue]
>
> [showtree.asp]
> <%
> Dim cn, rs, xmldoc, xsldoc
> Set cn = CreateObject("A DODB.Connection ")
> cn.Open gstrConn '<--Your DSN-Less OLEDB Connection String Here
> Set rs = cn.Execute("SEL ECT empname, managername FROM empprofile",1)
> Set xmldoc = CreateObject("M SXML2.DOMDocume nt.4.0")
> rs.Save xmldoc, 1 'adPersistXML
> rs.Close : Set rs = Nothing
> cn.Close : Set cn = Nothing
> Set xsldoc = CreateObject("M SXML2.DOMDocume nt.4.0")
> xsldoc.load Server.MapPath( "rs2tree.xs l")
> xmldoc.transfor mNodeToObject xsldoc, Response
> Set xsldoc = Nothing
> Set xmldoc = Nothing
> %>
>
> [rs2tree.xsl]
> <?xml version="1.0" encoding="utf-8"?>
> <xsl:styleshe et version="1.0"
> xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
> xmlns:rs='urn:s chemas-microsoft-com:rowset'
> xmlns:z='#Rowse tSchema'>
> <xsl:output method="html" version="4.0" indent="yes"/>
> <xsl:template match="rs:data" >
> <html>
> <body>
> <ul>
> <xsl:apply-templates select="z:row[@empname=@manag ername]"/>
> </ul>
> </body>
> </html>
> </xsl:template>
>
> <xsl:template match="z:row">
> <li>
> <xsl:value-of select="@empnam e"/>
> <xsl:if test="count(../z:row[@managername=cu rrent()/@empname and
> @empname!=curre nt()/@empname])>0">
> <ul>
> <xsl:apply-templates select="../z:row[@managername=cu rrent()/@empname
> and @empname!=curre nt()/@empname]"/>
> </ul>
> </xsl:if>
> </li>
> </xsl:template>
> </xsl:stylesheet>
>
>
>[/color]

**Chris Hohmann** · Jul 19 '05, 01:26 PM

Re: Recursive function help

"JP SIngh" <none@none.co m> wrote in message
news:OYvJveVdEH A.216@TK2MSFTNG P11.phx.gbl...[color=blue]
> 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[/color]
using[color=blue]
> xml file. By asking this I am not being lazy simply the fact that I don't
> have required skills for this[/color]

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** · Jul 19 '05, 01:26 PM

Re: Recursive function help

"Ray at <%=sLocation% > [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OF0Z2uWdEH A.4092@TK2MSFTN GP10.phx.gbl...[color=blue]
> 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![/color]

My pleasure.

**J P Singh** · Jul 19 '05, 01:27 PM

Re: Recursive function help

The max depth needed is 5 or less at anytime

Would that make the solution at any time

"Chris Hohmann" <nospam@thankyo u.com> wrote in message
news:OlkxFmZdEH A.3664@TK2MSFTN GP12.phx.gbl...[color=blue]
> "JP SIngh" <none@none.co m> wrote in message
> news:OYvJveVdEH A.216@TK2MSFTNG P11.phx.gbl...[color=green]
> > WOW!!! Chris thanks a lot, works like a charm.
> >
> > Though slight problem. We are planning to use this code in our project.[/color][/color]
I[color=blue][color=green]
> > 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[/color][/color]
in[color=blue][color=green]
> > HTML rather than xml.
> >
> > Would be extremly pleased if you can provide code in pure asp without[/color]
> using[color=green]
> > xml file. By asking this I am not being lazy simply the fact that I[/color][/color]
don't[color=blue][color=green]
> > have required skills for this[/color]
>
> Hmm...I'm not sure tables are well suited to the presentation of[/color]
hierachical[color=blue]
> 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[/color]
that[color=blue]
> would be very simple. Could you describe what the table should look like?[/color]
Or[color=blue]
> even better, provide me with a sample of the desired HTML output.
>
>[/color]

**Chris Hohmann** · Jul 19 '05, 01:27 PM

Re: Recursive function help

"J P Singh" <no@mno.com> wrote in message
news:uNKsazbdEH A.3212@TK2MSFTN GP12.phx.gbl...[color=blue]
> "Chris Hohmann" <nospam@thankyo u.com> wrote in message
> news:OlkxFmZdEH A.3664@TK2MSFTN GP12.phx.gbl...[color=green]
> > "JP SIngh" <none@none.co m> wrote in message
> > news:OYvJveVdEH A.216@TK2MSFTNG P11.phx.gbl...[color=darkred]
> > > WOW!!! Chris thanks a lot, works like a charm.
> > >
> > > Though slight problem. We are planning to use this code in our[/color][/color][/color]
project.[color=blue]
> I[color=green][color=darkred]
> > > 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[/color][/color]
> in[color=green][color=darkred]
> > > HTML rather than xml.
> > >
> > > Would be extremly pleased if you can provide code in pure asp without[/color]
> > using[color=darkred]
> > > xml file. By asking this I am not being lazy simply the fact that I[/color][/color]
> don't[color=green][color=darkred]
> > > have required skills for this[/color]
> >
> > Hmm...I'm not sure tables are well suited to the presentation of[/color]
> hierachical[color=green]
> > 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[/color][/color]
a[color=blue][color=green]
> > table with a single row with a single column containing the tree. If so[/color]
> that[color=green]
> > would be very simple. Could you describe what the table should look[/color][/color]
like?[color=blue]
> Or[color=green]
> > even better, provide me with a sample of the desired HTML output.
> >
> >[/color]
> The max depth needed is 5 or less at anytime
>
> Would that make the solution at any time
>[/color]

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

Recursive function help

Recursive function help

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment