Retrieving underlying exception from SqlDataReader for INSERT statment

Question

I am executing a SQL insert statement e.g.

INSERT INTO Table (fk1, value1) OUTPUT inserted.pk VALUES ('fkv1', 'v1');

where "pk" is an auto incremented key value, as:

SqlDataReader reader = cmd.ExecuteReader();

The foreign key is in conflict with the parent table, and the reader.HasRows() reflects this, but how can I retrieve the actual exception object that was thrown with the error description? If you remove the "OUTPUT" statement, it throws the exception fine, but with that statement in there, it swallows the error, and just returns that "HasRows" == false.

I can see the error using the debugger under the "Results View" property, but how can I get this value in code?

Sql Server 2008r2 .NET 4.0

Thanks for any help.

EDIT:

This call does not throw an exception. The only indication that it has not completed successfully is that "HasRows" is false.

Have you tried surrounding this code with a try/catch block? — Joel Etherton
– Joel Etherton, Commented Nov 5, 2011 at 17:20
Make sure to catch(ExceptionType ex), to get the exception's information. — Ry-
– Ry- ♦, Commented Nov 5, 2011 at 17:22

sll · Accepted Answer · 2011-11-05 17:37:05Z

4

try
{
   SqlDataReader reader = cmd.ExecuteReader();
}
catch(Exception ex)
{
   string errorMessage = String.Format(CultureInfo.CurrentCulture, 
                         "Exception Type: {0}, Message: {1}{2}", 
                         ex.GetType(),
                         ex.Message,
                         ex.InnerException == null ? String.Empty : 
                         String.Format(CultureInfo.CurrentCulture,
                                      " InnerException Type: {0}, Message: {1}",
                                      ex.InnerException.GetType(),
                                      ex.InnerException.Message));

  System.Diagnostics.Debug.WriteLine(errorMessage);
}

answered Nov 5, 2011 at 17:37

sll

62.7k22 gold badges109 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Brian Mains · Accepted Answer · 2012-11-28 13:16:10Z

I have a piece of sql which is generating an exception and yet the try catch is not catching it - it is so weird!

Here is the code

try
{
    // Run the SQL statement, and then get the returned rows to the DataReader.

    SqlDataReader MyDataReader = MyCommand.ExecuteReader();

    //note AT THIS POINT there is an exception in MyDataReader
    //if I view MyDataReader in Watch I see this in base->ResultsView->base
    //Conversion failed when converting the varchar value 'lwang' to data type int
    //and errors count is 1 but there is no exception catch!!

    int iRow = 0;
    if (MyDataReader.HasRows)
    {
        int iCol = 0;
        while (MyDataReader.Read())
        {

            //dt.Load(MyDataReader);
            List<String> strFields = new List<String>();

            for (int iField = 0; iField < MyDataReader.FieldCount; iField++)
            {
                strReturn = strReturn + MyDataReader[iField] + ",";
                strFields.Add(MyDataReader[iField].ToString());
            }
            DataRows.Add(strFields);
            iRow++;
        }
    }
}
catch (Exception ex)
{
    //no exception is caught in this case!!  This code is never reached!!
    strError = "An error occurred getting the data table: " + MyCommand.CommandText + " " + ex.ToString();
    throw new Exception(strError);
    return (DataRows);
}
finally
{
    Connection.Close();
}
    return (DataRows);
}

In case it helps here is the sql being executed The sql being executed is:

select hec_recommendation.RecID, hec_recommendation.UtilityID, hec_recommendation.CatID, hec_recommendation.Condition, hec_recommendation.RecommendationText, hec_recommendation.Active, hec_recommendation.Ordinal, hec_recommendation.StartDate, hec_recommendation.EndDate, hec_recommendation.CreateDate, hec_recommendation.CreatedByID, hec_recommendation.ModifyDate, hec_recommendation.ModifyByID from hec_recommendation, hec_utility, hec_reccategory where hec_recommendation.utilityid = hec_utility.id and hec_recommendation.catid = hec_reccategory.catid and hec_reccategory.utilityid = hec_utility.utilityid and hec_utility.utilityId = 'lwang'

Collectives™ on Stack Overflow

Retrieving underlying exception from SqlDataReader for INSERT statment

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related