Hi, bit of a newbie here, but done a bit of asp.
Got a problem working with data from 2 seperate ODBC sources in ASP, I can extract the information but I just dont seem able to pull it together and work with it.

Table 1 ODBC MS Access table
Stkcode qty date empid

Table 2 C/ODBC (Navision)
Item table, loads of fields interested in..
No. Description


Table 1 lists the transactions, Table 2 lists the info I want to look up.
The results I am after should look like...

Stkcode qty date Description empid
A001 3 01/01 Widget 1 1
A002 1 01/01 Widget 2 1
A001 1 01/01 Widget 1 2

Looks simple, but im pulling my hair out looking for the answer, atm I have tried lots of things and need some help, Example code..


<code>
<%
Dim Query, Connect, dbvar, dbvar2, coninfo2, connect2, query2, coninfo, sql, sql2
%>
<%

'server.scripttimeout=100000000

Set Connect2 = Server.CreateObject("ADODB.Connection")
Connect2.Open "Pepe"

Set Coninfo2 = Server.Createobject("ADODB.RecordSet")

sql2 = "SELECT * FROM transaction"

Coninfo2.Open sql2, Connect2, adopendynamic, adlockoptimistic


%>
<h3>Listing of all items in Bins</h3>
<table border ="1">
<tr>
<th>Part Ref</th>
<th>Quantity</th>
<th>Bin Name</th>
<th>Description</th>
<%DO until coninfo2.EOF %>

<tr>
<td>
<% =coninfo2("stkcode")%>
</td>
<td>
<% =coninfo2("qty")%>
</td>
<td>
<% =coninfo2("empid")%>
</td>
<td>

<%

Set Connect = Server.CreateObject("ADODB.Connection")
Connect.Open "navisionstk"

set coninfo = server.createobject("ADODB.Recordset")

sql = "SELECT * FROM Item "

sql = sql &" WHERE (Item.Description LIKE '"& (coninfo2("Description")) &"') "

coninfo.Open sql, connect, adopendynamic, adlockoptimistic
%>
<% =coninfo("Description")%>

<% coninfo.close %>

</td>
</tr>
<%coninfo2.MoveNext
LOOP%>
</table>

</code>

Thanks

First, create and open both your connections and create the recordsets objects outside of the DO loop. You don't need to open and close them repeatedly, just adds overhead.

Instead, open both conns, execute the first, start looping through the RS, then inside each iteration, execute SQL against the second conn and read from that RS, then each loop thereafter will only execute the SQL for the second RS. Then when you exit the loop, make sure to close and destroy both conns and