Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 Need a recursive ASP function (somewhat easy?)

Author  Topic 

MarkGG
Yak Posting Veteran

53 Posts
Posted - 2005-09-08 : 08:53:22
Hi, I need to make an ASP fucntion to recursively call all of the elements out of the database and just simply format them so thy output my tree structure. The code looks like this:

<head>
<title>T&W Organizational Chart</title>
<script src="w3f.js"></script>

<style>
@import "w3f.css";
.box, .boxHover, .boxChecked, .boxCheckedHover {
  padding:0.5em 1em;
  margin:0em;
  cursor:default;
  border:1px solid #000040;
  background:#ABCDEF;
  color:#000040;
}
.boxHover {
  background:white;
}
.boxCheckedHover {
  border-color: #4040A0;
  color: #4040A0;
  background:white;
}
.boxChecked {
  background: #F8F8FC; 
  border-color: #4040A0;
  color: #4040A0;
}
.boxc {
  padding:0em 0.3em;
  text-align:center;
}
.boxc TABLE {
  margin:auto;
}
.lineright {
  border-right:1px solid #000040;
  font-size:50%;
}
.leftabove {
  border-right:1px solid #000040;
  border-top:1px solid #000040;
  font-size:50%;
}
.rightabove {
  border-top:1px solid #000040;
  font-size:50%;
}
.nolines {
  font-size:50%;
}
TD#chart {
  padding:1em;
}
DIV.message {
  color:#4040A0;
  font-weight:bold;
  background-color:#F8F8FC;
  border:1px solid #4040A0;
  margin:1em 0em;
  padding:0em 0.5em;
}
DIV.somespace {
  padding:0.2em;
  text-align:center;
}
DIV.somespace TABLE {
  margin:auto;
}
</style>

<%
ON ERROR RESUME NEXT
Dim con,sql,constring, rs, nodeParent
Set con = Server.CreateObject("ADODB.Connection")
%>

<!-- #include file="dbConnect.ssi" -->

<%
set objRS=Server.CreateObject("ADODB.recordset")
'I open my connection
con.Open dbConnect
%>

<script>
var nr=1;
var nodeBag={};
var sel=0;var attach_sel=0;
var selnode;

function Node(data,childNodes) {
  this.data=data;
  this.id=nr++;
  nodeBag[this.id]=this;
  if (childNodes) {
    this.first=childNodes[0];
    for (var i=0;i<childNodes.length;i++) {
      childNodes[i].prev=childNodes[i-1];
      childNodes[i].next=childNodes[i+1];
      childNodes[i].parent=this;
    }
  }
}
Node.prototype={
render:function(finalver) {
  var s='';var next='';var l=1;
  if (this.first) {
    l=0;var node=this.first;
    while (node) {
      next+='<TD valign="top">'+node.render(finalver)+'</td>';
      node=node.next;l++;
    }
  }
  s+='\n<table cellspacing="0" cellpadding="0" width="100%"><tr><td colspan='+l+'>';
  s+='\n<table cellspacing="0" cellpadding="0" width="100%">';
  if (this.parent) {
    s+='\n<tr><td width="50%" class="'+(!this.prev?'lineright':'leftabove')+'"> </td>';
    s+='<td width="50%" class="'+(!this.next?'nolines':'rightabove')+'"> </td></tr>';
  }
  s+='\n<tr><td colspan="2" class="boxc">\n<table cellspacing="0" cellpadding="0">';
  s+='\n<tr><td class="'+(sel==this.id&&!finalver?'boxChecked':'box')+'"'
  if (!finalver) s+=' onclick="unhover(this);choose('+this.id+');hover(this);" id="box'+this.id+'" onmouseover="hover(this)" onmouseout="unhover(this)"';
  s+='"><nobr>';
  if (finalver&&this.data.email) s+='<a href="mailto:'+this.data.email+'">';
  s+=(this.data.name||'(no name)');
  if (finalver&&this.data.email) s+='</a>';
  s+='</nobr></td></tr></table>\n</td></tr>';
  if (this.first) s+='\n<tr><td width="50%" class="lineright"> </td><td width="50%"></td></tr>';
  s+='</table></td></tr><tr>'+next+'</tr></table>';
  return s;
},
getCode:function(indent) {
  indent=indent||'';
  var s='';
  for (var p in this.data) s+=","+p+":'"+this.data[p]+"'";
  var s="new Node({"+s.substr(1)+"}";  
  if (this.first) {
    var a='';
    var node=this.first;
    while (node) {
      a+=',\n  '+indent+node.getCode(indent+'  ');
      node=node.next;
    }
    s+=",["+a.substr(1)+"\n"+indent+"]";
  }
  s+=")";
  return s;
},
detach:function() {
  if (!this.parent) return;
  if (this.prev) this.prev.next=this.next; else this.parent.first=this.next;
  if (this.next) this.next.prev=this.prev;
  delete this.parent;
  delete this.next;
  delete this.prev;
},
moveLeft:function() {
  if (!this.prev) return;
  var n0=this.prev.prev;
  var n2=this.prev;
  var n1=this;
  var n3=this.next;
  if (n0) n0.next=n1; else this.parent.first=n1;
  n1.prev=n0;n1.next=n2;
  n2.prev=n1;n2.next=n3;
  if (n3) n3.prev=n2;
},
moveRight:function() {
  if (!this.next) return;
  var n0=this.prev;
  var n2=this;
  var n1=this.next;
  var n3=this.next.next;
  if (n0) n0.next=n1; else this.parent.first=n1;
  n1.prev=n0;n1.next=n2;
  n2.prev=n1;n2.next=n3;
  if (n3) n3.prev=n2;
},
del:function() {
  this.detach();
  var node=this.first;
  while (node) {
    var nodenext=node.next;
    node.detach();
    node=nodenext;
  }
  delete nodeBag[sel];
  unselect();
},
delBranch:function() {
  this.detach();
  var node=this.first;
  while (node) {
    node.delBranch();
    node=node.next;
  }
  delete nodeBag[this.id];
  unselect();
},
attach:function() {
  var c=document.getElementById('chart');
  c.innerHTML=c.innerHTML+'<div class="message">Select the target box</div>';
  attach_sel=sel;
},
doAttach:function(s) {
  attach_sel=0;
  var par=nodeBag[s];
  var node=par;
  while (node) {
    if (node==this) {
      alert('ERROR: Attaching to this node would create a loop.');
      return;
    }
    node=node.parent;
  }
  if (this.parent) this.detach();
  this.parent=par;
  if (!par.first) {
    par.first=this;
    return;
  }
  var node=par.first;
  while (node.next) {node=node.next;}
  this.prev=node;
  node.next=this;
},
update:function() {
  var f=document.forms[0];
  for (var i=0;i<f.elements.length;i++)
    this.data[f.elements[i].name]=f.elements[i].value;
}

} //end node prototype definition

