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 said:
Aaron said:
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!
