Access and Oracle connection on the same page. Strange Errors

[Dave Pope]

Greetings.

I am trying to connect to both an oracle db and an access db.
I know this is possible but my problem I think stems from the fact
that I am using a recordset object as a parameter for my access query.

Ex.
\\adodb connections\\
set rs = "oracle connection"
do while not rs.eof

set rs1 = "access connection where something = rs("whatever")

return rs results
do while not rs1.eof
return rs1 results
rs1.movenext
loop
rs1.close
rs.movenext
loop
rs.close

I do this and the script chugs along although slowly, I get some
results but then I get either a script timeout problem or an
"unspecified error" with my access connection.

Am I doing something wrong or is this just a limitation on the Access
DB?
If its a limitation anyone have any pointers on extracting data from
access and storing it in oracle on a recurring basis?

Thanks in advance
Dave

[Ray at]

"Dave Pope" <dlpope@hotmail.com> wrote in message
news:e4d1f293.0310020710.4ae44fbd@posting.google.c om...

> Greetings.
>
> I am trying to connect to both an oracle db and an access db.
> I know this is possible but my problem I think stems from the fact
> that I am using a recordset object as a parameter for my access query.
>
> Ex.
> \\adodb connections\\

What is that? Is that supposed to be a UNC path?

> set rs = "oracle connection"

What's that?

> do while not rs.eof
>
> set rs1 = "access connection where something = rs("whatever")

Are you SETting rs1 to be a string? If you want it to be a string, you
don't use SET. If you're trying to set it to be a recordset, you'd have to
execute a query and have it return the recordset. Also, that string isn't
valid:
"access connection where something=" & rs("whatever")
That would be a little less invalid than what you have above.

What's your real code?


Ray at work

[Dave Pope]

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message news:<uQY0VCQiDHA.2960@TK2MSFTNGP11.phx.gbl>...

> "Dave Pope" <dlpope@hotmail.com> wrote in message
> news:e4d1f293.0310020710.4ae44fbd@posting.google.c om...

> > Greetings.
> >
> > I am trying to connect to both an oracle db and an access db.
> > I know this is possible but my problem I think stems from the fact
> > that I am using a recordset object as a parameter for my access query.
> >
> > Ex.
> > \\adodb connections\\

>
> What is that? Is that supposed to be a UNC path?
>

No I was just saying that I am using adodb connections, thought it
might be important

> > set rs = "oracle connection"

> What's that?

Executing an oracle sql statement and returing a recordset

>

> > do while not rs.eof
> >
> > set rs1 = "access connection where something = rs("whatever")

>
> Are you SETting rs1 to be a string? If you want it to be a string, you
> don't use SET. If you're trying to set it to be a recordset, you'd have to
> execute a query and have it return the recordset. Also, that string isn't
> valid:
> "access connection where something=" & rs("whatever")
> That would be a little less invalid than what you have above.
>
> What's your real code?
>
>
> Ray at work

Sorry not supposed to be valid. I was just dealing in theoreticals.
Like does using a returned value from one query in another query cause
access2000 to crap out.

Here's the important stuff:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "my dsn connection"

sqlstring="select date_completed,wip_entity_name,scheduled_start_dat e,start_quantity,date_released"
sqlstring=sqlstring&" ,nvl(quantity_completed,0) quantity_completed,
wip_entity_id"
sqlstring=sqlstring&" ,nvl(quantity_remaining,0)
quantity_remaining,nvl(quantity_scrapped,0) quantity_scrapped "
sqlstring=sqlstring&" from wip_discrete_jobs_v where date_completed >
'"&schedule_date&"' "


Set rs=objConn.Execute(sqlstring)
do while not rs.eof
discrete_job=rs("wip_entity_name")
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:myaccessdb.mdb"



Set myConnection = Server.CreateObject("ADODB.Connection")
Set rs1 = Server.CreateObject("ADODB.Recordset")

myConnection.Open connectString
sqlstring1="SELECT FKParentID from tagvalue "
sqlstring1=sqlstring1&"where datavalue='"&discrete_job&"' "


Set RS1 = myConnection.Execute(sqlString1)