function choose(s) {
  if (attach_sel) {selnode.doAttach(s);renderIt();return;}
  if (sel) try{document.getElementById('box'+sel).className='box';}catch(e){unselect()}
  if (sel!=s) {
    sel=s;selnode=nodeBag[sel];
    document.getElementById('box'+sel).className='boxChecked';
    var f=document.forms[0];f.reset();
    for (var p in selnode.data) f[p].value=selnode.data[p];
    document.getElementById('props').style.display=document.getElementById('chartpart').style.display;
  } else {
    unselect();
  }
}


function unselect() {
  sel=0;selnode=false;document.forms[0].reset();
  document.getElementById('props').style.display='none';
}


function parseIt() {
  nr=1;nodeBag={};unselect();
  try {
    eval(document.forms[1].source.value);
  } catch(e) {
    alert('error: '+(e.message)+'\nthis is not valid syntax.');
  }
}


function generate() {
  var s='';
  for (var i in nodeBag)
    if (!nodeBag[i].parent) s+=nodeBag[i].getCode()+';\n';
  document.forms[1].source.value=s;
}


function renderIt() {
  var s='';var l="0;"
  for (var i in nodeBag)
    if (!nodeBag[i].parent) {
      if (l) s+='<HR width="70%">';
      s+=nodeBag[i].render();
      l++;
    }
  document.getElementById('chart').innerHTML=s||' ';
}


function hover(el) {
  el.oldclass=el.className;
  el.className=el.className+'Hover';
}


function unhover(el) {
  el.className=el.oldclass;
}

function loadExample() {


<%
SQL = "Select * from PROJECT where proj_parent=0"
objRS.Open SQL, dbConnect

response.write("new Node({name:'" & Trim(objRS("proj_name")) & "', manager:'" & Trim(objRS("manager")) & "', purpose:'" & Trim(objRS("Purpose")) & "', description:'" & Trim(objRS("description")) & "', start:'" & Trim(objRS("start_date")) & "', completion:'" & Trim(objRS("EstComp_Date")) & "'})")


con.close
objRS.close
%>

  new Node({name:'Boss of Org',email:'boss@example.com', purpose:'To serve and protect the W/Ww needs of the public at large and to ensure that this will properly handle the double line problem, though I wish I could just rowspan it'},[
    new Node({name:'One Manager',email:'man1@example.com'},[
      new Node({name:'B. Fast',email:'bfast@example.com'},[
        new Node({name:'Bea Worker',email:'beaworker@example.com'}),
        new Node({name:'D. Plebian',email:'dplebian@example.com'})
      ])
    ]),
    new Node({name:'Manny Two',email:'man2@example.com'},[
      new Node({name:'Slick Talkman',email:'stalkman@example.com'},[
        new Node({name:'Patricia Guru',email:'pguru@example.com'}),
        new Node({name:'Mark Time',email:'mtime@example.com'})
      ]),
      new Node({name:'D. Warbucks',email:'dwarbucks@example.com', description:'Lallalalalalal'}),
      new Node({name:'Berry Smart',email:'bsmart@example.com'})
    ])
  ]);
  renderIt();
}

