Thread: store data in a recordset from Output cursor

Last post 06-16-2009 11:19 PM by bappa001. 3 replies.

Average Rating Rate It (5)Thank you for the rating!

RSS

Page 1 of 1 (4 items)

Sort Posts:

06-16-2009, 5:56 AM

bappa001
Joined on 06-15-2009, 3:10 AM
Posts 6

store data in a recordset from Output cursor

Reply Contact

I have a stored procedure with output type as cursor.I want to store the output in a record set, to load a dropdown list.

if anyone can give me some sample code, or some idea about implementation, it will be very helpful.

Thanks in advance.

06-16-2009, 9:14 AM In reply to

qbernard
Joined on 03-25-2003, 10:12 PM
Malaysia
Posts 3,126

Re: store data in a recordset from Output cursor

Reply Contact

Try SQL newsgroups or forums.

Cheers,
Bernard Cheah

06-16-2009, 10:01 AM In reply to

tomkmvp
Joined on 03-20-2003, 10:27 AM
Central NJ
Posts 6,078

Re: store data in a recordset from Output cursor

Reply Contact

http://databases.aspfaq.com/database/when-should-i-use-createobject-to-create-my-recordset-objects.html

http://databases.aspfaq.com/database/using-stored-procedures.html

Tom Kaminski IIS MVP
http://mvp.support.microsoft.com/
http://blogs.iis.net/tomkmvp/
http://www.linkedin.com/pub/8/690/408
http://www.bluesrepublic.org/

06-16-2009, 11:19 PM In reply to

bappa001
Joined on 06-15-2009, 3:10 AM
Posts 6

Re: store data in a recordset from Output cursor

Reply Contact

Stored Procedure is returning the data as a out cursor

PROCEDURE PR_GET_SERVICE_YRS(p_in_ivr_plan_num VARCHAR2,

p_in_age_field VARCHAR2 DEFAULT 'BIRTH_DT',

p_out_yrsofserv OUT NOCOPY yrsofserv_cursor) IS

BEGIN

OPEN p_out_yrsofserv FOR

SELECT column_id,

column_nm

FROM tb_xop_re_dt_setup

WHERE ivr_plan_num = p_in_ivr_plan_num

AND column_nm = p_in_age_field;

EXCEPTION

WHEN OTHERS THEN

Pr_Xop_Log_Re_Errors('CRITICAL ERROR!Code'||SQLCODE||':'||

SQLERRM||' AT '||USER||

'.PK_XOP_RE.PR_GET_SERVICE_YRS');

END PR_GET_SERVICE_YRS;

And I have the following code to read the data from the record set,

dim NewConnx,NewCommandx,rsNewCommandx

set NewConnx = server.CreateObject("ADODB.Connection")
NewConnx.Open Session("MyConnection")
set NewCommandx = server.CreateObject ("ADODB.Command")

set NewCommandx.ActiveConnection = NewConnx
set rsNewCommandx = Server.CreateObject("ADODB.Recordset")
NewCommandx.CommandText = "PK_XOP_RE.PR_GET_SERVICE_YRS"
NewCommandx.CommandType = 4
NewCommandx.Parameters.Append NewCommandx.CreateParameter("p_in_ivr_plan_num",adVarChar,adParamInput,30)
NewCommandx.Parameters.Append NewCommandx.CreateParameter("p_in_age_field",adVarChar,adParamInput,30)

NewCommandx("p_in_ivr_plan_num") = "XOP1008"
NewCommandx("p_in_age_field") = "BIRTH_DT"
NewConnx.BeginTrans()

Response.Write("Start")
rsNewCommandx = NewCommandx.Execute

if Err.number > 0 then
  NewConnx.RollbackTrans()
else
  NewConnx.CommitTrans()
end if


do while not rsNewCommandx.eof
        response.write(rsNewCommandx(0))
        response.write(rsNewCommandx(1))
        rsNewCommandx.movenext
    loop

NewConnx.Close
set NewConnx = nothing

But it is not working, i think it is the problem to read data from out cursor.

Please help!!!!

Page 1 of 1 (4 items)