|
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
|
|
|
January 15th, 2004, 05:34 AM
|
Authorized User
|
|
Join Date: Nov 2003
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
February 3rd, 2004, 12:54 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
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.
|
October 13th, 2004, 10:17 AM
|
Registered User
|
|
Join Date: Oct 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
October 13th, 2004, 11:29 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
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
|
October 13th, 2004, 11:56 AM
|
Registered User
|
|
Join Date: Oct 2004
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
October 13th, 2004, 01:07 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 1,998
Thanks: 0
Thanked 3 Times in 3 Posts
|
|
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
|
April 13th, 2005, 04:13 AM
|
Registered User
|
|
Join Date: Apr 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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..
|
April 13th, 2005, 06:27 AM
|
Registered User
|
|
Join Date: Apr 2005
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
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
|
|
|