Recursive display from a database

Results 1 to 3 of 3

Thread: Recursive display from a database

  1. #1
    Mitch Trope Guest

    Default Recursive display from a database

    I am using the following recursive function to cycle through the messages in a message board to try and build the display so the messages all appear correctly. The function was originally written for use with an Access database, and now I&#039;m trying to port it to SQL Server 7. The error I&#039;m getting:<BR><BR>Microsoft OLE DB Provider for ODBC Drivers error &#039;80004005&#039; <BR>[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt <BR><BR>/forumtest/Message.asp, line 40<BR><BR>The line number corresponds to the Set Rst=objConn.Execute(...) statement. This appears to occur on the second time the function is called. Any suggestions would be greatly appreciated. Thanks in advance.<BR><BR>Function Code:<BR>public sub buildtree(messID, level)<BR> Dim Rst<BR> Set Rst = objConn.Execute("Select * from Forums where forumID=" & Mforum & " ORDER BY dateCreated DESC")<BR> Rst.Filter = "messageID = " & messID<BR> Response.write "&#060;TR&#062;"<BR> Response.write("&#060;TD&#062;" & trim(Rst.Fields("author")) & "&#060;/TD&#062;")<BR> Response.write "&#060;td&#062;" & FormatDateTime(Rst.Fields("dateCreated"),2) & "&#060;/td&#062;"<BR> Response.write "&#060;TD&#062;" <BR> for i=1 to cint(level)<BR> Response.write trim(IndentStr)<BR> next<BR> Response.write "&#060;a href=""Message.asp?messageid=" & Rst.Fields("messageID") & """&#062;"<BR> Response.write trim(Rst.Fields("subject")) & "&#060;/a&#062;&#060;/TD&#062;" & vbcrlf<BR> Response.Write "&#060;/tr&#062;"<BR> Rst.Filter = "replyTo = " & messID<BR> if not (Rst.EOF and Rst.bof) then<BR> Rst.movefirst<BR> while not Rst.eof<BR> buildtree Rst("messageID"), level+1<BR> Rst.movenext<BR> wend<BR> end if<BR>end sub<BR><BR>Called by (code from body of page):<BR> dim threads<BR> Set threads = objConn.Execute("SELECT * from Forums where forumID=" & Mforum & " and replyTo=messageID order by dateCreated DESC")<BR> threads.movefirst<BR> Response.Write "&#060;table&#062;"<BR> while not threads.eof<BR> buildtree threads("messageID"), 0<BR> threads.movenext<BR> wend

  2. #2
    Join Date
    Dec 1969

    Default This is poor design...

    ...I think the FAQs on this web site offer some sample threaded forums. Making this many return trips to the database is a very, very bad idea. It&#039;ll never stand up to any kind of real traffic regardless of the DB you are using.

  3. #3
    the other steve Guest

    Default RE: This is poor design...

    It appears to me that you are repeatedly creating recordsets and, if using access, there is a limit to how many you can open at once, and you wouldn&#039;t want unlimited recordsets open anyway since it could bring your whole system to a halt.<BR><BR>the solution, as the previous comment suggested, is to redesign your algorithm to avoid recursive calls. I would recommend using MSDatashape, which is specifically designed for stuff like this. Or perhaps save the recordsets to an array before you open the next one.<BR><BR>I don&#039;t think repeated calls to get recordsets is bad, you just shouldn&#039;t have the server opening many at once. They should be opened, used or saved to an array and destroyed.<BR><BR>Or you should drink more.

Posting Permissions

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