Home > Archive > ASP > July 2004 > Recursive function help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Recursive function help
|
|
| JP SIngh 2004-07-28, 3:55 pm |
| 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\h
olidays.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
%>
| |
|
| You open dbRS in GetParents() and then you try to use it again in GetReplies(). You need to explicitly define objects like these and other variables so you can be clear about their scope. (eg dim dbRs, conn, ...) You should also have the Option Explicit
declaration at the start of the page.
You could use a single query and return all employees sorted in to levels and use nested do while loops or a recursive function.
Tim M
"JP SIngh" wrote:
> 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\h
olidays.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 2004-07-28, 3:55 pm |
| 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" <none@none.com> wrote in message
news:ukFJKYJdEHA.2752@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\h
olidays.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
> %>
>
>
| |
| JP SIngh 2004-07-28, 3:55 pm |
| 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:%23bEfhBLdEHA.2696@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" <none@none.com> wrote in message
> news:ukFJKYJdEHA.2752@TK2MSFTNGP12.phx.gbl...
parentid[color=darkred]
"[color=darkred]
>
>
| |
| Chris Hohmann 2004-07-28, 8:55 pm |
| "JP SIngh" <none@none.com> wrote in message
news:%23lfWVULdEHA.3016@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>
| |
| JP SIngh 2004-07-29, 8:55 am |
| 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@thankyou.com> wrote in message
news:uew6tAPdEHA.3664@TK2MSFTNGP12.phx.gbl...
> "JP SIngh" <none@none.com> wrote in message
> news:%23lfWVULdEHA.3016@tk2msftngp13.phx.gbl...
that.[color=darkred]
>
> [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>
>
>
>
| |
| Ray at 2004-07-29, 3:55 pm |
| 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@thankyou.com> wrote in message
news:uew6tAPdEHA.3664@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>
>
>
>
| |
| Chris Hohmann 2004-07-29, 3:55 pm |
| "JP SIngh" <none@none.com> wrote in message
news:OYvJveVdEHA.216@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.
| |
| Chris Hohmann 2004-07-29, 3:55 pm |
| "Ray at <%=sLocation%> [MVP]" <myfirstname at lane34 dot com> wrote in
message news:OF0Z2uWdEHA.4092@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.
| |
| J P Singh 2004-07-29, 8:55 pm |
| The max depth needed is 5 or less at anytime
Would that make the solution at any time
"Chris Hohmann" <nospam@thankyou.com> wrote in message
news:OlkxFmZdEHA.3664@TK2MSFTNGP12.phx.gbl...
> "JP SIngh" <none@none.com> wrote in message
> news:OYvJveVdEHA.216@TK2MSFTNGP11.phx.gbl...
I[color=darkred]
in[color=darkred]
> using
don't[color=darkred]
>
> 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 2004-07-30, 8:55 pm |
| "J P Singh" <no@mno.com> wrote in message
news:uNKsazbdEHA.3212@TK2MSFTNGP12.phx.gbl...
> "Chris Hohmann" <nospam@thankyou.com> wrote in message
> news:OlkxFmZdEHA.3664@TK2MSFTNGP12.phx.gbl...
project.[color=darkred]
> I
> in
> don't
> hierachical
a[color=darkred]
> that
like?[color=darkred]
> Or
> 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?
|
|
|
|
|