Solved

Proc error handling.  vb.net

Posted on 2014-12-11
2
205 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 33

Author Closing Comment

by:Mike Eghtebas
ID: 40495943
Thank you.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

785 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