How to retrieve data from 2 databases??

Discussion in 'Classic ASP' started by Nov, Jul 8, 2004.

Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.
  1. Nov

    Nov

    Hi

    I want to retrieve data from 2 databases.

    Below is the code I used to retrieve data from 1 database. Can anyone tell me how to change the code so that i can retrieve data from 2 databases??

    <html>

    <%
    Set MyConn=Server.CreateObject("ADODB.Connection")
    MyConn.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\ok.dsn"
    Set RS=MyConn.Execute("SELECT * FROM Customers")
    %>
    <table>
    <tr>
    <% for i=0 to RS.Fields.Count - 1 %>
    <th> <%= RS(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS.EOF %>
    <TR>
    <% FOR i=0 to RS.Fields.Count -1%>
    <td> <%=RS(i) %></td>
    <% next %>
    </tr>
    <%
    RS.MoveNext
    WEND
    RS.Close
    MyConn.Close
    %>
    </table>
    </body>
    </html></font id="blue">

    I tried out a way; that is just copy and paste(as below) above code for another database but I felt this way lousy coding. Is there any better way to do that??
    Thanks...

    <html>

    <%
    Set MyConn=Server.CreateObject("ADODB.Connection")
    Set MyCon=Server.CreateObject("ADODB.Connection")
    MyConn.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\ok.dsn"
    MyCon.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\okk.dsn"
    Set RS=MyConn.Execute("SELECT * FROM Customers")
    Set RS2=MyCon.Execute("SELECT * FROM Jobs")
    %>
    <table>
    <tr>
    <% for i=0 to RS.Fields.Count - 1 %>
    <th> <%= RS(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS.EOF %>
    <TR>
    <% FOR i=0 to RS.Fields.Count -1%>
    <td> <%=RS(i) %></td>
    <% next %>
    </tr>
    <%
    RS.MoveNext
    WEND
    RS.Close
    MyConn.Close
    %>
    <% for i=0 to RS2.Fields.Count - 1 %>
    <th> <%= RS2(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS2.EOF %>
    <TR>
    <% FOR i=0 to RS2.Fields.Count -1%>
    <td> <%=RS2(i) %></td>
    <% next %>
    </tr>
    <%
    RS2.MoveNext
    WEND
    RS2.Close
    MyCon.Close
    %>

    </table>
    </body>
    </html></font id="green">
     
    Nov, Jul 8, 2004
    #1
  2. Bruce

    Bruce DiscountASP.NET Staff

    i think that's pretty much what you need to do.

    You need to open 2 seperate database connections if you are connecting to 2 DBs.


    quote:Originally posted by Nov

    Hi

    I want to retrieve data from 2 databases.

    Below is the code I used to retrieve data from 1 database. Can anyone tell me how to change the code so that i can retrieve data from 2 databases??

    <html>

    <%
    Set MyConn=Server.CreateObject("ADODB.Connection")
    MyConn.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\ok.dsn"
    Set RS=MyConn.Execute("SELECT * FROM Customers")
    %>
    <table>
    <tr>
    <% for i=0 to RS.Fields.Count - 1 %>
    <th> <%= RS(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS.EOF %>
    <TR>
    <% FOR i=0 to RS.Fields.Count -1%>
    <td> <%=RS(i) %></td>
    <% next %>
    </tr>
    <%
    RS.MoveNext
    WEND
    RS.Close
    MyConn.Close
    %>
    </table>
    </body>
    </html></font id="blue">

    I tried out a way; that is just copy and paste(as below) above code for another database but I felt this way lousy coding. Is there any better way to do that??
    Thanks...

    <html>

    <%
    Set MyConn=Server.CreateObject("ADODB.Connection")
    Set MyCon=Server.CreateObject("ADODB.Connection")
    MyConn.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\ok.dsn"
    MyCon.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\okk.dsn"
    Set RS=MyConn.Execute("SELECT * FROM Customers")
    Set RS2=MyCon.Execute("SELECT * FROM Jobs")
    %>
    <table>
    <tr>
    <% for i=0 to RS.Fields.Count - 1 %>
    <th> <%= RS(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS.EOF %>
    <TR>
    <% FOR i=0 to RS.Fields.Count -1%>
    <td> <%=RS(i) %></td>
    <% next %>
    </tr>
    <%
    RS.MoveNext
    WEND
    RS.Close
    MyConn.Close
    %>
    <% for i=0 to RS2.Fields.Count - 1 %>
    <th> <%= RS2(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS2.EOF %>
    <TR>
    <% FOR i=0 to RS2.Fields.Count -1%>
    <td> <%=RS2(i) %></td>
    <% next %>
    </tr>
    <%
    RS2.MoveNext
    WEND
    RS2.Close
    MyCon.Close
    %>

    </table>
    </body>
    </html></font id="green">
    </blockquote id="quote"></font id="quote">
     
    Bruce, Jul 8, 2004
    #2
  3. webwizard

    webwizard

    hi let tell a history, i cuban and i used two db to retrieve info, one using access and other one MS SQL 2k, just make two connections, or in case use two dsn in your server, if you'r runnig a workstation OS, just make de dsn in odbc setupo in control panel, i'd used the two ways, and works perfectly

    enjoy it


    quote:Originally posted by Nov

    Hi

    I want to retrieve data from 2 databases.

    Below is the code I used to retrieve data from 1 database. Can anyone tell me how to change the code so that i can retrieve data from 2 databases??

    <html>

    <%
    Set MyConn=Server.CreateObject("ADODB.Connection")
    MyConn.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\ok.dsn"
    Set RS=MyConn.Execute("SELECT * FROM Customers")
    %>
    <table>
    <tr>
    <% for i=0 to RS.Fields.Count - 1 %>
    <th> <%= RS(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS.EOF %>
    <TR>
    <% FOR i=0 to RS.Fields.Count -1%>
    <td> <%=RS(i) %></td>
    <% next %>
    </tr>
    <%
    RS.MoveNext
    WEND
    RS.Close
    MyConn.Close
    %>
    </table>
    </body>
    </html></font id="blue">

    I tried out a way; that is just copy and paste(as below) above code for another database but I felt this way lousy coding. Is there any better way to do that??
    Thanks...

    <html>

    <%
    Set MyConn=Server.CreateObject("ADODB.Connection")
    Set MyCon=Server.CreateObject("ADODB.Connection")
    MyConn.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\ok.dsn"
    MyCon.Open "FILEDSN=C:\Program Files\Common Files\ODBC\Data Sources\okk.dsn"
    Set RS=MyConn.Execute("SELECT * FROM Customers")
    Set RS2=MyCon.Execute("SELECT * FROM Jobs")
    %>
    <table>
    <tr>
    <% for i=0 to RS.Fields.Count - 1 %>
    <th> <%= RS(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS.EOF %>
    <TR>
    <% FOR i=0 to RS.Fields.Count -1%>
    <td> <%=RS(i) %></td>
    <% next %>
    </tr>
    <%
    RS.MoveNext
    WEND
    RS.Close
    MyConn.Close
    %>
    <% for i=0 to RS2.Fields.Count - 1 %>
    <th> <%= RS2(i).Name %> </th>
    <% NEXT %>
    </TR>
    <% While not RS2.EOF %>
    <TR>
    <% FOR i=0 to RS2.Fields.Count -1%>
    <td> <%=RS2(i) %></td>
    <% next %>
    </tr>
    <%
    RS2.MoveNext
    WEND
    RS2.Close
    MyCon.Close
    %>

    </table>
    </body>
    </html></font id="green">
    </blockquote id="quote"></font id="quote">

    WebWizard
    RealestateLatino.com
     
    webwizard, Aug 5, 2004
    #3
Thread Status:
Threads that have been inactive for 5 years or longer are closed to further replies. Please start a new thread.

Share This Page