Accessing Stored Procedure Return Value From ASP Page

[kwil38]

I've created a simple stored procedure in Oracle 9i which requires 1 input parameter (i_date_registered) and returns 1 output value (o_grant_access). The procedure works fine when running from sqlplus and seems to run fine from my ASP page until I try to access the o_grant_access value....then I receive the following message:

ADODB.Fields error '800a0cc1'

Item cannot be found in the collection corresponding to the requested name or ordinal.

/ArkansasOnline/login_verification.asp, line 121


Any help would be greatly appreciated as I have been beating my head against a wall with this for the last 2 days!

Here's my stored procedure:

CREATE OR REPLACE PROCEDURE PRO_RECENT_WEB_SUBSCRIBER
(i_date_registered IN DATE,
o_grant_access OUT VARCHAR2)
IS
v_sysdate DATE;
v_difference NUMBER;
BEGIN
SELECT SYSDATE
INTO v_sysdate
FROM DUAL;
v_difference := v_sysdate - i_date_registered;
IF v_difference <= 1 THEN
o_grant_access := 'Y';
ELSE
o_grant_access := 'N';
END IF;
END PRO_RECENT_WEB_SUBSCRIBER;
/

Here's my ASP code to call the procedure and return a value:

<%
Dim strDateRegistered
strDateRegistered = RS_Registration_Lookup.Fields.Item("DATE_REGISTERED").Value
%>

<%
Dim CMD_RecentSub__I_DATE_REGISTERED
CMD_RecentSub__I_DATE_REGISTERED = ""
if(strDateRegistered <> "") then CMD_RecentSub__I_DATE_REGISTERED = strDateRegistered
%>

<%
set CMD_RecentSub = Server.CreateObject("ADODB.Command")
CMD_RecentSub.ActiveConnection = MM_ArkansasOnline_STRING
CMD_RecentSub.CommandText = "WEHCO.PRO_RECENT_WEB_SUBSCRIBER"
CMD_RecentSub.CommandType = 4
CMD_RecentSub.CommandTimeout = 0
CMD_RecentSub.Prepared = true
CMD_RecentSub.Parameters.Append CMD_RecentSub.CreateParameter("I_DATE_REGISTERED", 7, 1,20,CMD_RecentSub__I_DATE_REGISTERED)
CMD_RecentSub.Parameters.Append CMD_RecentSub.CreateParameter("O_GRANT_ACCESS", 200, 3,1)
set RS_NewSub = CMD_RecentSub.Execute
RS_NewSub_numRows = 0

Here's the code I using to access the return value (o_grant_access):

Dim strGrantAccess
strGrantAccess = RS_NewSub.Fields.Item("O_GRANT_ACCESS").Value

 

[amrita418]

I am not familier with ASP but you should should create a function instead (function should be used when all you want to do is return a value back to the calling program)
and make a function call from ASP.

something like:
"select yourfunction(input_value) from dual"

Regards,
AA

 

[kwil38]

Thanks for the response AA. I did start off with it as a function but unfortunatly I found out that ASP handle them very well, so I was directed to switch to a procedure instead.

I was finally able to make it work though. Here's what I changed my ASP code to...

<%
Dim strDateRegistered
strDateRegistered = RS_Registration_Lookup.Fields.Item("DATE_REGISTERED").Value

set CMD_RecentSub = Server.CreateObject("ADODB.Command")
CMD_RecentSub.ActiveConnection = MM_ArkansasOnline_STRING
CMD_RecentSub.CommandText = "WEHCO.PRO_RECENT_WEB_SUBSCRIBER"
CMD_RecentSub.CommandType = 4
CMD_RecentSub.CommandTimeout = 0
CMD_RecentSub.Prepared = true
CMD_RecentSub.Parameters.Append CMD_RecentSub.CreateParameter("I_DATE_REGISTERED", 7, 1,20,strDateRegistered)
CMD_RecentSub.Parameters.Append CMD_RecentSub.CreateParameter("O_GRANT_ACCESS", 200, 2,1)
CMD_RecentSub.Execute

Dim strGrantAccess
strGrantAccess = CMD_RecentSub.Parameters.Item("O_GRANT_ACCESS").Value
response.write(strGrantAccess)
%>