Catch Result from Stored Procedure

[JJ26]

Hi guys,
I'm having trouble here with my ASP page dealing with Stored Procedure that return a build-in recordset.
it seems my ASP page tracking that the recordset is not opened yet, and that's why it can't return any fields to my Response object.

I need to know if ASP page can handle returnable stored procedure .

any one can help to share your experiences ???

thanks alot

 

[meckeard]

Post your code so that we may help.

Mark

 

[JJ26]

OK, here's the stored procedure first

CREATE Proc dbo.sp_EditMainArea    @kdMain  char(5),    
             @shortD char(50),			
             @longD char(50),@pic char(10) ,
	     @Descr char(255), @uid char(8) 
AS
declare @curs cursor;
declare @vtemp char(5);
declare @hsl char(1)

Set @curs = cursor READ_ONLY FOR 
   select kode from T_references WHERE kd_ref = 'TOPIC' 
   and   kode = @kdMain 
Open @curs
Fetch Next from @curs  INTO @vtemp
If @@FETCH_Status = 0 	--record exist
Begin
	Update T_references set shortNm = @shortD, longNm = 
             @longD, descri = @pic ,
	     descri2 = @Descr, tglUpd = getdate() , Coder= 
             @uid
      	     Where kd_ref = 'TOPIC' And kode = @kdMain
     Set @Hsl = '1' 
End
Else     Set @Hsl = '0' 

Close @curs
Deallocate @curs

Select  @hsl  as Result

GO

============================================================
Now my ASP code

<%
ssql = "Exec sp_AddMainArea '" & kode & "' , '" & short 
        & "' , '" & longNm & "', '" & pic & "', '" & des 
        & "', '" & UserID &"' "
rstemp.open  ssql , Conn
If rstemp("Result")  = "1" then		
	msgerr = "<font color=blue><b>Saving process  of    
               Main Area " & kode & " succeded</b></font>"
End if
response.write msgerr
%>

-==============================================
Please help me, thanks alot.

 

[TJRTech]

JJ26,

Might I suggest a simpler stored procedure. It seems that you are using a cursor in order to check that a record exists. You can do that simply by attempting the update and checking @@ROWCOUNT afterwards. If it (or more than one) existed, then you get a value >0 for @@ROWCOUNT, else you get 0.

And, instead of returning a single record for the indicator of the update, why not use an output parameter in the stored proc? And, since you might get 0, 1, or more records updated, make that output param an int.

Here is my version of the sp:
<code>

CREATE Proc dbo.sp_EditMainArea
@kdMain char(5),
@shortD char(50),
@longD char(50),
@pic char(10) ,
@Descr char(255),
@uid char(8),
@NumRows int OUTPUT
AS

Update T_references set shortNm = @shortD,
longNm = @longD,
descri = @pic ,
descri2 = @Descr,
tglUpd = getdate() ,
Coder= @uid
Where kd_ref = 'TOPIC' And kode = @kdMain
SET @NumRows = @@ROWCOUNT
RETURN(0)

</code>

Also, you will have to look at ADO reference for the "correct" way to call stored procedures and get back output parameters. It is quite easy, but does have a learning curve. Avoid the temptation of using a SQL statement with an exec and the sp name, and the params listed. Instead, bind the parameters, and the output, etc.

Regards,
TR