</script>
</head>

<body onload="loadExample();">

<table width="100%" height="100%" cellspacing="0" cellpadding="0">
<tr><td id="header">
<table cellspacing="0" cellpadding="0" style="margin-left:4px"><tr>
</tr></table>
</td></tr><tr><td id="body">

    

<table width="100%" class="border"0 cellspacing="0" cellpadding="0" id="chartpart">
<tr><td class="header">Transportation & Works</td></tr>
<tr><td id="chart" class="border"> </td></tr>

<form onsubmit="return false">
<center>
<table class="border" cellspacing="0" cellpadding="0" style="display:none" id="props">
<tr><td class="header" colspan="2">Project Details</td></tr>
<tr><th class="props">Name:</th><td class="border"><input class="props" size="100" type="text" readonly name="name" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<tr><th class="props">Manager:</th><td class="border"><input class="props" size="100" type="text" readonly name="manager" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<!--<tr><th class="props">E-Mail:</th><td class="border">--> <input type=hidden class="props" size="100" readonly type="text" name="email" value="" onblur="if (selnode) selnode.update();renderIt();" />  <!-- </td></tr> --> 
<tr><th class="props">Purpose:</th><td class="border"><textarea cols=75 rows=3 class="props" size="100" readonly type="text" name="purpose" value="" onblur="if (selnode) selnode.update();renderIt();" /> </textarea></td></tr>
<tr><th class="props">Description:</th><td class="border"><textarea cols=75 rows=3 class="props" size="100" type="text" readonly name="description" value="" onblur="if (selnode) selnode.update();renderIt();" /> </textarea></td></tr>
<tr><th class="props">Start Date:</th><td class="border"><input class="props" size="100" type="text" readonly name="start" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<tr><th class="props">Est. Completion Date:</th><td class="border"><input class="props" size="100" type="text" readonly name="completion" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
</table>
</center>
</form>


<div style="display:none" id="codepart">
<form onsubmit="return false">
<table width="100%" class="border" cellspacing="0" cellpadding="0">
<tr><td class="header">Code</td></tr>
<tr><td class="border">
<textarea name="source" cols="80" style="width:100%" rows="12" class="noborder">
</textarea>
</td></tr>
</table>
</form>


</td></tr><tr><td id="footer">
</td></tr></table>

<script>initButtons()</script>
</body>
</html>


I need it to be able to call the database information and output it much like the nodes (those are hard-coded and work) to make a dynamic chart. I bolded the part that needs the code.

Any help would be greatly appreciated.

Thanks,
MarkGG

madhivanan
Premature Yak Congratulator

22864 Posts
Posted - 2005-09-08 : 09:23:43
Not sure whether these will be helpful

http://www.nigelrivett.net/RetrieveTreeHierarchy.html
http://www.seventhnight.com/treestructs.asp


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

MarkGG
Yak Posting Veteran

53 Posts
Posted - 2005-09-08 : 10:15:36
I found the first article to be the more useful of the two, but I need some time to digest this

Everything I have found seems so confusing, I thought it would be pretty simple but I guess not.

Thanks for the help,
Mark
Go to Top of Page

MarkGG
Yak Posting Veteran

53 Posts
Posted - 2005-09-13 : 13:34:43
Alright here is what I have now. I have it so that the first set of children work, but when it gets to seeding the children of children it doesn't fit what I have. Hopefully this makes more sense as to what I am doing:


<head>
<script src="w3f.js"></script>

