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.comwrote in message
news:1165959660.852629.77420@73g2000cwn.googlegrou ps.com...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

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:

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.comwrote in message
news:1166192823.953442.192250@79g2000cws.googlegro ups.com...On Error Resume Next
If rsQuery.State = 0 Then
Response.Write "recordset is closed"
Else
Response.Write "Fields.Count = " & rsQuery.Fields.Count & "<br>"
End If
If Err.Number <0 Then
Response.Write DumpConnectionErrors()
End If


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