CODEWITHSUNDEEP
sql-server
Mike108
Mike108

Reputation: 2147

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

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?

Upvotes: 9

Views: 57913

Answers (4)

n8wrl
n8wrl

Reputation: 19765

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.

Upvotes: 1

Chinjoo
Chinjoo

Reputation: 2832

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();
        }

Upvotes: 12

Dean Harding
Dean Harding

Reputation: 72678

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

Upvotes: 23

Francisco Soto
Francisco Soto

Reputation: 10402

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

Upvotes: 0

Related Questions