Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    1

    Unanswered: Working with data from 2 Databases

    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

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •