Nested Loop (SQL) not working Properly

[theflyingminst]

Hi, I've nested a loop to write out all comments corresponding to it's original message (RS & RSs). The loop works perfectly fine for the first record in "RS" but will not display for the rest of the records.

<%

Set adoCon = Server.CreateObject("ADODB.Connection")
strCon = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &Server.MapPath("data.mdb")
adoCon.Open strCon
Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM MESSAGES ORDER BY ID Desc"
RS.Open strSQL, adoCon

uniquen = RS("uniquen")

Set RSs = Server.CreateObject("ADODB.Recordset")
strSQL2 = "SELECT * FROM COMMENTS WHERE uniquen LIKE '%" & uniquen & "%' ORDER BY ID Desc"
RSs.Open strSQL2, adoCon

Do While Not RS.EOF

response.write "-----------<br>"
response.write RS("message")
response.write "<br>"

Do While Not RSs.EOF

response.write RSs("comments")
response.write "<br>"

RSs.MoveNext
Loop

response.write "<br>-----------"

RS.MoveNext
Loop


Set RS = Nothing
Set RSs = Nothing
adoCon.Close
Set adoCon = Nothing

%>

Any help is greatly appreciated.

Thank you

[Kuriyama]

This is because the inner record set never gets new SQL to work with and thus is always at the EOF after the first iteration.

Move

strSQL2 = "SELECT * FROM COMMENTS WHERE uniquen LIKE '%" & uniquen & "%' ORDER BY ID Desc"
RSs.Open strSQL2, adoCon


Inside the first loop and it should work correctly.

[theflyingminst]

Hey thanks so much. I changed the code and I'm getting this error:

ADODB.Recordset error '800a0e79'

Operation is not allowed when the object is open.

view-msgs.asp, line 136


I tried moving the "Set RSs = Nothing" after it's loop and even made a second connection string to close it after the RSs loop, and then I get errors that it's not open now.

[Kuriyama]

Don't set it to nothing, just close the record set before that inner while loop ends.

rs2.close

if it give your errors do this.

if rs2.stat <> 0 then
rs2.close
end if

Remember setting it to nothing destories that object. You want to reuse that object over and over again. Inside that inner while loop you open the RS, spit out some data, close the object. Rinse and repeat.

[theflyingminst]

That worked. Thanks so much!