04-30-04, 11:36 #1Registered User
- Join Date
- Apr 2004
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..
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..
Dim Query, Connect, dbvar, dbvar2, coninfo2, connect2, query2, coninfo, sql, sql2
Set Connect2 = Server.CreateObject("ADODB.Connection")
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">
<%DO until coninfo2.EOF %>
Set Connect = Server.CreateObject("ADODB.Connection")
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.close %>
04-30-04, 16:51 #2Useless...
- Join Date
- Jul 2003
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 andThat which does not kill me postpones the inevitable.