Recursive Menu Function - Am I Creating Memory Pro

Results 1 to 2 of 2

Thread: Recursive Menu Function - Am I Creating Memory Pro

  1. #1
    Join Date
    Dec 1969

    Default Recursive Menu Function - Am I Creating Memory Pro

    Hi, I am building a menu tree from an Access database using ASP (vbscript). To do this, I have made a function to open a recordset first calling the record at the top of the menu tree (using a parentID), and then recursively calling the same function from within itself to build the menu (passing in the current pageID as the new parent ID). <BR><BR>It works, but I am concerned about the number of times I can open (ADODB) a new recordset to build the entire menu. The recordsets don&#039;t seem to be closing until the end, though I am uncertain of this. Is there a limit to the number of recordsets that can be open simultaneously? I ask this, as the function appears to works fine on my development machine, but stops after about 75 records on the hosting server.<BR> <BR>I have pasted the entire function (slightly streamlined) below. Any help or advice would be greatly appreciated.<BR> <BR>Thanks,<BR>Geoff<BR> <BR> <BR><BR> Dim uid, child_id, indent_level<BR> <BR> DisplayChildren firstParentID, 1 <BR> <BR> Function DisplayChildren(uid, indent_level) <BR> <BR> Dim rs<BR> &#039; SQL for Recursive PAGE RECORDSET FOR ENTIRE PAGE LIST <BR> &#039; (Showing only pages with suitable permission levels)<BR> <BR> strSQL = "SELECT DISTINCT tblPages.pageID, tblPages.pgTypeID, tblPages.parentID, tblPages.pgName, tblPages.pgFolderPath, tblPages.pgFileName, tblPages.pgActive, tblPages.pgActivateDate, tblPages.pgExpiryDate, "<BR> strSQL = strSQL & " tblUserTypes.level"<BR> strSQL = strSQL & " FROM (tblUserTypes INNER JOIN tblPages ON = tblPages.pgVwLevId)"<BR> strSQL = strSQL & " WHERE tblPages.parentID=" & uid <BR> <BR> set rs = Server.CreateObject("ADODB.Recordset")<BR> rs.ActiveConnection = strDBString<BR> rs.Source = strSQL<BR> rs.Open<BR> <BR> <BR> If Not rs.EOF Then <BR> Do Until rs.EOF <BR> <BR> child_id = rs("pageID")<BR> <BR> DisplayChildren child_id, indent_level+1<BR> <BR> rs.MoveNext <BR> Loop<BR> End If<BR> rs.Close<BR> Set rs = Nothing <BR> <BR> End Function<BR>

  2. #2
    Join Date
    Dec 1969

    Default Really bad practice...

    Any kind of recursion involving recordsets is almost always deadly.<BR><BR>This topic has been discussed to death in these forums and others (e.g., There *are* solutions. Some based on SQL (using a slightly re-designed DB), some based on pulling all records into an array using GetRows() and then doing the recursion there, some using XML and XSL style sheets.<BR><BR>Any of which are better than RS-based recursion.<BR><BR>Look in the forums for "Hierarchical" and you should get lots of hits.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts