p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

proasp_howto thread: Comparing Data results from both Oracle and MSSQL


Message #1 by "Ruben Moreno" <ruben.moreno@y...> on Mon, 13 Aug 2001 20:29:07
I have a programmed a page that give me results from a both an Oracle DB 
as well as a MSSQL DB and I need to figure out how I can match up the 
result via the first record in each record set. And then display them in 
in a table. I'll go ahead and attach the code I have so far. If anyone can 
help me with this, I'd truly appreciate it.




<%
StrDate=request.querystring("Strdate")
if StrDate = "" then
	response.end
end if

dim inatconn, rs, sqlquery, msrs, mssqlquery, msinatconn, SdATA

Set INatconn = Server.CreateObject("ADODB.Connection") 
INatconn.Open "UID=forte;PWD=forte;driver={Microsoft ODBC for 
Oracle};SERVER=Phxprd;"
SQLQuery = "select o.Orderkey OrderNumber, count(*) Lines from orders o, 
orderdetail od where o.orderkey=od.orderkey and trunc(o.deliverydate)='" & 
StrDate & "' group by o.orderkey order by o.orderkey"
set rs = INatconn.Execute(SQLQuery)


Set msINatconn = Server.CreateObject("ADODB.Connection") 
msINatconn.Open "UID=sa;PWD=;driver={SQL 
Server};SERVER=smdb;database=foodchain;"
msSQLQuery = "select orderid OrderNumber, count(*) Lines from 
smorderheader, smorderline where Dateadd( day, 0, CONVERT( varchar, 
deliverydate,112 ) )='" & StrDate & "' and 
smorderheader.orderid=smorderline.orderheaderid group by orderid order by 
orderid"
set msrs = msINatconn.Execute(msSQLQuery)

%>

<html>
<body>
<table BORDER="1" align="center">
<tr>
<td>EXE OrderNumber</td>
<td>SM OrderNumber</td>
<td>EXE Lines</td>
<td>SM Lines</td>
</tr>

<%
' Move to the first record
rs.movefirst
msrs.movefirst

' Move while msrs still has data
do while not msrs.eof

' Check that Rs still has records if not then Write no data esle make it 
equal to result set
IF RS.EOF THEN
	SData = "No Data"
	SData1 = "<FONT COLOR='RED'><B>No Data</B></FONT>"
ELSE
   SData = RS(0)
   SData1 = RS(1)
END IF
	
%>

<tr>
<td bgcolor="#FFFFCC">
<a href = 'http://vos1intranet/orders/Order_detail.asp?StrOrderkey=<%= 
SData %>'>
<%= SData %>
</a>
</td>
<td bgcolor="#CCFFCC">
<a href = 'http://vos1intranet/orders/Order_detail_sm.asp?StrOrderkey=<%= 
msrs(0) %>'>
<%= msrs(0) %>
</a>
</td>

<td bgcolor="#FFFFCC">
<%= SData1 %>
</td>

<td bgcolor="#CCFFCC">
<%= msrs(1) %>
</td>
</tr>

<%

' Move to the next record
IF NOT rs.EOF THEN
rs.movenext
END IF
msrs.movenext

' Loop back to the do statement
loop 

%>
</table>

</body>
</html>

<%
rs.close
msrs.close
set rs=nothing
set msrs=nothing
%> 

  Return to Index