%>
<tr><td align="right"><font face="Arial"
size="1"><%=rs("wip_entity_name")%></font>&nbsp;</td>
<td align="right"><font face="Arial"
size="1"><%=formatdatetime(rs("scheduled_start_dat e"),2)%>&nbsp;</td>
<td align="right"><font face="Arial" size="1">
<%if rs("date_released")<>"" then%>
<%=formatdatetime(rs("date_released"),2)%>
<%else%>
- Unreleased -
<%end if%>&nbsp;</td>
<td align="right"><font face="Arial"
size="1"><%=formatdatetime(rs("date_completed"),2) %>&nbsp;</td>
<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs("start_quantity"),0)%> &nbsp;</td>
<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs("quantity_completed"), 0)%>&nbsp;</td>
<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs("quantity_scrapped"),0 )%>&nbsp;</td>
<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs("quantity_remaining"), 0)%>&nbsp;</td>
<%do while not rs1.eof %>
<td align="right"><font face="Arial" size="1">
<%if rs1("fkparentid")<>"" then%>
<%=rs1("fkparentid")%>
<%else%>
- No SPC Data Found! -
<%end if%>&nbsp;</td>
<%
rs1.movenext
loop
rs1.close

%>
</tr>
<%
rs.movenext
loop
rs.close
%>

Any help greatly appreciated.

Dave

[Ray at]

Maybe your unspecified error is because of your connection string. Look at
the path to your database:

> connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data

Source=c:myaccessdb.mdb"

Ray at work

[Ray at]

Whoah, hold on here.


"Dave Pope" <dlpope@hotmail.com> wrote in message
news:e4d1f293.0310030503.1665a27f@posting.google.c om...

>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "my dsn connection"
>
> sqlstring="select

date_completed,wip_entity_name,scheduled_start_dat e,start_quantity,date_rele
ased"

> sqlstring=sqlstring&" ,nvl(quantity_completed,0) quantity_completed,
> wip_entity_id"
> sqlstring=sqlstring&" ,nvl(quantity_remaining,0)
> quantity_remaining,nvl(quantity_scrapped,0) quantity_scrapped "
> sqlstring=sqlstring&" from wip_discrete_jobs_v where date_completed >
> '"&schedule_date&"' "

fine

>
>
> Set rs=objConn.Execute(sqlstring)
> do while not rs.eof
> discrete_job=rs("wip_entity_name")
> connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=c:myaccessdb.mdb"
>
>
>
> Set myConnection = Server.CreateObject("ADODB.Connection")
> Set rs1 = Server.CreateObject("ADODB.Recordset")
>
> myConnection.Open connectString
> sqlstring1="SELECT FKParentID from tagvalue "
> sqlstring1=sqlstring1&"where datavalue='"&discrete_job&"' "
>
>
> Set RS1 = myConnection.Execute(sqlString1)

You are creating a new adodb.connection every time you loop through the
first recordset. Also, you're naming columns that won't be returned in your
recordset. When you do something like nvl(columnName), you either have to
name that result, or access it by its index value in the recordset. Of
course, this is assuming that nvl is some sort of Oracle function. I could
be wrong about this, and for all I know, Oracle will still return the column
name in the recordset. Try this code:





Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "my dsn connection"

