Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server ASP
|
SQL Server ASP Discussions about ASP programming with Microsoft's SQL Server. For more ASP forums, see the ASP forum category.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server ASP section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 15th, 2004, 05:34 AM
Authorized User
 
Join Date: Nov 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default Catching errors in a stored procedure

Hi,

Is it possible to set error handling locally to a stored procedure, what I am trying to do is insert data using a stored procedure and if the insert fails due to a constraint between the FK of two tables I want to pass back a friendly message to an ASP page, but what seems to happen is that SQL handles the error before I can catch it displays the following message:

INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS', table 'Employee'

So within my SP I am adding the following after my insert statement:

IF @@ERROR<>0
BEGIN
     SELECT' WRONG SEC'
END


Then in my asp page I use the following after my execute command:

if objRS(0) = "WRONG SEC" Then
SEC_ERROR = 1
end if

I am no expert in SQL but my guess is SQL is taking control of the error which is why this select doesn't work.

Any ideas?

May thanks
Peter


P.S

I have also just tried to use TRAN only commiting to the insert if no error but still to no avail it still shows the default error message in the browser:

SELECT @UPDATE_ERROR = @@ERROR

-- Test the error values.
IF @UPDATE_ERROR= 0
BEGIN
   COMMIT TRAN
END
ELSE
BEGIN
         SELECT 'WRONG SEC'
   ROLLBACK TRAN
END
 
Old February 3rd, 2004, 12:54 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

You can try using the RaiseError method. That may work. Then use the objConnection.Errors collection to check for errors to display to the screen.
 
Old October 13th, 2004, 10:17 AM
Registered User
 
Join Date: Oct 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Trojan_UK,

Did you find the solution for this problem?
I have the same problem, cannot handling the error and cannot execute the rollback instruction.

Regards,
Marco


Quote:
quote:Originally posted by Trojan_uk
 Hi,

Is it possible to set error handling locally to a stored procedure, what I am trying to do is insert data using a stored procedure and if the insert fails due to a constraint between the FK of two tables I want to pass back a friendly message to an ASP page, but what seems to happen is that SQL handles the error before I can catch it displays the following message:

INSERT statement conflicted with TABLE FOREIGN KEY constraint 'FK_Directory_sec_employee'. The conflict occurred in database 'CNMS', table 'Employee'

So within my SP I am adding the following after my insert statement:

IF @@ERROR<>0
BEGIN
      SELECT' WRONG SEC'
END


Then in my asp page I use the following after my execute command:

if objRS(0) = "WRONG SEC" Then
 SEC_ERROR = 1
end if

I am no expert in SQL but my guess is SQL is taking control of the error which is why this select doesn't work.

Any ideas?

May thanks
Peter


P.S

I have also just tried to use TRAN only commiting to the insert if no error but still to no avail it still shows the default error message in the browser:

SELECT @UPDATE_ERROR = @@ERROR

-- Test the error values.
IF @UPDATE_ERROR= 0
BEGIN
   COMMIT TRAN
END
ELSE
BEGIN
         SELECT 'WRONG SEC'
   ROLLBACK TRAN
END
 
Old October 13th, 2004, 11:29 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Hello,

I did not see a begin tran(saction) in the proc name. Also, why not check the @@error field directly, instead of passing it to a variable? Try referencing 'WRONG SEC' by an alias so you can reference to the field by name instead of position. Not sure if that would help...

Brian
 
Old October 13th, 2004, 11:56 AM
Registered User
 
Join Date: Oct 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi bmains,

Thanks for your fast response.
This is a example of my "problem":

--
begin transaction
insert into tblTest(unique_field)
values(100)
if @@ERROR<>0
 begin
  ....
  rollback transaction
 end
else
 begin
  commit transaction
 end
---

If the "insert into.. " fails for any reason (duplicate value, rights, etc) TSQL raise a exception and
the next instruction "IF @@ERROR<>0 " dont work.
I cannot handling the error.

Thanks in advanced for your help.
Marco

 
Old October 13th, 2004, 01:07 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
Default

So what is happening, do you believe that the else statement is executing, or is the problem that an error actually comes back to the application?

I don't know if if @@error <> 0 is foolproof, because I think there are errors that will return even if you have transactional processing. Permissions may be one of those issues.... I haven't fully researched it myself so I can't help you further, but would recommend looking into what errors transactional processing will catch.

Brian
 
Old April 13th, 2005, 04:13 AM
Registered User
 
Join Date: Apr 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hello there,

here is the solution as far as i see..

first of all, the working principle of an SP in case of an error is this..

- do something
- if there is an error, raise an exception
- set the proper @@error code
- execute any instructions left

as you see, (although you did not be able to see it did) your SP continued to work after raising the exception (which you saw in the asp page..)

(in order to be convinced you can put some test code after the error test clause..)

one things to keep in mind here:

- you should read the @@error and @@rowcount values into local variables immediately after the query, (not after any "return" or "print" command). And check those local variables. This will make sure that those @@error and @@rowcount values belong to your intended query results..


SO why do you see an exception message in the asp page? this might be related to your connection objects or something like that.. you might need to check them or implement an error handling mechanism there if necessary...

i hope it helps..
 
Old April 13th, 2005, 06:27 AM
Registered User
 
Join Date: Apr 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Additionally,

for an exception mechanism on asp you could have a look at this thread.. (search for the keywords "on error resume next")

http://p2p.wrox.com/topic.asp?TOPIC_ID=1607





Similar Threads
Thread Thread Starter Forum Replies Last Post
This Stored Procedure rao965 SQL Server 2000 2 July 2nd, 2007 07:21 PM
Stored Procedure jezywrap SQL Server ASP 1 January 3rd, 2007 12:29 AM
Stored Procedure Help. midway11 SQL Language 3 November 20th, 2006 06:36 AM
stored procedure allang MySQL 3 January 26th, 2005 12:48 PM
Stored Procedure bmains SQL Server ASP 2 October 8th, 2004 03:19 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.