none
SQLException not having instance of command that resulted into it RRS feed

  • Question

  • Hi,

    I have 2 command getting executed on 2 different servers in same vb.net function.

    I want to log the errors in database in case of command execution failure. How ever SQLException does not consist of command name and text. Only way is I have to build it manually. Also when exception occurs there is no way to know which command execution has failed. Anyone any idea please help.

    Regards,

    HV

    Saturday, June 2, 2012 12:09 PM

Answers

  • a variant of Joel's answer...  You can use the often overlooked Data property of the exception.

    static void ExecAll(List<IDbCommand> commands)
    {
        try
        {
            foreach (var cmd in commands)
            {
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (System.Exception x)
                {
                    x.Data.Add("Failing SQL", cmd.CommandText);
                    throw; // NOT throw x;  !!!
                }
            }
        }
        catch (Exception x)
        {
            string sql = x.Data["Failing SQL"];
            // log it.
        }
    }
    


    This signature unintentionally left blank.

    • Proposed as answer by Mike FengModerator Tuesday, June 5, 2012 7:50 AM
    • Marked as answer by Hiral Thursday, June 7, 2012 5:19 AM
    Monday, June 4, 2012 10:11 AM

All replies

  • Your request is mising a lot of details to be able to give a good answer.  what class and methods are you using to send the commands to the servers.  There are a lot of methods you could be using.  Are these database commands and that is why you are getting an SQL exception.  Yo uare asking to store these exception in a database so are there 3 databases?   Two on the servers that you are sending commands and a third where you are storing the error messages?

    jdweng

    Saturday, June 2, 2012 2:37 PM
  • I use ExecuteNonQuery and ExecuteScalar.

    I have a custom function say, MyExecuteNonQuery which takes collection of SQLCommand as parameter and executes it on same server with same connectionstring. However in the collection there are different commands and any one can give error. I would like to know which one has thrown SQLException and what is the text along with parameters for it.

    Execution as well as error logging is done on the same server, same database.

    Monday, June 4, 2012 4:50 AM
  • You could do something like below.  Use the same variable for each SQL statement.  when you get the exception the error string will always be the variable commandstring.

    Try

    {

       commandstring = "Select ......."

       mydatabase.command(commandstring)

       commandstring = "Select ......."

       mydatabase.command(commandstring)

       commandstring = "Select ......."

       mydatabase.command(commandstring)

    }

    catch(exception e)

    {

       console.write("SQL Error - command string failed : " + commandstring);

    }


    jdweng

    Monday, June 4, 2012 9:11 AM
  • a variant of Joel's answer...  You can use the often overlooked Data property of the exception.

    static void ExecAll(List<IDbCommand> commands)
    {
        try
        {
            foreach (var cmd in commands)
            {
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (System.Exception x)
                {
                    x.Data.Add("Failing SQL", cmd.CommandText);
                    throw; // NOT throw x;  !!!
                }
            }
        }
        catch (Exception x)
        {
            string sql = x.Data["Failing SQL"];
            // log it.
        }
    }
    


    This signature unintentionally left blank.

    • Proposed as answer by Mike FengModerator Tuesday, June 5, 2012 7:50 AM
    • Marked as answer by Hiral Thursday, June 7, 2012 5:19 AM
    Monday, June 4, 2012 10:11 AM
  • Thank you Nick. Information was very useful. It has saved me from writing too much code of errorhandling.
    Thursday, June 7, 2012 5:19 AM
  • Thank you Mike.
    Thursday, June 7, 2012 5:19 AM