sqlstring="select
date_completed,wip_entity_name,scheduled_start_dat e,start_quantity,date_rele
ased"
sqlstring=sqlstring & " ,nvl(quantity_completed,0)
quantity_completed,wip_entity_id"
'''there were two commas missing in the line below
sqlstring=sqlstring & "
,nvl(quantity_remaining,0),quantity_remaining,nvl( quantity_scrapped,0),
quantity_scrapped "
sqlstring=sqlstring & " from wip_discrete_jobs_v where date_completed '" &
schedule_date & "'"





Set rs=objConn.Execute(sqlstring)
If Not rs.EOF Then
connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=c:\myaccessdb.mdb"
Set myConnection = Server.CreateObject("ADODB.Connection")
myConnection.Open connectString

do while not rs.eof
discrete_job=rs.Fields.Item(1).Value




Set rs1 = myConnection.Execute("sqlstring1="SELECT FKParentID from tagvalue
where datavalue='" & discrete_job & "' "


%>
<tr><td align="right"><font face="Arial"
size="1"><%=rs.Fields.Item(1).Value%></font>&nbsp;</td>

<td align="right"><font face="Arial"
size="1"><%=formatdatetime(rs.Fields.Item(2).Value ,2)%>&nbsp;</td>

<td align="right"><font face="Arial" size="1">
<%if rs.Fields.Item(4).Value <> "" then%>
<%=formatdatetime(rs.Fields.Item(4).Value,2)%>
<%else%>
- Unreleased -
<%end if%>
&nbsp;
</td>

<td align="right"><font
face="Arial"size="1"><%=formatdatetime(rs.Fields.I tem(0).Value,2)%>&nbsp;</t
d>

<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs.Fields.Item(3).Value,0 )%>&nbsp;</td>

<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs.Fields.Item(6).Value,0 )%>&nbsp;</td>

<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs.Fields.Item(11).Value, 0)%>&nbsp;</td>

<td align="right"><font face="Arial"
size="1"><%=formatnumber(rs.Fields.Item(9).Value,0 )%>&nbsp;</td>

<%do while not rs1.eof %>
<td align="right"><font face="Arial" size="1">
<%if rs1.Fields.Item(0).Value <> "" then%>
<%=rs1.Fields.Item(0).Value%>
<%else%>
- No SPC Data Found! -
<%end if%>
&nbsp;
</td>
<%
rs1.movenext
loop
rs1.close

%>
</tr>
<%
rs.movenext
loop
rs.close
%>


Also, you may want to consider assigning all the recordset values to
variables as you loop through. That can help you in debugging and also make
your page more efficient.

Ray at work

[Dave Pope]

Ray Thank you for your help.
I tried your script and am no longer getting the "unspecified error"
but I still get the script timeout error. So I set the timeout higher.
Although its at about 3 minutes now which is way too long to wait for
a page to load anyway.
Not sure why this is. Direct to oracle is fast, Direct to Access is
fast, but the loop really kills it. Dont have any clue where to go
from here. I think its back to the drawing board.

Dave

"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message news:<ewSgZxciDHA.1820@TK2MSFTNGP09.phx.gbl>...

> Whoah, hold on here.
>
>
> "Dave Pope" <dlpope@hotmail.com> wrote in message
> news:e4d1f293.0310030503.1665a27f@posting.google.c om...

> >
> > Set objConn = Server.CreateObject("ADODB.Connection")
> > objConn.Open "my dsn connection"
> >
> > sqlstring="select

> date_completed,wip_entity_name,scheduled_start_dat e,start_quantity,date_rele
> ased"

> > sqlstring=sqlstring&" ,nvl(quantity_completed,0) quantity_completed,
> > wip_entity_id"
> > sqlstring=sqlstring&" ,nvl(quantity_remaining,0)
> > quantity_remaining,nvl(quantity_scrapped,0) quantity_scrapped "
> > sqlstring=sqlstring&" from wip_discrete_jobs_v where date_completed >
> > '"&schedule_date&"' "

>
> fine
>

> >
> >
> > Set rs=objConn.Execute(sqlstring)
> > do while not rs.eof
> > discrete_job=rs("wip_entity_name")
> > connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> > Source=c:myaccessdb.mdb"
> >
> >
> >
> > Set myConnection = Server.CreateObject("ADODB.Connection")
> > Set rs1 = Server.CreateObject("ADODB.Recordset")
> >
> > myConnection.Open connectString
> > sqlstring1="SELECT FKParentID from tagvalue "
> > sqlstring1=sqlstring1&"where datavalue='"&discrete_job&"' "
> >
> >
> > Set RS1 = myConnection.Execute(sqlString1)

>
> You are creating a new adodb.connection every time you loop through the
> first recordset. Also, you're naming columns that won't be returned in your
> recordset. When you do something like nvl(columnName), you either have to
> name that result, or access it by its index value in the recordset. Of
> course, this is assuming that nvl is some sort of Oracle function. I could
> be wrong about this, and for all I know, Oracle will still return the column
> name in the recordset. Try this code:
>
>
>
>
>
> Set objConn = Server.CreateObject("ADODB.Connection")
> objConn.Open "my dsn connection"
>
> sqlstring="select
> date_completed,wip_entity_name,scheduled_start_dat e,start_quantity,date_rele
> ased"
> sqlstring=sqlstring & " ,nvl(quantity_completed,0)
> quantity_completed,wip_entity_id"
> '''there were two commas missing in the line below
> sqlstring=sqlstring & "
> ,nvl(quantity_remaining,0),quantity_remaining,nvl( quantity_scrapped,0),
> quantity_scrapped "
> sqlstring=sqlstring & " from wip_discrete_jobs_v where date_completed '" &
> schedule_date & "'"
>
>
>
>
>
> Set rs=objConn.Execute(sqlstring)
> If Not rs.EOF Then
> connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=c:\myaccessdb.mdb"
> Set myConnection = Server.CreateObject("ADODB.Connection")
> myConnection.Open connectString
>
> do while not rs.eof
> discrete_job=rs.Fields.Item(1).Value
>
>
>
>
> Set rs1 = myConnection.Execute("sqlstring1="SELECT FKParentID from tagvalue
> where datavalue='" & discrete_job & "' "
>
>
> %>
> <tr><td align="right"><font face="Arial"
> size="1"><%=rs.Fields.Item(1).Value%></font>&nbsp;</td>
>
> <td align="right"><font face="Arial"
> size="1"><%=formatdatetime(rs.Fields.Item(2).Value ,2)%>&nbsp;</td>
>
> <td align="right"><font face="Arial" size="1">
> <%if rs.Fields.Item(4).Value <> "" then%>
> <%=formatdatetime(rs.Fields.Item(4).Value,2)%>
> <%else%>
> - Unreleased -
> <%end if%>
> &nbsp;
> </td>
>
> <td align="right"><font
> face="Arial"size="1"><%=formatdatetime(rs.Fields.I tem(0).Value,2)%>&nbsp;</t
> d>
>
> <td align="right"><font face="Arial"
> size="1"><%=formatnumber(rs.Fields.Item(3).Value,0 )%>&nbsp;</td>
>
> <td align="right"><font face="Arial"
> size="1"><%=formatnumber(rs.Fields.Item(6).Value,0 )%>&nbsp;</td>
>
> <td align="right"><font face="Arial"
> size="1"><%=formatnumber(rs.Fields.Item(11).Value, 0)%>&nbsp;</td>
>
> <td align="right"><font face="Arial"
> size="1"><%=formatnumber(rs.Fields.Item(9).Value,0 )%>&nbsp;</td>
>
> <%do while not rs1.eof %>
> <td align="right"><font face="Arial" size="1">
> <%if rs1.Fields.Item(0).Value <> "" then%>
> <%=rs1.Fields.Item(0).Value%>
> <%else%>
> - No SPC Data Found! -
> <%end if%>
> &nbsp;
> </td>
> <%
> rs1.movenext
> loop
> rs1.close
>
> %>
> </tr>
> <%
> rs.movenext
> loop
> rs.close
> %>
>
>
> Also, you may want to consider assigning all the recordset values to
> variables as you loop through. That can help you in debugging and also make
> your page more efficient.
>
> Ray at work

[Turkbear]

You may want to load the recordsets into arrays and then close out your connections to the databases;You can then loop
through the arrays without having any resources tied up in maintaining tyhe connection...

May help..


On 7 Oct 2003 13:55:50 -0700, [email]dlpope@hotmail.com[/email] (Dave Pope) wrote:

>Ray Thank you for your help.
>I tried your script and am no longer getting the "unspecified error"
>but I still get the script timeout error. So I set the timeout higher.
>Although its at about 3 minutes now which is way too long to wait for
>a page to load anyway.
>Not sure why this is. Direct to oracle is fast, Direct to Access is
>fast, but the loop really kills it. Dont have any clue where to go
>from here. I think its back to the drawing board.
>
>Dave
>
>"Ray at <%=sLocation%>" <myfirstname at lane34 dot com> wrote in message news:<ewSgZxciDHA.1820@TK2MSFTNGP09.phx.gbl>...

>> Whoah, hold on here.
>>
>>
>> "Dave Pope" <dlpope@hotmail.com> wrote in message
>> news:e4d1f293.0310030503.1665a27f@posting.google.c om...

>> >
>> > Set objConn = Server.CreateObject("ADODB.Connection")
>> > objConn.Open "my dsn connection"
>> >
>> > sqlstring="select

>> date_completed,wip_entity_name,scheduled_start_dat e,start_quantity,date_rele
>> ased"

>> > sqlstring=sqlstring&" ,nvl(quantity_completed,0) quantity_completed,
>> > wip_entity_id"
>> > sqlstring=sqlstring&" ,nvl(quantity_remaining,0)
>> > quantity_remaining,nvl(quantity_scrapped,0) quantity_scrapped "
>> > sqlstring=sqlstring&" from wip_discrete_jobs_v where date_completed >
>> > '"&schedule_date&"' "

>>
>> fine
>>

>> >
>> >
>> > Set rs=objConn.Execute(sqlstring)
>> > do while not rs.eof
>> > discrete_job=rs("wip_entity_name")
>> > connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>> > Source=c:myaccessdb.mdb"
>> >
>> >
>> >
>> > Set myConnection = Server.CreateObject("ADODB.Connection")
>> > Set rs1 = Server.CreateObject("ADODB.Recordset")
>> >
>> > myConnection.Open connectString
>> > sqlstring1="SELECT FKParentID from tagvalue "
>> > sqlstring1=sqlstring1&"where datavalue='"&discrete_job&"' "
>> >
>> >
>> > Set RS1 = myConnection.Execute(sqlString1)

>>
>> You are creating a new adodb.connection every time you loop through the
>> first recordset. Also, you're naming columns that won't be returned in your
>> recordset. When you do something like nvl(columnName), you either have to
>> name that result, or access it by its index value in the recordset. Of
>> course, this is assuming that nvl is some sort of Oracle function. I could
>> be wrong about this, and for all I know, Oracle will still return the column
>> name in the recordset. Try this code:
>>
>>
>>
>>
>>
>> Set objConn = Server.CreateObject("ADODB.Connection")
>> objConn.Open "my dsn connection"
>>
>> sqlstring="select
>> date_completed,wip_entity_name,scheduled_start_dat e,start_quantity,date_rele
>> ased"
>> sqlstring=sqlstring & " ,nvl(quantity_completed,0)
>> quantity_completed,wip_entity_id"
>> '''there were two commas missing in the line below
>> sqlstring=sqlstring & "
>> ,nvl(quantity_remaining,0),quantity_remaining,nvl( quantity_scrapped,0),
>> quantity_scrapped "
>> sqlstring=sqlstring & " from wip_discrete_jobs_v where date_completed '" &
>> schedule_date & "'"
>>
>>
>>
>>
>>
>> Set rs=objConn.Execute(sqlstring)
>> If Not rs.EOF Then
>> connectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
>> Source=c:\myaccessdb.mdb"
>> Set myConnection = Server.CreateObject("ADODB.Connection")
>> myConnection.Open connectString
>>
>> do while not rs.eof
>> discrete_job=rs.Fields.Item(1).Value
>>
>>
>>
>>
>> Set rs1 = myConnection.Execute("sqlstring1="SELECT FKParentID from tagvalue
>> where datavalue='" & discrete_job & "' "
>>
>>
>> %>
>> <tr><td align="right"><font face="Arial"
>> size="1"><%=rs.Fields.Item(1).Value%></font>&nbsp;</td>
>>
>> <td align="right"><font face="Arial"
>> size="1"><%=formatdatetime(rs.Fields.Item(2).Value ,2)%>&nbsp;</td>
>>
>> <td align="right"><font face="Arial" size="1">
>> <%if rs.Fields.Item(4).Value <> "" then%>
>> <%=formatdatetime(rs.Fields.Item(4).Value,2)%>
>> <%else%>
>> - Unreleased -
>> <%end if%>
>> &nbsp;
>> </td>
>>
>> <td align="right"><font
>> face="Arial"size="1"><%=formatdatetime(rs.Fields.I tem(0).Value,2)%>&nbsp;</t
>> d>
>>
>> <td align="right"><font face="Arial"
>> size="1"><%=formatnumber(rs.Fields.Item(3).Value,0 )%>&nbsp;</td>
>>
>> <td align="right"><font face="Arial"
>> size="1"><%=formatnumber(rs.Fields.Item(6).Value,0 )%>&nbsp;</td>
>>
>> <td align="right"><font face="Arial"
>> size="1"><%=formatnumber(rs.Fields.Item(11).Value, 0)%>&nbsp;</td>
>>
>> <td align="right"><font face="Arial"
>> size="1"><%=formatnumber(rs.Fields.Item(9).Value,0 )%>&nbsp;</td>
>>
>> <%do while not rs1.eof %>
>> <td align="right"><font face="Arial" size="1">
>> <%if rs1.Fields.Item(0).Value <> "" then%>
>> <%=rs1.Fields.Item(0).Value%>
>> <%else%>
>> - No SPC Data Found! -
>> <%end if%>
>> &nbsp;
>> </td>
>> <%
>> rs1.movenext
>> loop
>> rs1.close
>>
>> %>
>> </tr>
>> <%
>> rs.movenext
>> loop
>> rs.close
>> %>
>>
>>
>> Also, you may want to consider assigning all the recordset values to
>> variables as you loop through. That can help you in debugging and also make
>> your page more efficient.
>>
>> Ray at work