[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Proc error handling.  vb.net

Posted on 2014-12-11
2
Medium Priority
?
226 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 2000 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

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
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.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

607 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