none
ODBC error checking RRS feed

  • Question

  •  

    I'm trying to find out how to capture ODBC errors in C#.  Is there a set of methods used for this?  C++ had various checks such as:

     

    SQLState, NativeError, Message, SQL_NO_DATA_FOUND, SQL_INVALID_HANDLE, SQL_ERROR

     

    All of these put together would help the developer to isolate where the problem for any transaction was occurring.  How is that done in C# ?

     

    If you happened to be interested, I posted a question showing the previous method I used for all these checks:

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3217097&SiteID=1

     

    I didn't get any answers there so I'm content to get any input here as to the advised approach.

     

    Thanks

    Friday, April 25, 2008 6:39 PM

Answers

  • well if there are errors thrown by the connection source/data source then you place it in a try catch block and catch the specific exception, such as SQLException. When caught, look at the exception instance, it should give you everything you need to know about the error that occured.

     

    try

    {

       ..

       mySqlCommand.ExecuteNonQuery(); //lets say there is a duplicate record that exists, an exception will be thrown

    }

    catch (SqlException ex)

    {

       //check ex

    }

     

    is this what you are after?
    Friday, April 25, 2008 6:50 PM
  • OK, sure.

     

    Well you only need one try catch block around the code you are going to execute which you know may throw an exception. Thats all.

     

    you need to look at the ErrorCode/ErrorNumber and match it up to whatever error is thrown. The description of the error is given in the InnerException or the Message property of the exception instance.

     

    there isnt a thing as no data found. if there is no data in a table, then nothing gets returned. The way to check this is as follows:

     

    method 1: use a DataSet

     

    DataSet ds = new DataSet();

     

    using (SqlCommand command = new SqlCommand("SELECT * FROM [table]", new SqlConnection(connectionString))

    {

       SqlDataAdapter adapter = new SqlDataAdapter(command);

     

       try

       {

          command.Connection.Open();

          adapter.Fill(ds); //fills the records

          command.Connection.Close();

          if (ds.Tables.Count == 0)

          {

             //no records

          }

       }

       catch (SqlException ex)

       {

          MessageBox.Show(ex.ToString());

       }

       finally

       {

          if (command.Connection.State == ConnectionState.Open)

          {

             command.Connection.Close();

          }

       }

    }

     

     

    method 2: use a SqlDataReader. This is a fast forward only reader

     

     

    using (SqlCommand command = new SqlCommand("SELECT * FROM [table]", new SqlConnection(connectionString))

    {

       try

       {

          command.Connection.Open();

          SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

          if (reader.HasRows)

          {

             //there is data to be read.

          }

        

          command.Connection.Close();

       }

       catch (SqlException ex)

       {

          MessageBox.Show(ex.ToString());

       }

       finally

       {

          if (command.Connection.State == ConnectionState.Open)

          {

             command.Connection.Close();

          }

       }

    }

     

     

     

    if there is an exception thrown, again say duplicate key or maybe the server couldnt be contacted, a SqlException is thrown (or OleDbException if you are using the OleDb classes and I believe same with Odbc). To see what the error is in a string, do a .ToString() on the exception, as shown above, or to find out where it came from, look at the InnerException to see further details about the exception in depth.

     

    the ErrorNumber evaluates/maps up to the SQL Error Codes I believe.

     

    does this help?

    Friday, April 25, 2008 7:36 PM

All replies

  • well if there are errors thrown by the connection source/data source then you place it in a try catch block and catch the specific exception, such as SQLException. When caught, look at the exception instance, it should give you everything you need to know about the error that occured.

     

    try

    {

       ..

       mySqlCommand.ExecuteNonQuery(); //lets say there is a duplicate record that exists, an exception will be thrown

    }

    catch (SqlException ex)

    {

       //check ex

    }

     

    is this what you are after?
    Friday, April 25, 2008 6:50 PM
  • >>  is this what you are after?

     

    Yes, definitely.  Is it possible to get you to show me what the try catch block(s) would look like for all these:

     

    SQLState, NativeError, Message, SQL_NO_DATA_FOUND, SQL_INVALID_HANDLE, SQL_ERROR

     

    In other words I'm not very familar with try catch blocks.  It looks as though the first step would the statement:

     

    try

    {

    mySqlCommand.ExecuteQuery();

    }

     

    but in order to analyze all the ones listed above (SQLState - SQL_ERROR) how many catch blocks would be necessary?

     

    Thanks

    Friday, April 25, 2008 7:03 PM
  • OK, sure.

     

    Well you only need one try catch block around the code you are going to execute which you know may throw an exception. Thats all.

     

    you need to look at the ErrorCode/ErrorNumber and match it up to whatever error is thrown. The description of the error is given in the InnerException or the Message property of the exception instance.

     

    there isnt a thing as no data found. if there is no data in a table, then nothing gets returned. The way to check this is as follows:

     

    method 1: use a DataSet

     

    DataSet ds = new DataSet();

     

    using (SqlCommand command = new SqlCommand("SELECT * FROM [table]", new SqlConnection(connectionString))

    {

       SqlDataAdapter adapter = new SqlDataAdapter(command);

     

       try

       {

          command.Connection.Open();

          adapter.Fill(ds); //fills the records

          command.Connection.Close();

          if (ds.Tables.Count == 0)

          {

             //no records

          }

       }

       catch (SqlException ex)

       {

          MessageBox.Show(ex.ToString());

       }

       finally

       {

          if (command.Connection.State == ConnectionState.Open)

          {

             command.Connection.Close();

          }

       }

    }

     

     

    method 2: use a SqlDataReader. This is a fast forward only reader

     

     

    using (SqlCommand command = new SqlCommand("SELECT * FROM [table]", new SqlConnection(connectionString))

    {

       try

       {

          command.Connection.Open();

          SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

          if (reader.HasRows)

          {

             //there is data to be read.

          }

        

          command.Connection.Close();

       }

       catch (SqlException ex)

       {

          MessageBox.Show(ex.ToString());

       }

       finally

       {

          if (command.Connection.State == ConnectionState.Open)

          {

             command.Connection.Close();

          }

       }

    }

     

     

     

    if there is an exception thrown, again say duplicate key or maybe the server couldnt be contacted, a SqlException is thrown (or OleDbException if you are using the OleDb classes and I believe same with Odbc). To see what the error is in a string, do a .ToString() on the exception, as shown above, or to find out where it came from, look at the InnerException to see further details about the exception in depth.

     

    the ErrorNumber evaluates/maps up to the SQL Error Codes I believe.

     

    does this help?

    Friday, April 25, 2008 7:36 PM
  • That will do it!  Appreciate the detail !

     

    John

     

    Friday, April 25, 2008 7:51 PM
  • no worries, glad I could help you, please DO post more questions if you need more help Smile

     

    Friday, April 25, 2008 7:52 PM