Looping through 2 recordsets - part 2 - arrays ?

welshone · Jan 30, 2002

Hello,
earlier I asked for help regarding looping through 2 recordsets, lobstah came up with the following solution, which works, however this way is storing a connection object - an OPEN connection object at that - in a session variable!.
Does anybody have any other ideas ?
how would I run the first sql, put the results into an array, then close that connection, then open the second connection and run the second sql against the results in the array. ?

<%
Dim SearchTextcompany, MyConncompany, SQLcompany, RScompany

Set MyConncompany=Server.CreateObject("ADODB.Connection&quot

MyConncompany.Open "schoolsasp","schoolsasp","schoolsasp"

SQLcompany = "SELECT EstablishmentID, DfEE, EstablishmentName From Establishments ORDER BY EstablishmentName"

Set RScompany = MyConncompany.Execute(SQLcompany)
%>

<%
Dim module
Dim sError

Param = Request.QueryString("Param&quot

Data = Request.QueryString("Data&quot

%>

<center>

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

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

</TR>
</THEAD>
<TBODY>

<%

Set conn = Server.CreateObject("ADODB.Connection&quot

conn.open "Holding","edholding","edholding"
Set Session("Holding_conn&quot

= conn

do while Not rscompany.eof

module = Server.HTMLEncode(rscompany.Fields("dfee&quot

.Value)

spCommand.Parameters.Append spCommand.CreateParameter ("@module", 200, 1, 50, module)

sql= "files"

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

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

rs.Open sql, conn, 3, 3

if rs.eof then
sError = "No Files Uploaded."
else
SError="" 'clear error
RS.movefirst
do while Not rs.eof

%>

<TR VALIGN=TOP onMouseover="changeto('yellow')"
onMouseout="changeback('white')">

<TD BORDERCOLOR=#c0c0c0 nowrap><FONT SIZE=2 FACE="Arial" COLOR=#000000><a href="./viewschool.asp?Establishment=<%=Server.HTMLEncode(rscompany.Fields("EstablishmentID&quot

.Value)%>"><%=Server.HTMLEncode(rscompany.Fields("DfEE&quot

.Value)%>, <%=Server.HTMLEncode(rscompany.Fields("establishmentname&quot

.Value)%></A><BR></FONT></TD>

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

.Value)%>"><%=Server.HTMLEncode(rs.Fields("filename&quot

.Value)%></a><BR></FONT></TD>

</TR>

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

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

</BODY>

<%
conn.close
set RS = Nothing
set Conn = Nothing
%>

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

lobstah · Jan 30, 2002

Dim arrRows

Set MyConncompany=Server.CreateObject("ADODB.Connection&quot

MyConncompany.Open "schoolsasp","schoolsasp","schoolsasp"

SQLcompany = "SELECT EstablishmentID, DfEE, EstablishmentName From Establishments ORDER BY EstablishmentName"

RScompany.Open sSQL, MyConncompany, 0, 1
arrRows = RScompany.GetRows
RScompany.Close
Set RScompany = Nothing

' arrRows is now a 2 dimensional array, 2nd dimension being record # offset, 1st dimension being field # offset:

arrRows(0,0) ' Record 1, field 1
arrRows(1,0) ' Record 1, field 2
arrRows(2,0) ' Record 1, field 3
arrRows(3,0) ' Record 1, field 4
arrRows(4,0) ' Record 1, field 5
arrRows(0,1) ' Record 2, field 1
arrRows(1,1) ' Record 2, field 2
arrRows(2,1) ' Record 2, field 3
arrRows(3,1) ' Record 2, field 4
arrRows(4,1) ' Record 2, field 5

etc.
and you can loop thru it like this:
(example)
Dim r, f
For r = 0 To UBound(arrRows,2) ' loop thru records
For f = 0 To UBound(arrRows,1) ' loop thru fields
Response.Write "<td>" & arrRows(f,r) & "</td>"
Next
Next

welshone · Jan 30, 2002

thats great lobstah, but how do I then use the results from this recordset in the 2nd recordset ?

lobstah · Jan 30, 2002

instead of your
do while not recordset.eof
.....
loop

use the

dim i, EstablishmentID, DfEE, EstablishmentName
for i=0 to UBound(arrRows,2)
EstablishmentID = arrRows(0,i)
DfEE = arrRows(1,i)
EstablishmentName = arrRows(2,i)
'then add your 2nd recordset code in here.......

next

is that enough to get you going?

welshone · Jan 30, 2002

I think so,
however I get object required message, relating to this line :

RScompany.Open SQLCompany, MyConncompany, 0, 1

lobstah · Jan 30, 2002

try using what you had originally in place....

Set RScompany = MyConncompany.Execute(SQLcompany)

welshone · Jan 30, 2002

Yeah, that seemed to work..

I think this is the last question, then Hopefully it will work,.
I cna't use this anymore :
<TD BORDERCOLOR=#c0c0c0 nowrap><FONT SIZE=2 FACE="Arial" COLOR=#000000><a href="./viewschool.asp?Establishment=<%=Server.HTMLEncode(rscompany.Fields("EstablishmentID&quot

.Value)%>"><%=Server.HTMLEncode(rscompany.Fields("DfEE&quot

.Value)%>, <%=Server.HTMLEncode(rscompany.Fields("establishmentname&quot

.Value)%></A><BR></FONT></TD>

what should I use instead of Server.HTMLEncode ?

lobstah · Jan 30, 2002

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

that should work. you can still use Server.HTMLEncode...

welshone · Jan 30, 2002

Sorry lobstah,
it is still so slow.
I have tried to change it so both queries are created into an array, meaning the server isn't put under too much processing., however I get the following message :

ADODB.Recordset error '800a0e7d'
The connection cannot be used to perform this operation. It is either closed or invalid in this context.

/HoldingDBv2/searchresultsschool.asp, line 345

however it does show the first record....

my code now looks like :

<%
Dim SearchTextcompany, MyConncompany, SQLcompany, RScompany

Dim arrRows

Set MyConncompany=Server.CreateObject("ADODB.Connection&quot

MyConncompany.Open "schoolsasp","schoolsasp","schoolsasp"

SQLcompany = "SELECT EstablishmentID, DfEE, EstablishmentName From Establishments ORDER BY EstablishmentName"

Set RScompany = MyConncompany.Execute(SQLcompany)

arrRows = RScompany.GetRows

RScompany.Close
Set RScompany = Nothing
MyConncompany.Close
Set MyConncompany = Nothing

%>

<%
Dim module
Dim sError

Param = Request.QueryString("Param&quot

Data = Request.QueryString("Data&quot

%>

<center>

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

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

</TR>
</THEAD>
<TBODY>

<%

Set conn = Server.CreateObject("ADODB.Connection&quot

conn.open "Holding","edholding","edholding"
Set Session("Holding_conn&quot

= conn

dim i, EstablishmentID, DfEE, EstablishmentName
for i=0 to UBound(arrRows,2)
EstablishmentID = arrRows(0,i)
DfEE = arrRows(1,i)
EstablishmentName = arrRows(2,i)

'module = Server.HTMLEncode(rscompany.Fields("dfee&quot

.Value)

sql = "SELECT DISTINCT [RepGenA].[filename] FROM RepGenA WHERE SUBSTRING(RepGenA.filename,4,4) = '"&DfEE&"' "

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

rs.Open sql, conn, 3, 3

arrRows1 = RS.GetRows

RS.Close
Set RS = Nothing
Conn.Close
Set Conn = Nothing

dim j, Filename
for j=0 to UBound(arrRows1,2)
Filename = arrRows1(0,j)

' if rs.eof then
'sError = "No Files Uploaded."
'else
' SError="" 'clear error
' RS.movefirst
'do while Not rs.eof

%>

<TR VALIGN=TOP onMouseover="changeto('yellow')"
onMouseout="changeback('white')">

<TD BORDERCOLOR=#c0c0c0 nowrap><FONT SIZE=2 FACE="Arial" COLOR=#000000><a href="./viewschool.asp?Establishment=<%=Server.HTMLEncode(EstablishmentID)%>"><%=Server.HTMLEncode(DfEE)%></a></FONT></TD>

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

</TR>

<%

next
'end if

next
%>

lobstah · Jan 30, 2002

If speed is an issue, then I'll repeat what I mentioned in your first post on this issue:

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

Code:

sql = &quot;SELECT DISTINCT [RepGenA].[filename], EstablishmentID, DfEE, EstablishmentName FROM RepGenA, Establishments WHERE SUBSTRING(RepGenA.filename,4,4) = DfEE&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

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

I really think that would be your best solution, and cleanest in every sense.

welshone · Jan 30, 2002

The thing is its 2 databases, so I need 2 connections.

lobstah · Jan 30, 2002

oh, yeah. sorry about that......

speed issues:
are they large databases/tables? are your queries returning a large amount of data? the server(s) that the databases reside on, are they overly busy?

welshone · Jan 30, 2002

one DB - ois quite small, returning about 400 records.
the other database returns about 500,000 records

lobstah · Jan 30, 2002

what kind of db? (ms access, sql server)?

welshone · Jan 31, 2002

MS SQL version 7

lobstah · Jan 31, 2002

i'm not real good with sql server, but maybe you can create stored procedures and run those, try posting on the sql server forum to get more info on that. i think that will greatly improve speed and streamline your code too.

welshone · Jan 31, 2002

ok Lobstah, thank you for all your help.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Looping through 2 recordsets - part 2 - arrays ?

welshone

Programmer

lobstah

Programmer

welshone

Programmer

lobstah

Programmer

welshone

Programmer

lobstah

Programmer

welshone

Programmer

lobstah

Programmer

welshone

Programmer

lobstah

Programmer

welshone

Programmer

lobstah

Programmer

welshone

Programmer

lobstah

Programmer

welshone

Programmer

lobstah

Programmer

welshone

Programmer

Similar threads

Part and Inventory Search

Sponsor