Out of Memory: 'Session' 1

[Kavius]

What does this mean? What have I done?

function ItemList(Indent, Parent)
  dim rtrn
  dim rs
  dim sql
  dim d
  d = string(indent," ")

  sql = "SELECT ItemNum,Name,size " & _
        "FROM   things " & _
        "WHERE  parent=" & parent
  set rs = server.createobject("ADODB.Connection")
  rs.open sql, Application("ConnectionString")
  while(not rs.eof)
    rtrn = rtrn & _
      &quot;<tr>&quot; & vbcrlf & _
      &quot;  <td>&quot; & rs(&quot;Name&quot;) & &quot;</td>&quot; & vbcrlf & _
      &quot;  <td>&quot; & rs(&quot;size&quot;) & &quot;</td>&quot; & vbcrlf & _
      &quot;<tr>&quot; & vbnewline
    rtrn = rtrn & ItemList(indent+1,rs(&quot;ItemNum&quot;))
    rs.movenext
  wend
  rs.close
  set rs = nothing
  ItemList = rtrn
end function

What I am trying to do is create a directory style list of items where sub items are indented. Everytime this function gets called I get an "Out of Memory" error.

Working with recursion, this normally wouldn't surprise me but I got this error while testing the "add item" functionality. I had just added my first item. This function should not recurse more than two deep.

Thanks in advance.
---------------------------------------
Where would one begin to study Myology?

 

[JohnYingling]

