How to retrieve data from 2 databases?? [Archive]

View Full Version : How to retrieve data from 2 databases??

Nov

07-08-2004, 09:41 AM

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>
[b]
<%
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>
[b]
<%
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">

bruce

07-08-2004, 10:41 AM

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.

[b]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>
[b]
<%
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>
[b]
<%
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

08-05-2004, 03:33 AM

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

[b]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>
[b]
<%
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>
[b]
<%
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