Nested Loop 2

[d222222]

I want to retrieve the data from a database and put it in a table but I need to use a nested loop. I think my code is close and I know I've done it before but I can't that code any where.

I want it to look like this:

Folder 1
File 1
File 4
Folder 2
File 3
File 7
Folder 3
File 5

I have a Folder table and File table and I want it to get the FolderID from the Folder table and then get the FolderID's in the Files table that match.

This is what I have so far:

Set rsFolders = objConn.execute("SELECT FolderID, FolderName FROM Folders GROUP BY FolderID, FolderName")

Set rsFiles = objConn.execute("SELECT * FROM FileInfo WHERE FolderID=" & sFolderID & " ORDER BY FileName")

<% While not rsFolders.EOF %>	
      <%=rsFolders("FolderName")%><br>
          <% While not rsFiles.EOF %>	          
          	<%=rsFiles("Filename")%><br>
 	  <%  rsFiles.MoveNext	
	      Wend
	  %>    			
<%  rsFolders.MoveNext	
    Wend
%>

I get an error message on the line that has the Select statement that has the "sFolderID" in it. I know I need to retrieve that from the first recordset but I'm not sure how to do that within the loop.


Thanks.

 

[d222222]

I'm getting closer. I added:

sFolderID = rsFolders("FolderID")

I put it under the Set rsFolders....

It works for the first Folder it finds in the table and gets all of the correct filenames from the second table but it doesn't look to the next folder. I tried putting the new code in the loop and it doesn't do it.


Thanks

 

[onpnt]

You are fighting the database with everything you have.

First:

Folder table and File table and I want it to get the FolderID from the Folder table and then get the FolderID's in the Files table that match

This task has no place in your code. No excuses and no reasoning at all. this is a simple join condition. You actually said it there

Set rsFolders = objConn.execute("SELECT FolderID, FolderName FROM Folders GROUP BY FolderID, FolderName")

Select * From Folders 
Inner Join FileInfo On Folders.FolderID = FileInfo.FolderID

This will give you not truly an optimized view of course sense you didn't give the columns in FileInfo its hard to say how to make it a bit easier.

So you'll get something like this

FolderID FolderName FileName
1 one fileone
1 one fileonetwo
2 two filetwo
3 three filethree
4 four filefour
5 five filefive

So now all you need to do is loop once. write the file name out for each folder name and keep checking to see if folder name changes. When it does write the folder name out instead.


_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[nicklieb]

^^^^ thats easier

 

[d222222]

Thanks. I did change the SQL statement and it's getting the right results. Now I'm playing with the code and trying to get it to check when the FolderName changes. That's the part I was having problems with.

 

[onpnt]

I made this very crude but I think the way I wrote it will explain to you how you need to do it. There are much faster and better ways though but may be more confusing

<%
'my db connection and object
DIM objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("Provider=SQLNCLI;Server=myserver;Database=test;Uid=test;Pwd=test;")

'fill recordset with my view into memory
Set rs = objConn.Execute("Select fd.FolderID, [FileName], [FolderName] " & _
				"From Files fl " & _
				"Inner Join Folders fd On fl.FolderID = fd.FolderID")

'first check to see if there are records before
'grabbing the initial folder name
If NOT rs.EOF Then
	Dim fld : fld = rs("FolderName")
	'if we're here we should just write out the first folder name 
	Response.Write fld & "<br />"
End If

Do While NOT rs.EOF
'check to see if we ever got a folder name
'more than likly this wouldn't happen sense EOF would have been 
'found but its good habit anyhow.  If the names are different
'then reset it and write it out. (don't forget to write out filename
 If Not IsNull(fld) And fld <> rs("FolderName") Then
 	fld = rs("FolderName")
 	Response.Write fld & "<br />"
 	Response.Write "&nbsp;&nbsp;&nbsp;" & rs("FileName") & "<br />"

 ElseIf Not IsNull(fld) And fld = rs("FolderName") Then
	'we must not have changed cause we're just going to write filename
 	Response.Write "&nbsp;&nbsp;&nbsp;" & rs("FileName") & "<br />"
 End If

rs.MoveNext
Loop
%>

_{____________ signature below ______________
I am Tedward Keyboardhands!!!
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[d222222]

Wow! Thanks a lot!!

 

[d222222]

I discovered that this works only if there are files in a folder. However when I need to add a folder it doesn't shoe the folder because there isn't a record in the fileinfo table that has that folderid in it. I need it to list ALL of the folder and then if there is a file associated with the folderid then I need to list it. I tried changing it to an outer join and a regular join but they don't work. How do I do this?

 

[d222222]

I still can't get this to work for the folder that doesn't have a file in it. I want it to take each FolderID in the Folder table and see if there is a file in the File table that has the same FolderID. I can't get it to do that.

Thanks.