Solved

Proc error handling.  vb.net

Posted on 2014-12-11
2
209 Views
Last Modified: 2014-12-12
The following proc deletes records in tblSoftware. SoftwareID from this table is used as FK in another table. On delete, it will produce some errors ("There are some records with this SoftwareID in tblOrderDetails; cannot delete this item.").

What are options to show this error message.

Question: How to catch this error in try/catch with multiple catch for this error and some other error may happen?

ALTER PROCEDURE [dbo].[spSoftwareDelete]
     @SoftwareID int
As
BEGIN

	Delete From tblSoftware Where SoftwareID = @SoftwareID 
   
	if @@Error>0 
        Begin
		 -- Set @msg = @msg + '; SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))
		-- Raise an error and return
		RAISERROR ('There are some records with this SoftwareID in tblOrderDetails; cannot delete this item..', 16, 1)
		RETURN @@ERROR
        End 
END 

Open in new window

0
Comment
Question by:Mike Eghtebas
2 Comments
 
LVL 11

Accepted Solution

by:
LordWabbit earned 500 total points
ID: 40495549
Well it would be best to check for the FK restraint first, rather then just trying to delete and failing.  That way you can decide what message needs to be sent, as well as having a catch all for unexpected errors (deadlock's etc.)
Something like
ALTER PROCEDURE [dbo].[spSoftwareDelete]
     @SoftwareID int
As
BEGIN
	DECLARE @Check INT
	SET @Check = (SELECT COUNT(*) FROM OtherTable WHERE SoftwareID = @SoftwareID)
	IF (@Check > 0)
	BEGIN
		RAISERROR ('There are some records with this SoftwareID in tblOrderDetails; cannot delete this item..', 16, 1)
		RETURN @@ERROR
	END
	ELSE
	BEGIN
	Delete From tblSoftware Where SoftwareID = @SoftwareID 
   
	if @@Error>0 
        Begin
		 -- Set @msg = @msg + '; SQL Server error: ' + CAST(@@Error AS NVARCHAR(10))
		-- Raise an error and return
		RAISERROR ('There are some records with this SoftwareID in tblOrderDetails; cannot delete this item..', 16, 1)
		RETURN @@ERROR
        End 
END 

Open in new window

0
 
LVL 34

Author Closing Comment

by:Mike Eghtebas
ID: 40495943
Thank you.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question