ASP Aggregate Function
 

I have been searching the boards trying to find an answer to this
question and no luck. I am using a query similar to this:

Select count(col1) from table1

I was having a hard time accessing the count information. After
reading for a while the following SQL examples were given to correct
this issue.

Select count(col1) Blah from table1
Select count(col1) As Blah from table1

Then, supposedly, I am able to access the data using the following:
rsQuery("Blah")
- or -
rsQuery(0)

Neither the rsQuery("Blah") or rsQuery(0) allows me to access the data.
I get the following error.

------------------------------
Provider error '80020009'

Multiple-step OLE DB operation generated errors. Check each OLE DB
status value, if available. No work was done.

/CallLeveraging/assets/forms/adhoc_popup.asp, line 0
-------------------------------------------

The database is Oracle 9 and IIS 6 is the webserver.

Anyone have any ideas at all? Thank you in advance!

Re: ASP Aggregate Function
 

"Aaron" <aaronandbree@gmail.com> wrote in message
news:1165959660.852629.77420@73g2000cwn.googlegrou ps.com...
>I have been searching the boards trying to find an answer to this
> question and no luck. I am using a query similar to this:
>
> Select count(col1) from table1
>
> I was having a hard time accessing the count information. After
> reading for a while the following SQL examples were given to correct
> this issue.
>
> Select count(col1) Blah from table1
> Select count(col1) As Blah from table1
>
> Then, supposedly, I am able to access the data using the following:
> rsQuery("Blah")
> - or -
> rsQuery(0)
>
> Neither the rsQuery("Blah") or rsQuery(0) allows me to access the data.
> I get the following error.

This seems like more of an Oracle issue than ASP, and I have no hands-on
with Oracle, but here are some things to try:

1.) Test the query syntax using an Oracle-provided tool. (Something
analogous to SQL Server's Query Analyzer.)
2.) Try explicitly specifying the Value property, rather than relying on the
default property.
The fully qualified prop name is rsQuery.Fields(0).Value. Some environments
(like JScript) assume you want a reference to the Field object unless you
specify the value property.
3.) Trap errors and enumerate the Connection.Errors object when any errors
occur, sometimes there are multiple messages, soime of which may be more
informative.
4.) Show us some code. You might be doing something dumb, like MoveFirst on
a forward-only recordset, or trying to assign a derived, or otherwise
inherently read-only field. That [ever-annoying] error is more typically
associated with recordset operations that change data. I can't recall ever
seeing it happen when reading a scalar value... if it does happen ever, it
doesn't happen often.


-Mark




> ------------------------------
> Provider error '80020009'
>
> Multiple-step OLE DB operation generated errors. Check each OLE DB
> status value, if available. No work was done.
>
> /CallLeveraging/assets/forms/adhoc_popup.asp, line 0
> -------------------------------------------
>
> The database is Oracle 9 and IIS 6 is the webserver.
>
> Anyone have any ideas at all? Thank you in advance!
>

Re: ASP Aggregate Function
 

Thank you Mark for your sugestions!

The query works perfect if I run directly against the Oracle Database
(Toad, Infomaker, etc)
The only time the error is received is when I try to access the value
of the aggregate function. If I run the query and don't attempt to
access the value I receive no error. Here is the code.

Thank you again for your time!

TABLE
=====
pts_status_type

status_type_id status_type_name
----------------------------------------------------
1 complete
2 active
3 pending
4 inactive


strSQL = "select count(*) as recCount "_
& "from calltrkapp.pts_status_type"

call openCon(strSQL, adOpenForwardOnly, adLockReadOnly)
response.Write("COUNT = " & rsQuery.Fields(0).Value & "<br><br>")
call closeCon()

----------------------------------------------------------------------------------------------------------
Includes
----------------------------------------------------------------------------------------------------------
<%
'DB Variables
Dim dcnDB 'As ADODB.Connection
Dim strSQL 'As string SQL Statement
Dim rsQuery
Dim strCon 'Connection String
'DEV
strCon = "PROVIDER=MSDAORA; Data Source=blah.world; User
ID=*****;Password=*****;"

'''''''''''''''''''''''''''''''''''''''''''''''
' sub openCon(strSQL, cursorType, LockType)
' subprocedure to open database connection with appropriate rights
' Read, Write, Etc
'''''''''''''''''''''''''''''''''''''''''''''''
sub openCon(strSQL, cursorType, LockType)
'ADODB Connection
Set dcnDB = Server.CreateObject("ADODB.Connection")
dcnDB.Open strCon

'ADODB.RecordSet
Set rsQuery = Server.CreateObject("ADODB.RecordSet")
'Response.Write("STRSQL = " & strSQL & "<br><br>")
rsQuery.Open strSQL, dcnDB, cursorType, LockType

end sub
%>

<%
'''''''''''''''''''''''''''''''''''''''''''''''
' sub CloseCon()
' subprocedure to close database connection
'''''''''''''''''''''''''''''''''''''''''''''''
sub closeCon()
set rsQuery = nothing
dcnDB.close
end sub
%>

Mark McGinty wrote:
> "Aaron" <aaronandbree@gmail.com> wrote in message
> news:1165959660.852629.77420@73g2000cwn.googlegrou ps.com...
> >I have been searching the boards trying to find an answer to this
> > question and no luck. I am using a query similar to this:
> >
> > Select count(col1) from table1
> >
> > I was having a hard time accessing the count information. After
> > reading for a while the following SQL examples were given to correct
> > this issue.
> >
> > Select count(col1) Blah from table1
> > Select count(col1) As Blah from table1
> >
> > Then, supposedly, I am able to access the data using the following:
> > rsQuery("Blah")
> > - or -
> > rsQuery(0)
> >
> > Neither the rsQuery("Blah") or rsQuery(0) allows me to access the data.
> > I get the following error.
>
> This seems like more of an Oracle issue than ASP, and I have no hands-on
> with Oracle, but here are some things to try:
>
> 1.) Test the query syntax using an Oracle-provided tool. (Something
> analogous to SQL Server's Query Analyzer.)
> 2.) Try explicitly specifying the Value property, rather than relying on the
> default property.
> The fully qualified prop name is rsQuery.Fields(0).Value. Some environments
> (like JScript) assume you want a reference to the Field object unless you
> specify the value property.
> 3.) Trap errors and enumerate the Connection.Errors object when any errors
> occur, sometimes there are multiple messages, soime of which may be more
> informative.
> 4.) Show us some code. You might be doing something dumb, like MoveFirst on
> a forward-only recordset, or trying to assign a derived, or otherwise
> inherently read-only field. That [ever-annoying] error is more typically
> associated with recordset operations that change data. I can't recall ever
> seeing it happen when reading a scalar value... if it does happen ever, it
> doesn't happen often.
>
>
> -Mark
>
>
>
>
> > ------------------------------
> > Provider error '80020009'
> >
> > Multiple-step OLE DB operation generated errors. Check each OLE DB
> > status value, if available. No work was done.
> >
> > /CallLeveraging/assets/forms/adhoc_popup.asp, line 0
> > -------------------------------------------
> >
> > The database is Oracle 9 and IIS 6 is the webserver.
> >
> > Anyone have any ideas at all? Thank you in advance!
> >

Re: ASP Aggregate Function
 

Strange... See additional code suggestions inline... (Note that the "air
code" below is untested.)

Maybe there are cursor/lock/option idiosyncrasies at play, you might want to
cut to the chase by letting the connection create a default recordset:, as
opposed to creating/opening it yourself

[open connection first...]
Set rsQuery = dcnDB.Execute(strSQL)

And if that doesn't work, and you *really* want to go deep sea fishing,
persist the recordset to XML and dump that to the response:

Set xml = CreateObject("MSXML2.DOMDocument")
rsQuery.Save xml, 1
Response.Write xml.xml

Why that might work when a simple field reference failed I can't even
imagine, but... sometimes when you drop a hook in deep water, there are
interesting things attached when you reel it in.


-Mark



"Aaron" <aaronandbree@gmail.com> wrote in message
news:1166192823.953442.192250@79g2000cws.googlegro ups.com...
> Thank you Mark for your sugestions!
>
> The query works perfect if I run directly against the Oracle Database
> (Toad, Infomaker, etc)
> The only time the error is received is when I try to access the value
> of the aggregate function. If I run the query and don't attempt to
> access the value I receive no error. Here is the code.
>
> Thank you again for your time!
>
> TABLE
> =====
> pts_status_type
>
> status_type_id status_type_name
> ----------------------------------------------------
> 1 complete
> 2 active
> 3 pending
> 4 inactive
>
>
> strSQL = "select count(*) as recCount "_
> & "from calltrkapp.pts_status_type"

On Error Resume Next

> call openCon(strSQL, adOpenForwardOnly, adLockReadOnly)

If rsQuery.State = 0 Then
Response.Write "recordset is closed"
Else
Response.Write "Fields.Count = " & rsQuery.Fields.Count & "<br>"
End If

> response.Write("COUNT = " & rsQuery.Fields(0).Value & "<br><br>")

If Err.Number <> 0 Then
Response.Write DumpConnectionErrors()
End If

> call closeCon()
>
> ----------------------------------------------------------------------------------------------------------
> Includes
> ----------------------------------------------------------------------------------------------------------
> <%
> 'DB Variables
> Dim dcnDB 'As ADODB.Connection
> Dim strSQL 'As string SQL Statement
> Dim rsQuery
> Dim strCon 'Connection String
> 'DEV
> strCon = "PROVIDER=MSDAORA; Data Source=blah.world; User
> ID=*****;Password=*****;"



Function DumpConnectionErrors()
If dcnDB.State = 0 then
DumpConnectionErrors = "(connection is closed)<br>"
Exit Function
End If

Dim i, buf
buf = ""
For i = 0 to dcnDB.Errors.Count - 1
buf = buf & CStr(i + 1) & ".) " & dcnDB.Errors(i).Description &
"<br>"
Next

DumpConnectionErrors = buf
End Function


> '''''''''''''''''''''''''''''''''''''''''''''''
> ' sub openCon(strSQL, cursorType, LockType)
> ' subprocedure to open database connection with appropriate rights
> ' Read, Write, Etc
> '''''''''''''''''''''''''''''''''''''''''''''''
> sub openCon(strSQL, cursorType, LockType)
> 'ADODB Connection
> Set dcnDB = Server.CreateObject("ADODB.Connection")
> dcnDB.Open strCon
>
> 'ADODB.RecordSet
> Set rsQuery = Server.CreateObject("ADODB.RecordSet")
> 'Response.Write("STRSQL = " & strSQL & "<br><br>")
> rsQuery.Open strSQL, dcnDB, cursorType, LockType
>
> end sub
> %>
>
> <%
> '''''''''''''''''''''''''''''''''''''''''''''''
> ' sub CloseCon()
> ' subprocedure to close database connection
> '''''''''''''''''''''''''''''''''''''''''''''''
> sub closeCon()
> set rsQuery = nothing
> dcnDB.close
> end sub
> %>
>
> Mark McGinty wrote:
>> "Aaron" <aaronandbree@gmail.com> wrote in message
>> news:1165959660.852629.77420@73g2000cwn.googlegrou ps.com...
>> >I have been searching the boards trying to find an answer to this
>> > question and no luck. I am using a query similar to this:
>> >
>> > Select count(col1) from table1
>> >
>> > I was having a hard time accessing the count information. After
>> > reading for a while the following SQL examples were given to correct
>> > this issue.
>> >
>> > Select count(col1) Blah from table1
>> > Select count(col1) As Blah from table1
>> >
>> > Then, supposedly, I am able to access the data using the following:
>> > rsQuery("Blah")
>> > - or -
>> > rsQuery(0)
>> >
>> > Neither the rsQuery("Blah") or rsQuery(0) allows me to access the data.
>> > I get the following error.
>>
>> This seems like more of an Oracle issue than ASP, and I have no hands-on
>> with Oracle, but here are some things to try:
>>
>> 1.) Test the query syntax using an Oracle-provided tool. (Something
>> analogous to SQL Server's Query Analyzer.)
>> 2.) Try explicitly specifying the Value property, rather than relying on
>> the
>> default property.
>> The fully qualified prop name is rsQuery.Fields(0).Value. Some
>> environments
>> (like JScript) assume you want a reference to the Field object unless you
>> specify the value property.
>> 3.) Trap errors and enumerate the Connection.Errors object when any
>> errors
>> occur, sometimes there are multiple messages, soime of which may be more
>> informative.
>> 4.) Show us some code. You might be doing something dumb, like MoveFirst
>> on
>> a forward-only recordset, or trying to assign a derived, or otherwise
>> inherently read-only field. That [ever-annoying] error is more typically
>> associated with recordset operations that change data. I can't recall
>> ever
>> seeing it happen when reading a scalar value... if it does happen ever,
>> it
>> doesn't happen often.
>>
>>
>> -Mark
>>
>>
>>
>>
>> > ------------------------------
>> > Provider error '80020009'
>> >
>> > Multiple-step OLE DB operation generated errors. Check each OLE DB
>> > status value, if available. No work was done.
>> >
>> > /CallLeveraging/assets/forms/adhoc_popup.asp, line 0
>> > -------------------------------------------
>> >
>> > The database is Oracle 9 and IIS 6 is the webserver.
>> >
>> > Anyone have any ideas at all? Thank you in advance!
>> >
>