Classic ASP Recursive function

Question

Hi everyone,

I havent done any classic ASP for a couple of years and now trying to get back into it from c# is prooving impossible! I've got a recursive function which very simply is supposed to query a database based on a value passed to the function and once the function has stopped calling itself it returns the recordset....however im getting the old error '80020009' message. I've declared the recordset outside of the function.

Cany anyone see the error in my ways?

Dim objRsTmp

Function buildList(intParentGroupID)

 Set objRsTmp = Server.CreateObject("Adodb.Recordset")
 SQLCommand = "SELECT * FROM tblGroups WHERE tblGroups.intParentGroupID = " & intParentGroupID & ";"
 objRsTmp.Open SQLCommand, strConnection, 3, 3

 If Not objRsTmp.Eof Then

  While Not objRsTmp.Eof

   Response.Write(objRsTmp("strGroup") & "<br />")

   buildList(objRsTmp("intID"))

   objRsTmp.MoveNext

  Wend

 End If

 Set buildList = objRsTmp

 '#objRsTmp.Close()
 'Set objRsTmp = Nothing

End Function

Set objRs = buildList(0)

If Not objRs.Eof Then

 Response.Write("There are records")

 While Not objRs.Eof

  For Each oFld in objRs.Fields
   Response.Write(oFld.Name & ": " & oFld.Value & ",")
  next

  Response.Write("<br />")

  objRs.MoveNext

 Wend

End If

Any assistance would be greatly appreciated.

Regards, Al

Answer 1

There is a single instance of objRsTmp in global scope, meaning its shared between every buildList() on the stack; so whenever you recurse into buildList() and call objRsTmp.Open() your causing the prior buildList() on the stack to use the new objRsTmp.

I don't think this is what your after as your looping its rows as though every buildList() had its own set of results.

To do that you would have to make the recordset local... however, that's a *very expensive way to do it, particularly because each iteration is also creating a new connection object.

Depending on your schema/rdbms there is usually a single-query way to pull of a set of hierarchically related records.