Re: store data in a recordset from Output cursor

bappa001 — Wed, 17 Jun 2009 03:19:01 GMT

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!!!!

Re: store data in a recordset from Output cursor

tomkmvp — Tue, 16 Jun 2009 14:01:00 GMT

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

Re: store data in a recordset from Output cursor

qbernard — Tue, 16 Jun 2009 13:14:55 GMT

Try SQL newsgroups or forums.

store data in a recordset from Output cursor

bappa001 — Tue, 16 Jun 2009 09:56:59 GMT

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.

Classic ASP

Re: store data in a recordset from Output cursor

Re: store data in a recordset from Output cursor

Re: store data in a recordset from Output cursor

store data in a recordset from Output cursor