<style>
@import "w3f.css";
.box, .boxHover, .boxChecked, .boxCheckedHover {
  padding:0.5em 1em;
  margin:0em;
  cursor:default;
  border:1px solid #000040;
  background:#ABCDEF;
  color:#000040;
}
.boxHover {
  background:white;
}
.boxCheckedHover {
  border-color: #4040A0;
  color: #4040A0;
  background:white;
}
.boxChecked {
  background: #F8F8FC; 
  border-color: #4040A0;
  color: #4040A0;
}
.boxc {
  padding:0em 0.3em;
  text-align:center;
}
.boxc TABLE {
  margin:auto;
}
.lineright {
  border-right:1px solid #000040;
  font-size:50%;
}
.leftabove {
  border-right:1px solid #000040;
  border-top:1px solid #000040;
  font-size:50%;
}
.rightabove {
  border-top:1px solid #000040;
  font-size:50%;
}
.nolines {
  font-size:50%;
}
TD#chart {
  padding:1em;
}
DIV.message {
  color:#4040A0;
  font-weight:bold;
  background-color:#F8F8FC;
  border:1px solid #4040A0;
  margin:1em 0em;
  padding:0em 0.5em;
}
DIV.somespace {
  padding:0.2em;
  text-align:center;
}
DIV.somespace TABLE {
  margin:auto;
}
</style>

<%
ON ERROR RESUME NEXT
Dim con,sql,constring, rs, ParentNode
Set con = Server.CreateObject("ADODB.Connection")
%>

<!-- #include file="dbConnect.ssi" -->

<%
set objRS=Server.CreateObject("ADODB.recordset")
'I open my connection
con.Open dbConnect
%>

<script>
var nr=1;
var nodeBag={};
var sel=0;var attach_sel=0;
var selnode;

function Node(data,childNodes) {
  this.data=data;
  this.id=nr++;
  nodeBag[this.id]=this;
  if (childNodes) {
    this.first=childNodes[0];
    for (var i=0;i<childNodes.length;i++) {
      childNodes[i].prev=childNodes[i-1];
      childNodes[i].next=childNodes[i+1];
      childNodes[i].parent=this;
    }
  }
}
Node.prototype={
render:function(finalver) {
  var s='';var next='';var l=1;
  if (this.first) {
    l=0;var node=this.first;
    while (node) {
      next+='<TD valign="top">'+node.render(finalver)+'</td>';
      node=node.next;l++;
    }
  }
  s+='\n<table cellspacing="0" cellpadding="0" width="100%"><tr><td colspan='+l+'>';
  s+='\n<table cellspacing="0" cellpadding="0" width="100%">';
  if (this.parent) {
    s+='\n<tr><td width="50%" class="'+(!this.prev?'lineright':'leftabove')+'"> </td>';
    s+='<td width="50%" class="'+(!this.next?'nolines':'rightabove')+'"> </td></tr>';
  }
  s+='\n<tr><td colspan="2" class="boxc">\n<table cellspacing="0" cellpadding="0">';
  s+='\n<tr><td class="'+(sel==this.id&&!finalver?'boxChecked':'box')+'"'
  if (!finalver) s+=' onclick="unhover(this);choose('+this.id+');hover(this);" id="box'+this.id+'" onmouseover="hover(this)" onmouseout="unhover(this)"';
  s+='"><nobr>';
  if (finalver&&this.data.email) s+='<a href="mailto:'+this.data.email+'">';
  s+=(this.data.name||'(no name)');
  if (finalver&&this.data.email) s+='</a>';
  s+='</nobr></td></tr></table>\n</td></tr>';
  if (this.first) s+='\n<tr><td width="50%" class="lineright"> </td><td width="50%"></td></tr>';
  s+='</table></td></tr><tr>'+next+'</tr></table>';
  return s;
},
update:function() {
  var f=document.forms[0];
  for (var i=0;i<f.elements.length;i++)
    this.data[f.elements[i].name]=f.elements[i].value;
}

} //end node prototype definition

function choose(s) {
  if (attach_sel) {selnode.doAttach(s);renderIt();return;}
  if (sel) try{document.getElementById('box'+sel).className='box';}catch(e){unselect()}
  if (sel!=s) {
    sel=s;selnode=nodeBag[sel];
    document.getElementById('box'+sel).className='boxChecked';
    var f=document.forms[0];f.reset();
    for (var p in selnode.data) f[p].value=selnode.data[p];
    document.getElementById('props').style.display=document.getElementById('chartpart').style.display;
  } else {
    unselect();
  }
}


function unselect() {
  sel=0;selnode=false;document.forms[0].reset();
  document.getElementById('props').style.display='none';
}


function parseIt() {
  nr=1;nodeBag={};unselect();
  try {
    eval(document.forms[1].source.value);
  } catch(e) {
    alert('error: '+(e.message)+'\nthis is not valid syntax.');
  }
}


function generate() {
  var s='';
  for (var i in nodeBag)
    if (!nodeBag[i].parent) s+=nodeBag[i].getCode()+';\n';
  document.forms[1].source.value=s;
}


