ASP or Stored Proc problem w/ raiserror

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

ASP or Stored Proc problem w/ raiserror

Author

Topic

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-07-27 : 14:11:03

Hi,
I created this stored proc


ALTER PROCEDURE [dbo].[spAddEditProgram] 
	@id			int, 
	@pname		varchar(150)
AS
BEGIN
	SET NOCOUNT ON;
	if (@id <= 0) or (@id is null)
	  Begin
		if (not exists (select ProgramID from Programs where lower(ProgramName) = lower(@pname)))
			Begin
				insert into Programs(ProgramName)
					values (@pname);
			end
		else
			Begin
			-- we have a duplicate program name! raise error
			RAISERROR('Error! That Program Name already exists! Choose a new name or edit the existing one.', 10, 1)
			
			end
	  end
	else if (exists (select ProgramID from Programs where ProgramID = @id))
	  Begin
		update Programs set ProgramName = @pname where ProgramID = @id;
	  end
END

I call the stored proc from a classic ASP page however when I enter a duplicate name the stored proc handles it correctly (yay!) but the asp recordset is empty (bleah)
How do i get the error string into the recordset? Am I raising the error correctly?
it works right if I run the sp from the query analyzer.
Please advise...

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-07-27 : 14:34:37

Ok, I added this line after RAISERROR

select 'Error! That name already exists. please choose another' as err_msg;

and the string shows up in the recordset.
Even though this works; is this the correct way to do it?

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-07-27 : 15:08:51

that's the wrong way to do it.

after you execute the SP

capture the error in the ASP


<%
Dim strErr

On Error Resume Next
' execute sp

If Err.Number <> 0 Then
   strErr = Err.Description
End If
On Error Goto 0

' now do something with the error like display it
Response.Write "<h4>" & strErr & "</h4>"
%>

Zim327
Yak Posting Veteran

62 Posts

Posted - 2009-07-27 : 15:24:03

Thanks Russell!
That did the trick.
Thanks again

Best regards,
Zim
(Eternal Yak God Emperor from the Future)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources