retrieving recordset-help needed urgent

[mmcg]

Hi all,
I am unable to retrieve recordsets (Connected to Oracle 9i) but the PL/SQL procedure is getting executed(all the delete and update queries are working fine).If I try to access the values in the record set I get an error:
Operation is not allowed when the object is closed.

The part of the code is:

Set Comm = Server.CreateObject("ADODB.Command&quot
Set rs = Server.CreateObject("ADODB.Recordset&quot
RS.CursorLocation = 3
Set comm.ActiveConnection = Conn
/*con -> connection object*/
comm.commandtype = 4
comm.commandtext = "test"
/*test -> PL/SQL procedure*/
Set rs = comm.execute()
response.write rs.eof /* or any code to access the record set gives an error*/
Any help appreciated
Thanx in advance

 

[codestorm]

How did you intend to retrieve it through ADO?
I'm guessing you may need to declare a Parameter object of type adParamOutput. codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[mmcg]

I tried it but that too dosent work .!!

 

[Palooka]

Oracle doesn't work in the same way as MSSQL Server. While MSSQL Server automatically builds and returns a recordset, Oracle does not. You have to use cursors.

Something like this:

DECLARE

-- Define a reference cursor

   TYPE refCursor IS REF CURSOR;

-- Declare cursor variable

   myCursor refCursor;
   
   OPEN myCursor FOR

SELECT * FROM table;

The part of the code that is colored red, is where your query goes.

Hope this helps,
Palooka

 

[codestorm]

I vote 1 Yuck for Oracle. codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[BarkingFrog]

mmcg,

This may sound very simplistic but after you save, update and delete are you doing a commit?

I have had the same problem you are. I can see everything that I am doing in the database if I go directly into it and look, but nothing was being returned via recordset. Once I started commiting everything then my recordsets were able to return everything to me.

 

[mmcg]

No commit ....dosent solve my problem.
If my code is
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
Conn.open &quot;DSN=****; UID=***; PWD=***&quot;
Set Comm = Server.CreateObject(&quot;ADODB.Command&quot
Set comm.ActiveConnection = Conn
comm.commandtype = 4
comm.commandtext = &quot;test&quot;
set param = comm.Parameters
param.append comm.createparameter(&quot;a&quot;,3,2)
response.write conn&&quot;<BR>&quot;
comm.execute()
a=Comm(&quot;a&quot
response.write &quot;STATE: &quot;&Comm.state&&quot;<BR>&quot;
response.write &quot;A: &quot;&a
The value is returned as 3000.That is, my program works but the only problem is with recordsets.But comm.state gives 0.What does it mean?

 

[codestorm]

Have you tried something like
param.append comm.createparameter(&quot;Return&quot;,adInteger,adParamReturnValue)
? codestorm
Fire bad. Tree pretty. - Buffy
select * from population where talent > 'average'
<insert witticism here>

 

[mmcg]

I get an error

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Oracle][ODBC][Ora]ORA-24334: no descriptor for this position

/aspproc.asp, line 27

if I use it