function renderIt() {
  var s='';var l="0;"
  for (var i in nodeBag)
    if (!nodeBag[i].parent) {
      if (l) s+='<HR width="70%">';
      s+=nodeBag[i].render();
      l++;
    }
  document.getElementById('chart').innerHTML=s||' ';
}


function hover(el) {
  el.oldclass=el.className;
  el.className=el.className+'Hover';
}


function unhover(el) {
  el.className=el.oldclass;
}

function loadExample() {


<%
ParentNode = 0
SQL = "Select * from PROJECT order by proj_parent, proj_id"
objRS.Open SQL, dbConnect
do until objRS.EOF

if objRS("Proj_parent") = ParentNode then
response.write("new Node({name:'" & Trim(objRS("proj_name")) & "', manager:'" & Trim(objRS("manager")) & "', purpose:'" & Trim(objRS("Purpose")) & "', description:'" & Trim(objRS("description")) & "', start:'" & Trim(objRS("start_date")) & "', completion:'" & Trim(objRS("EstComp_Date")) & "'}, [")
response.write(vbcrlf)
ParentNode = objRS("Proj_id")


else
response.write("]),")
response.write(vbcrlf)
ParentNode = ParentNode - 1
response.write("new Node({name:'" & Trim(objRS("proj_name")) & "', manager:'" & Trim(objRS("manager")) & "', purpose:'" & Trim(objRS("Purpose")) & "', description:'" & Trim(objRS("description")) & "', start:'" & Trim(objRS("start_date")) & "', completion:'" & Trim(objRS("EstComp_Date")) & "'})")
response.write(vbcrlf)
end if

objRS.movenext
Loop
objRS.close
con.close
set con = nothing
%>


  new Node({name:'Boss of Org',email:'boss@example.com', purpose:'To serve and protect the W/Ww needs of the public at large and to ensure that this will properly handle the double line problem, though I wish I could just rowspan it'},[
    new Node({name:'One Manager',email:'man1@example.com'},[
      new Node({name:'B. Fast',email:'bfast@example.com'},[
        new Node({name:'Bea Worker',email:'beaworker@example.com'}),
        new Node({name:'D. Plebian',email:'dplebian@example.com'})
      ])
    ]),
    new Node({name:'Manny Two',email:'man2@example.com'},[
      new Node({name:'Slick Talkman',email:'stalkman@example.com'},[
        new Node({name:'Patricia Guru',email:'pguru@example.com'}),
        new Node({name:'Mark Time',email:'mtime@example.com'})
      ]),
      new Node({name:'D. Warbucks',email:'dwarbucks@example.com'}),
      new Node({name:'Berry Smart',email:'bsmart@example.com'})
    ])
  ]);
  renderIt();
}

</script>
</head>

<body onload="loadExample();">

<table width="100%" height="100%" cellspacing="0" cellpadding="0">
</td></tr><tr><td id="body">

<table width="100%" class="border"0 cellspacing="0" cellpadding="0" id="chartpart">
<tr><td class="header">Transportation & Works</td></tr>
<tr><td id="chart" class="border"> </td></tr>

<form onsubmit="return false">
<center>
<table class="border" cellspacing="0" cellpadding="0" style="display:none" id="props">
<tr><td class="header" colspan="2">Project Details</td></tr>
<tr><th class="props">Name:</th><td class="border"><input class="props" size="100" type="text" readonly name="name" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<tr><th class="props">Manager:</th><td class="border"><input class="props" size="100" type="text" readonly name="manager" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<!--<tr><th class="props">E-Mail:</th><td class="border">--> <input type=hidden class="props" size="100" readonly type="text" name="email" value="" onblur="if (selnode) selnode.update();renderIt();" />  <!-- </td></tr> --> 
<tr><th class="props">Purpose:</th><td class="border"><textarea cols=75 rows=3 class="props" size="100" readonly type="text" name="purpose" value="" onblur="if (selnode) selnode.update();renderIt();" /> </textarea></td></tr>
<tr><th class="props">Description:</th><td class="border"><textarea cols=75 rows=3 class="props" size="100" type="text" readonly name="description" value="" onblur="if (selnode) selnode.update();renderIt();" /> </textarea></td></tr>
<tr><th class="props">Start Date:</th><td class="border"><input class="props" size="100" type="text" readonly name="start" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
<tr><th class="props">Est. Completion Date:</th><td class="border"><input class="props" size="100" type="text" readonly name="completion" value="" onblur="if (selnode) selnode.update();renderIt();" /></td></tr>
</table>
</center>
</form>

<script>initButtons()</script>
</body>
</html>


Thanks,
Mark
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site