How to find out if sql update query was successfully executed or completed?

Question

How to find out if sql update query was executed successfully or failed?

I am using sql server 2005 and c # asp.net.

Can I get successful or unsuccessful information in C # without adding sql code to the old sql statement?

Answer 1

You can use the ExecuteNonQuery return value 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 2

You can use @@ ROWCOUNT to get the number of rows affected by the last query. This can be used to decide if your proposal really WHEREmatched something, for example.

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

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

Answer 3

""?

, , - SQL, FK- - TRY/CATCH, RAISEERROR .. .

Or, if you mistakenly indicated that no rows were updated, then the return value of ExecuteNonQuery will give you the row number if you do not suppress the number of rows in the stored procedure.

Answer 4

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

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

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