Solved

How to throw a new exception when an exception is caught?

Posted on 2010-08-12
14
621 Views
Last Modified: 2012-05-10
Within my code I access a data layer to execute a SQL command.  In some cases, SQL can throw an exception (SqlException) if the database rules are broken during the transaction.  When this happens, by data I can roll back this transcation.  

All works well, except I want to override the SqlException 515 error message with my own user-friendly message.  Currently the VS2010 debugger is saying Exception Unhandled and not showing my own error.  The code also fails when executed.  Can anyone help me achieve what I need?

Here's the code...

Dim s as new DataLayer.SqlTransaction
s.OpenConnection
Try
	For Each r As DataRow In _dt.Rows
		' database transaction parameters are all set here
		s.Save() ' save data
		s.ClearParams() ' clear parameters in data layer ready for next DataRow
	Next
	s.Commit() ' commit Sql Transaction
Catch sqlEx As SqlClient.SqlException
	Select Case sqlEx.Number
		Case 515 ' cannot insert NULL
			If sqlEx.Message.Contains("Cannot insert the value NULL into column 'myDbColumnName'") Then
				' !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
				' Error occurs here in VS2010 - Exception not handled
				Throw New Exception("The foreign key values for xxxxxx were not found, please check your spelling and try again.")
			Else
				Throw sqlEx
			End If
		Case Else
			Throw sqlEx	' unknown SQL error, so throw
	End Select
Catch ex As Exception
	s.RollBack() ' catch all exceptions and roll back
	myLiteral.Text = "Error:" & ex.Message
Finally
	s.Closedown() ' close database connection
End Try

Open in new window

0
Comment
Question by:Rouchie
  • 4
  • 3
  • 2
  • +5
14 Comments
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 33423538
hi Rouchie
Just after case 515 give your own message!!!
0
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 33423607
Hi,

You have to use MSGBOX intead of Throw new exception, so intead of this

Throw New Exception("The foreign key values for xxxxxx were not found, please check your spelling and try again.")

Use this

MsgBox("The foreign key values for xxxxxx were not found, please check your spelling and try again.", MsgBoxStyle.Information)
0
 
LVL 1

Expert Comment

by:tnewc59
ID: 33423615
can you wrap the thrown new exception in a nested try catch?
try
Throw New Exception("The foreign key values for xxxxxx were not found, please check your spelling and try again.")
Catch ex as Exception
throw ex
end try
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 100 total points
ID: 33423769
The error you are getting basically means that, although you are throwing a new exception, that exception is bubbling up the call stack and not being caught anywhere. The context in which you are executing the snippet you posted would be useful?
0
 
LVL 11

Accepted Solution

by:
ladarling earned 400 total points
ID: 33423931
The bigger question is: Why are you playing pitch and catch with the exception that way?  I mean, why new it up and rethrow when you can handle it with else. If you were going to throw it back up the stack to another process I would understand, but if you are going to immediately attempt to catch it as generic just handle it right there....
Catch ex As Exception
If TypeOf(ex) Is SqlClient.SQLException AndAlso ex.Number = 515 Then
..... do your thing
Else
 s.RollBack() ' catch all exceptions and roll back
 myLiteral.Text = "Error:" & ex.Message

Finally...
 
0
 
LVL 5

Expert Comment

by:KiasChaos83
ID: 33425602
To throw a new exception while you're in the catch statement you would do it like this:

catch (Exception ex) {
    throw new Exception("hello 123");
}

To throw the same exception you would do:
catch (Exception ex) {
    throw;
}

You could use the below but it will mean you don't get the call stack in the exception:
catch (Exception ex) {
    throw ex;
}

You could also run checks on the exception and deal with certain ones and pass others along. E.g:

catch (Exception ex) {
    if (Exception is NotImplementedException) {
        /* do something */
        return;
    }
    throw;
}
0
 
LVL 5

Expert Comment

by:KiasChaos83
ID: 33425616
Sorry, I think in c#, forgot to translate to VB.NET.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 25

Author Comment

by:Rouchie
ID: 33427544
Hi All
Thanks for your replies.  Please let me answer each of your responses in turn...

>> You have to use MSGBOX intead of Throw new exception

This is a web application so I don't think that will work.  I posted under VB.NET topic area though because I thought this issue is relating to language structure rather than the application of it.


>> can you wrap the thrown new exception in a nested try catch?


Doesn't that just do the same as what the existing code does, but with more effort?!?


>> The context in which you are executing the snippet you posted would be useful?

The code migrates data from an on-screen DataTable into SQL Server.  So, I am iterating through each DataRow and firing an INSERT stored procedure within my data layer.  The problem is that my users have no idea about the database structure, so occasionally if the DataTable has values that might cause issues (e.g. NULLs where there should be values), SQL is throwing an Exception.
The issue is that SQL's error messages aren't very useful to people who haven't a clue about database structure, so I need to override SQL's messages with my own (more user friendly) message.

What I was hoping to do therefore was catch the SqlException, then pass my own Exception up the stack that contained more useful information.  I know my code needs improvement, so I thought I'd check on the best way to achieve this exception handling issue.


>> why new it up and rethrow when you can handle it with else

I read ages ago that good practice states you should check for different types of exceptions in turn, hence the reason I checked for the SqlException first.  Is this not correct?

>> To throw a new exception while you're in the catch statement you would do it like this:

I did think that's what my code does.  Catch and then throw, but I get the error shown in my original post.

The pseudo code for my final objective would be something like this:

open connection
begin transaction
for each datarow in mydatatable
  insert data into database
  if error occurred
      if error is a SQL error
          override it to provide a more user friendly error
      else
          show the ASP.NET error
      end if
      rollback transaction
  end if
next
commit transaction
close connection
0
 
LVL 5

Expert Comment

by:KiasChaos83
ID: 33427717
If that's the case, then your code looks basically good.... I made some slight changes. Does this work?
Dim s as new DataLayer.SqlTransaction

s.OpenConnection

Try

        For Each r As DataRow In _dt.Rows

                ' database transaction parameters are all set here

                s.Save() ' save data

                s.ClearParams() ' clear parameters in data layer ready for next DataRow

        Next

        s.Commit() ' commit Sql Transaction

Catch sqlEx As SqlClient.SqlException

        Select Case sqlEx.Number

                Case 515 ' cannot insert NULL

                        If sqlEx.Message.ToLower().Contains("cannot insert the value null into column") Then

                                Throw New Exception("The foreign key values for xxxxxx were not found, please check your spelling and try again.")

                        Else

                                Throw 

                        End If

                Case Else

                        Throw 

        End Select

Catch ex As Exception

        s.RollBack() ' catch all exceptions and roll back

        myLiteral.Text = "Error:" & ex.Message

	Throw

Finally

        s.Closedown() ' close database connection

End Try

Open in new window

0
 
LVL 4

Expert Comment

by:vbgb
ID: 33428194
Why don't you use a javascript alert box to display an error to the user and allow them to correct their entries and try again.

Simply put the code below in your code behind and call it instead if throwing the new exception.
i.e.

Messagebox("The foreign key values for xxxxxx were not found, please check your spelling and try again.")
Private Sub MessageBox(ByVal msg As String)

        ' define a javascript alertbox containing

        ' the string passed in as argument



        ' create a new label

        Dim lbl As New Label()



        msg = msg.Replace("'", "\'")

        ' add the javascript to fire an alertbox to the label and

        ' add the string argument passed to the subroutine as the

        ' message payload for the alertbox

        lbl.Text = "<script language='javascript'>" & Environment.NewLine & _

                   "window.alert('" + msg + "')</script>"



        ' add the label to the page to display the alertbox

        Page.Controls.Add(lbl)



    End Sub

Open in new window

0
 
LVL 25

Author Comment

by:Rouchie
ID: 33428720
@KiasChaos83

I altered the code to match yours, but there are issues.  I've attached a plan to illustrate the debugger's movement through the code.  First the first DataRow, everything works well and the FOR loop returns to the beginning.  On the second run though, a SqlException is thrown.  This gets caught and the exception is recognised as 515, which then throws my inner exception.
The problem then however is that the code just returns straight to the FINALLY part, and never goes through the CATCH EX AS EXCEPTION part.

Do you know why this might be?
debugger.gif
0
 
LVL 25

Author Comment

by:Rouchie
ID: 33429100
@ladarling

Your code does work because there is no property called Number in Exception.  Therefore, is it feasible to cast the Exception to a SqlException before reading the Number value?
0
 
LVL 11

Assisted Solution

by:ladarling
ladarling earned 400 total points
ID: 33429876
Yes it is, sorry for the confusion. The snippit below should allow you to handle the specific SQL error that you are after and treat all others as generic.
The problem that you are having with rethrowing in this example is that you *did* catch the error. To rethrow like you want you would need to catch it -outside- of the current try catch block. It  useslogic similar to Select Case... meaning once you match on a value, you couldnt change the value and then have it pick up on the next "case"  because no further cases are evaluated.
 

Catch ex As Exception

            Dim isGeneric As Boolean = True

            If TypeOf (ex) Is Data.SqlClient.SqlException Then

                Dim sqlex = DirectCast(ex, Data.SqlClient.SqlException)

                If sqlex.Number = 515 Then

                    '.... handle the SQL exception

                    isGeneric = False

                End If

            End If

            If isGeneric Then

                '.... rollback

            End If

        End Try

Open in new window

0
 
LVL 25

Author Comment

by:Rouchie
ID: 33429908
After spending the whole day searching for info on this, I am unable to find any examples that match the code I posted.  Therefore, I can only presume that I am doing this wrongly and that the Exception cannot be bubbled up in this way.

Please correct me if I'm wrong.

Anyway, I've re-written the code to use the RollBack() function whenever any type of exception is caught, e.g.

Try
   ' do database work here
Catch SqlEx as SqlException
    s.RollBack()
    Select Case sqlEx.Number
        ' show user friendly error message here
    End Select
Catch Ex As Exception
     s.RollBack
     ltl_output.Text = ex.Message ' show actual error if not already caught
Finally
     s.CloseDown()
End Try

This seems to work okay, although there is an issue with retaining the database object's access after the exception is thrown, which is for a new post...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
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 …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now