CODEWITHSUNDEEP
c#sql
dumbel
dumbel

Reputation: 93

SQL update not working

I am trying to update my database using SQL commands but it is not working.

id = primarykey

public void updateName(int id, string name)
{ 
    using(var cmd = new SqlCommand("UPDATE person SET name = @name where id = @id", connect()))
    {
        cmd.Parameters.AddWithValue("@id", id);
        cmd.Parameters.AddWithValue("@name",name);
        cmd.ExecuteNonQuery();
    }
}

private SqlConnection connect()
{
    string conStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    var c = new SqlConnection(conStr);
    c.Open();
    return c;
}

What have I missed?

Upvotes: 2

Views: 3965

Answers (2)

Oleg Dok
Oleg Dok

Reputation: 21776

  • See if the DB you connect is the right DB, not test or whatever, and the same for the server

  • Rewrite query as

    UPDATE person SET name = @name where id = @id; 
IF @@ROWCOUNT = 0 
  RAISERROR('No rows updated', 16,1)

  • check if there are exists some empty try ... catch{} in call stack

  • see if there exists other person tables in different schemas
  • see if there is a trigger on the table and what it does
  • see if any other processes are changing the same table
  • see if an application hits this code while running

Upvotes: 2

Sergii Kudriavtsev
Sergii Kudriavtsev

Reputation: 10532

You should change the parameters addition order - with most providers it DOES matter.

So it should be not

    cmd.Parameters.AddWithValue("@id", id);
    cmd.Parameters.AddWithValue("@name",name);

but rather

    cmd.Parameters.AddWithValue("@name",name);
    cmd.Parameters.AddWithValue("@id", id);

General rule: the order of calls to cmd.Parameters.AddWithValue should be the same as the order of parameter occurence in the query.

Upvotes: 3

Related Questions