The function is calling itself recursively with
ItemList(indent+1,rs("ItemNum&quot)

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[JohnYingling]

And you have a very deep tree or there is a circular reference in there.
A<-+
| |
B |
| |
C->+

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[JohnYingling]

Try using parent

sql = &quot;SELECT ItemNum,Name,size,parent &quot; & _
&quot;FROM things &quot; & _
&quot;WHERE parent=&quot; & parent

rtrn = rtrn & ItemList(indent+1,rs(&quot;Parent&quot)

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[JohnYingling]

Forget the previous post. It is late and I am in a loop.

http://www.VBResizer.com

Forms/Controls Resizing/Tabbing Control

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort Class in VB or VBScript

 

[Kavius]

JohnYinling...Thank you. The code I submitted originally is the solution to my problem, not the problem.

I had typed it in from memory (in this thread) and found that when I looked at it this morning there was a key difference between what I had typed here and what was on the page...

The code that JohnYingling posted was what was causing the problem.

I will post an indepth explanation later (for those who are new to recursion and may experience this problem in the future).

John...thanks. Your accidental blunder pointed me straight to my problem.
---------------------------------------
Where would one begin to study Myology?

 

[Kavius]

OK...so I promissed an indepth explanation of my recursion problem. First, some keywords for people searching:[ul]
[li]recursion[/li]
[li]recursive[/li]
[li]tutorial[/li]
[li]error[/li]
[li]memory[/li][/ul]
So the original piece of code that I posted is the solution to the problem. To change the code to duplicate the problem change ItemNum to Parent in the SQL statement and then change the line:

rtrn = rtrn & ItemList(indent+1,rs("ItemNum"))

to

rtrn = rtrn & ItemList(indent+1,rs("Parent"))

In the table things there is only one row but for explanation purposes lets put three in:
[tt]
ItemNum Parent Name Size
^^^^^^^ ^^^^^^ ^^^^^^^^^^^ ^^^^
1 0 Gift Basket 10
2 1 Corn 6
3 1 Jam 4
[/tt]
In this case, when I want to start the whole process of building my HTML tables, I would do an initial call to this function with a 0 indent and 0 parent:

<table>
<%=ItemList(0,0)%>
</table>

With the code that causes the error, the SQL statement will return one row to the recordset:
[tt]
ItemNum Parent Name Size
^^^^^^^ ^^^^^^ ^^^^^^^^^^^ ^^^^
1 0 Gift Basket 10
[/tt]
Because there is one row the loop would be entered, rtrn will have the appropriate information placed in it and ItemList will be called to see if &quot;Gift Basket&quot; has any children. This is where the problem starts.

rtrn = rtrn & ItemList(indent+1,rs("Parent"))

The Current record will be the parent for the next set of items. Because of this, I want to be passing in the current item, but I am passing in the current item's parent:

rtrn = rtrn & ItemList(1,0)

Compare this to the original call. The parent is identical, so this call to ItemList will result in the same row being returned. Can you guess what the next call to ItemList was?

rtrn = rtrn & ItemList(2,0)

As you can see, the indent is getting deeper, but the records are not going any deeper into the parent/child structure.

The end result of this was code was an out of memory error but the HTML it was generating looked something like this:

  <tr>
    <td>Gift Basket</td>
    <td>100</td>
  </tr>
  <tr>
    <td> Gift Basket</td>
    <td>100</td>
  </tr>
  <tr>
    <td>  Gift Basket</td>
    <td>100</td>
  </tr>

It just kept building the same row, over and over, indenting the Item more and more until the session ran out of memory.

Now let's walk through the correct code.

The initial call to function remains the same, but the first call within the function gets different values. Remember the row that gets returned the first time:
[tt]
ItemNum Parent Name Size
^^^^^^^ ^^^^^^ ^^^^^^^^^^^ ^^^^
1 0 Gift Basket 10
[/tt]
This generates a call, within the loop, that looks like this:

rtrn = rtrn & ItemList(1,1)

Indent = 1
Parent = 1
This means that on the next pass the sql statement will vary slightly and return a different recordset. Only items with a parent of 1:
[tt]
ItemNum Parent Name Size
^^^^^^^ ^^^^^^ ^^^^^^^^^^^ ^^^^
2 1 Corn 6
3 1 Jam 4
[/tt]
So we now have two nested instances of the function and the second one contains the above recordset. As we step into the while loop the row for &quot;Corn&quot; is created and then a call to ItemList is done to get any of &quot;Corn&quot;'s children:

rtrn = rtrn & ItemList(2,2)

In this third instance of ItemList the SQL statement is looking for items with a parent=2. There are none, so the recordset is empty, the while loop is never entered and there is no call to ItemList, nothing is returned and we are back in the second instance.

In the second instance the loop moves to the next record, the HTML row is generated and we check for children of item 3:

rtrn = rtrn & ItemList(2,3)

This instance of ItemList (three deep) behaves identically to the &quot;Corn&quot; check and returns nothing.

Back in the second instance, we are out of records in our recordset and the function returns its HTML to the first instance:

  <tr>
    <td> Corn</td>
    <td>60</td>
  </tr>
  <tr>
    <td> Jam</td>
    <td>40</td>
  </tr>

Now we are in the first instance and the statment dictates that we append the returned text to the text that was already generated:

rtrn = rtrn & ItemList(indent+1,rs("Parent"))

Where rtrn already contains:

  <tr>
    <td>Gift Basket</td>
    <td>100</td>
  </tr>

Giving:

  <tr>
    <td>Gift Basket</td>
    <td>100</td>
  </tr>
  <tr>
    <td> Corn</td>
    <td>60</td>
  </tr>
  <tr>
    <td> Jam</td>
    <td>40</td>
  </tr>

Since &quot;Gift Basket&quot; is the only item in the recordset we are now at EOF, the loop terminates, the function terminates and returns the completed HTML table. The recursion properly indented each of the rows and didn't crash the system. Hooray for me.[smiley]

A couple of Notes on all of this:[ol]
[li]It is still possible to run out of memory once this function is working properly. If the there are to many children of children of children of children of...you get the idea...then the function will keep instantiating itself until either its done, or it runs out of memory. To prevent this I am limiting users as to how deep the item nesting can go.[/li]
[li]When I was in school I had an instructor tell the class that recursive functions could not contain loops. That is a load of horse .... He insisted on this even after giving a similar function to this one as an example. Of course you can place a recursive call in a loop, as long is there is some methodology for terminating the recursion. No names, but I hope some of his current students read this.[/li][/ol]

If you have any questions about this feel free to ask. I will try to remember to keep the email notification turned on on this one.
---------------------------------------
Where would one begin to study Myology?