output parameters

[arkadia93]

I am trying to return an output parameter from a stored procedure, and I am then trying to write this value to the screen using Response.Write. Can anybody tell me what I am doing wrong?

If Request.QueryString("Approve") = "y" then
dim ObjConnApprove,ObjCommApprove,ObjRsetApprove
set ObjConnApprove = server.CreateObject("ADODB.connection")
set ObjRsetApprove = server.CreateObject("ADODB.Recordset")
Set ObjCommApprove = server.CreateObject("ADODB.Command")
ObjConnApprove.Open strConn
ObjCommApprove.ActiveConnection = ObjConnApprove
ObjCommApprove.CommandType = 4
ObjCommApprove.CommandText = "ApproveDeclineHoliday"
ObjCommApprove.Parameters.Refresh
ObjCommApprove.Parameters(1).Value = Request.QueryString("HolidayKey")
ObjCommApprove.Parameters(2).Value = "y"
if Session("IsHR") = 1 then
ObjCommApprove.Parameters(3).Value = "HR" 'if HR, change parameter to "HR"
else
ObjCommApprove.Parameters(3).Value = "Manager"
end if
ObjCommApprove.Parameters.Append ObjCommApprove.CreateParameter("@RequestOutcome", adInteger,adParamOutput, oVal)
Dim intRequestOutcome
intRequestOutcome = ObjCommApprove.Parameters("@RequestOutcome").value

ObjCommApprove.Execute

Response.Write(intRequestOutcome)
response.end
set ObjCommApprove = nothing
set ObjConnApprove = nothing

Response.Redirect "holidaystobeapproved.asp"
End If

Stored procedure :
CREATE procedure dbo.ApproveDeclineHoliday

(
@HolidayKey int,
@Approve char(1),
@AppDecBy varchar(10),
@RequestOutcome int output
)

as

declare @CurrentStatus int

select @CurrentStatus = RequestOutcome
from tbl_HolidayRequest
where HolidayRequestKey = @HolidayKey

if @Approve = 'y'
begin
if @AppDecBy = 'Manager'
begin
if @CurrentStatus = 1
begin
set @RequestOutcome = 2

update tbl_HolidayRequest
set RequestOutcome = 2
where HolidayRequestKey = @HolidayKey
end

if @CurrentStatus = 3
begin
set @RequestOutcome = 4

update tbl_HolidayRequest
set RequestOutcome = 4
where HolidayRequestKey = @HolidayKey
end
end

if @AppDecBy = 'HR'
begin
if @CurrentStatus = 1
begin
set @RequestOutcome = 3

update tbl_HolidayRequest
set RequestOutcome = 3
where HolidayRequestKey = @HolidayKey
end

if @CurrentStatus = 2
begin
set @RequestOutcome = 4

update tbl_HolidayRequest
set RequestOutcome = 4
where HolidayRequestKey = @HolidayKey
end
end

end

if @Approve = 'n'
begin
if @AppDecBy = 'Manager'
begin
set @RequestOutcome = 6

update tbl_HolidayRequest
set RequestOutcome = 6
where HolidayRequestKey = @HolidayKey
end

if @AppDecBy = 'HR'
begin
set @RequestOutcome = 5

update tbl_HolidayRequest
set RequestOutcome = 5
where HolidayRequestKey = @HolidayKey
end
end

return @RequestOutcome
GO

 

[Tarwn]

I generally don't use command objects to handle my stored procedures, so I may be a little off base, but shoulnd't you be waiting until after you execute the stored procedure before you retrieve the return value?

         Dim intRequestOutcome
            intRequestOutcome = ObjCommApprove.Parameters("@RequestOutcome").value
            
        ObjCommApprove.Execute

-T