How to know whether a sql Update statement executed successfully or failed ?

Question

How to know whether a sql Update statement executed successfully or failed ?

I use sql server 2005 and C# asp.net.

Can I get the successful or failed infomation in C# without adding some sql code into the old sql statement?

Answer 1

Using a TRY/CATCH block and maybe RAISERROR to send a message.

http://msdn.microsoft.com/en-us/library/ms179296.aspx

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Answer 2

You can use @@ROWCOUNT to get the number of rows affected by the last query. This can be used to decide whether your WHERE clause actually matched something, for example.

UPDATE mytable SET
  field = 'SomeValue'
WHERE
  id = 1234

IF @@ROWCOUNT = 0
BEGIN
   --  No row with id=1234
END

Answer 3

You can use the return value of the ExecuteNonQuery to check if the update was successful or not.

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        SqlCommand cmd = new SqlCommand("sp_updateData", con);
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter p1 = new SqlParameter("@id", SqlDbType.Int);
        p1.Value = 1;
        p1.Direction = ParameterDirection.Input;

        SqlParameter p2 = new SqlParameter("@name", SqlDbType.VarChar,50);
        p2.Value = "sls";
        p2.Direction = ParameterDirection.Input;

        cmd.Parameters.Add(p1);
        cmd.Parameters.Add(p2);

        try
        {
            con.Open();
            //count will be the number of rows updated. will be zero if no rows updated.
            int count = cmd.ExecuteNonQuery();
            if (count > 0)
            {
                Console.WriteLine("Update Success!!!");
            }
            else
            {
                Console.WriteLine("No Updates!!!");
            }
            Console.ReadLine();

        }
        catch (SqlException ex)
        {
            Console.WriteLine("Update Failed coz.. " + ex.Message);
        }
        finally
        {
            con.Close();
        }

Answer 4

What does 'failed' mean?

If by failed you mean an error was generated - SQL Syntax, constraint- or FK-violation - then TRY/CATCH, RAISEERROR, etc. are options.

Or, if by failed you mean no rows were updated, then the return value of ExecuteNonQuery will give you a rowcount IF you're not suppressing rowcount in your stored procedure.