How to suppress error message return from SQL Server Stored Procedure in ASP Form,

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

SQL Server 2000 Forums

SQL Server Development (2000)

How to suppress error message return from SQL Server Stored Procedure in ASP Form,

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-25 : 12:17:58

Tejas writes "Dear Sir,

I have ASP form and within this form I am calling Stored Procedure of SQL Server.

Example
-------
ASP Code
--------

with rsemp_status

.ActiveConnection = cn
.CommandText = "employee_sp;3"
.CommandType = 4

.Parameters.Refresh

.Parameters("@emp_id")= trim(request.form("myChk"))

.Execute
set a=.Parameters("@status")

end with

Stored Procedure
----------------
CREATE PROCEDURE employee_sp;3
@emp_id INT,
@status INT OUTPUT
AS

IF NOT EXISTS (SELECT * FROM employee WHERE emp_id = @emp_id)
BEGIN
SET @status = -2002
RETURN -2002
END

DELETE employee
WHERE emp_id = @emp_id

IF @@ERROR <> 0
BEGIN
SET @status = -3001
RETURN -3001
END
ELSE
BEGIN
SET @status = 0
RETURN 0
END
GO

Now, when DELETE statement fails because foreign key constraint
of other table it immediately generate an error

Error
-----
Server: Msg 547, Level 16, State 1, Procedure employee_sp, Line 18
DELETE statement conflicted with COLUMN REFERENCE constraint 'fk_project_incharge'. The conflict occurred in database 'PMS', table 'project', column 'incharge'.
The statement has been terminated.

Now this error message terminates the execution of ASP Form.

How to trap this error, without displaying this error message.

Thanks,
Tejas"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-25 : 13:52:24

Ideas.

don't delete the employee if it is going to error

(if SQL2k) have and INSTEAD OF trigger for employee on delete that handles the problem by either cascading the delete thru the dependencies or just not deleting the record.

I'm no ASP guru, but I do know that if you issue a statement that violates a constraint, it will throw an error and halt processing. There is no 'on error resume next' or 'try...catch...' in sql. There may be something you can do in asp though . . . Idunnoboutthat

<O>

Peter Dutch
Posting Yak Master

127 Posts

Posted - 2002-04-26 : 02:30:48

Asp code:

ON ERROR GOTO 0
rsemp_status.Errors.Clear

'execute the sp
IF rsemp_status.Errors.Count > 0 THEN
Response.Write "Something went wrong!!"
Else
Response.Write "Whoohoo, all good!"
End if

Check the VBScript docs for the Errors collection. You could also get the error number and description from this collection.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-04-26 : 17:46:01

In ASP, to allow your page to continue processing so you can handle the errors yourself, you need to include

On Error Resume Next

But you should read up on the scope of this command because it behaves differently in terms of rolling up errors whether you put this at the page level or at the subroutine/function level.

You might check [url]http://www.4guysfromrolla.com[/url] for more ASP information.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources