Solved

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

Posted on 2010-08-12
14
613 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
Comment Utility
hi Rouchie
Just after case 515 give your own message!!!
0
 
LVL 13

Expert Comment

by:gamarrojgq
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Sorry, I think in c#, forgot to translate to VB.NET.
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 25

Author Comment

by:Rouchie
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
@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
Comment Utility
@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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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.
This video discusses moving either the default database or any database to a new volume.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

763 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

10 Experts available now in Live!

Get 1:1 Help Now