Looping through 2 recordsets

[welshone]

Hello all,

I am opening 2 connections to different databases , then saying show records in a table where a field from one databsae table = the field from another database table.

however,

It loops through the first recordset showing all school names, but it doesn't loop through the second recordset to show related filenames.
It just shows the first filename next to every school.

this is my code, can anybody see what I am doing wrong ?




<%
Dim SearchTextcompany, MyConncompany, SQLcompany, RScompany

Set MyConncompany=Server.CreateObject(&quot;ADODB.Connection&quot
MyConncompany.Open &quot;schoolsasp&quot;,&quot;schoolsasp&quot;,&quot;schoolsasp&quot;

SQLcompany = &quot;SELECT EstablishmentID, DfEE, EstablishmentName From Establishments ORDER BY EstablishmentName&quot;

Set RScompany = MyConncompany.Execute(SQLcompany)
%>

<%
Dim module


module = Server.HTMLEncode(rscompany.Fields(&quot;dfee&quot.Value)

Param = Request.QueryString(&quot;Param&quot
Data = Request.QueryString(&quot;Data&quot
%>
<%

Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;Holding&quot;,&quot;edholding&quot;,&quot;edholding&quot;
Set Session(&quot;Holding_conn&quot = conn

%>
<%
sql = &quot;SELECT DISTINCT [RepGenA].[filename] FROM RepGenA WHERE SUBSTRING(RepGenA.filename,4,4) = '&quot;&module&&quot;' &quot;


If cstr(Param) <> &quot;&quot; And cstr(Data) <> &quot;&quot; Then
sql = sql & &quot; And [&quot; & cstr(Param) & &quot;] = &quot; & cstr(Data)
End If
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql, conn, 3, 3

if rs.eof then
Response.Write &quot;No Files Uploaded.&quot;
else
%>


<center>

<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=1 CELLPADDING=3><FONT FACE=&quot;Arial&quot; COLOR=#000000>

<THEAD>
<TR>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=white>Establishment</FONT></TH>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=white>Filename</FONT></TH>

</TR>
</THEAD>
<TBODY>

<%
do while Not rscompany.eof
%>

<%
RS.movefirst
do while Not rs.eof
%>

<TR VALIGN=TOP onMouseover=&quot;changeto('yellow')&quot;
onMouseout=&quot;changeback('white')&quot;>

<TD BORDERCOLOR=#c0c0c0 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=#000000><a href=&quot;./viewschool.asp?Establishment=<%=Server.HTMLEncode(rscompany.Fields(&quot;EstablishmentID&quot.Value)%>&quot;><%=Server.HTMLEncode(rscompany.Fields(&quot;DfEE&quot.Value)%>, <%=Server.HTMLEncode(rscompany.Fields(&quot;establishmentname&quot.Value)%></A><BR></FONT></TD>

<TD BORDERCOLOR=#c0c0c0 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=#000000><a href=&quot;./moredetails.asp?Filename=<%=Server.HTMLEncode(rs.Fields(&quot;filename&quot.Value)%>&quot;><%=Server.HTMLEncode(rs.Fields(&quot;filename&quot.Value)%></a><BR></FONT></TD>

</TR>


<%
rs.MoveNext
loop%>

<%
rscompany.MoveNext
loop%>


</TBODY>
<TFOOT></TFOOT>
</TABLE>

</BODY>


<% RS.Close
Conn.Close
set RS = Nothing
set Conn = Nothing
%>

<%
RScompany.Close
MyConncompany.Close
Set RScompany = Nothing
Set MyConncompany = Nothing
%>
<br>


<%end if%>


thank you in advance.
Jamie

 

[lobstah]

you need to add this:

module = Server.HTMLEncode(rscompany.Fields(&quot;dfee&quot.Value)

to your loop of displaying the records, cuz the way you have it now, you're only selecting where equal to the first record in rscompany, you don't loop thru the rscompany recordset for this selection:

sql = &quot;SELECT DISTINCT [RepGenA].[filename] FROM RepGenA WHERE SUBSTRING(RepGenA.filename,4,4) = '&quot;&module&&quot;' &quot;


I also think that you can combine this into one select statement:

sql = &quot;SELECT DISTINCT [RepGenA].[filename], EstablishmentID, DfEE, EstablishmentName FROM RepGenA, Establishments WHERE SUBSTRING(RepGenA.filename,4,4) = DfEE&quot;

Then you don't need to loop thru 2 recordsets.....

 

[welshone]

thanks very much,
but where do I add :
module = Server.HTMLEncode(rscompany.Fields(&quot;dfee&quot.Value) ?

 

[lobstah]

I think this should do it, might be some minor errors in there, but should at least get you on your way....


<%
Dim SearchTextcompany, MyConncompany, SQLcompany, RScompany

Set MyConncompany=Server.CreateObject(&quot;ADODB.Connection&quot
MyConncompany.Open &quot;schoolsasp&quot;,&quot;schoolsasp&quot;,&quot;schoolsasp&quot;

SQLcompany = &quot;SELECT EstablishmentID, DfEE, EstablishmentName From Establishments ORDER BY EstablishmentName&quot;

Set RScompany = MyConncompany.Execute(SQLcompany)
%>

<%
Dim module
Dim sError

Param = Request.QueryString(&quot;Param&quot
Data = Request.QueryString(&quot;Data&quot
%>
<center>

<TABLE BORDER=1 BGCOLOR=#ffffff CELLSPACING=1 CELLPADDING=3><FONT FACE=&quot;Arial&quot; COLOR=#000000>

<THEAD>
<TR>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=white>Establishment</FONT></TH>
<TH BGCOLOR=#008080 BORDERCOLOR=#000000 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=white>Filename</FONT></TH>

</TR>
</THEAD>
<TBODY>

<%
Set conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open &quot;Holding&quot;,&quot;edholding&quot;,&quot;edholding&quot;
Set Session(&quot;Holding_conn&quot = conn

do while Not rscompany.eof

module = Server.HTMLEncode(rscompany.Fields(&quot;dfee&quot.Value)

sql = &quot;SELECT DISTINCT [RepGenA].[filename] FROM RepGenA WHERE SUBSTRING(RepGenA.filename,4,4) = '&quot;&module&&quot;' &quot;

If cstr(Param) <> &quot;&quot; And cstr(Data) <> &quot;&quot; Then
sql = sql & &quot; And [&quot; & cstr(Param) & &quot;] = &quot; & cstr(Data)
End If
Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot
rs.Open sql, conn, 3, 3

if rs.eof then
sError = &quot;No Files Uploaded.&quot;
else
sError = &quot;&quot; ' clear error
RS.movefirst
do while Not rs.eof
%>

<TR VALIGN=TOP onMouseover=&quot;changeto('yellow')&quot;
onMouseout=&quot;changeback('white')&quot;>

<TD BORDERCOLOR=#c0c0c0 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=#000000><a href=&quot;./viewschool.asp?Establishment=<%=Server.HTMLEncode(rscompany.Fields(&quot;EstablishmentID&quot.Value)%>&quot;><%=Server.HTMLEncode(rscompany.Fields(&quot;DfEE&quot.Value)%>, <%=Server.HTMLEncode(rscompany.Fields(&quot;establishmentname&quot.Value)%></A><BR></FONT></TD>

<TD BORDERCOLOR=#c0c0c0 nowrap><FONT SIZE=2 FACE=&quot;Arial&quot; COLOR=#000000><a href=&quot;./moredetails.asp?Filename=<%=Server.HTMLEncode(rs.Fields(&quot;filename&quot.Value)%>&quot;><%=Server.HTMLEncode(rs.Fields(&quot;filename&quot.Value)%></a><BR></FONT></TD>

</TR>
<%
rs.MoveNext
loop
rscompany.MoveNext
loop%>
</TBODY>
<TFOOT></TFOOT>
</TABLE>

</BODY>


<% RS.Close
Conn.Close
set RS = Nothing
set Conn = Nothing
%>

<%
RScompany.Close
MyConncompany.Close
Set RScompany = Nothing
Set MyConncompany = Nothing
%>

 

[welshone]

Thank you, however I am getting the following error :

Microsoft VBScript compilation error '800a040e'

'loop' without 'do'

/HoldingDBv2/searchresultsschool.asp, line 359

loop
^

its talking about the secong loop :

rscompany.MoveNext
loop%>

 

[lobstah]

loop
end if
rs.close
rscompany.MoveNext
loop%>

I think that should fix it.

 

[welshone]

Excellent thats worked, but it is very slow.
any suggestions for speeding it up ?

thank you for all your help.