SQL Update not working for primary key

Question

I write a statement for updating details belongs to primary key(Mobile). but it is working only for other columns. when i update mobile number. it doesn't change.

Here the my query

private void button4_Click(object sender, EventArgs e)
{
    con.Open();

    SqlCommand cmd = new SqlCommand(@"UPDATE [dbo].[Table]
SET [First] = '"+textBox1.Text+"',[Last] = '"+textBox2.Text+ "' ,[Mobile] = '" +textBox3.Text+ "'    ,[Email] = '" +textBox4.Text+ "' ,[Category] = '" + comboBox1.Text + "' WHERE (Mobile='" + textBox3.Text + "')", con);

    cmd.ExecuteNonQuery();

    con.Close();
    MessageBox.Show("Updated Successfully");
    display();
}

These following columns can be updated

First,
Last,
Email,
Category

but Mobile column cannot be updated.

CREATE TABLE [dbo].[Table]
(
    [First] VARCHAR(50) NOT NULL , 
    [Last] VARCHAR(50) NOT NULL, 
    [Mobile] VARCHAR(50) NOT NULL, 
    [Email] VARCHAR(50) NOT NULL, 
    [Category] VARCHAR(50) NOT NULL, 

    CONSTRAINT [PK_Table] PRIMARY KEY ([Mobile])
)

Could anybody tell me the error?

Answer 1

Your update to [mobile] doesn't make any sense because of the where clause. It states: "update all this stuff including the mobile where the mobile is this value. You're using textbox3.Text in both cases so it will never update because it is the same value.

e.g.

update foo set mobile = 'abc' where mobile = 'abc'

mobile will never be anything apart from 'abc' given that query.

If you want to fix this problem you need to actually design your table properly. Have a separate primary key:

PersonId int identity primary key  

then you can do:

update Person set mobile = 'abc' where PersonId = 1  

and store the PersonId somewhere where the user doesn't have to look at it.
If you're gonna query the table via Mobile a lot then consider adding a secondary index onto the Mobile column, if you need it to be unique add a unique constraint as well.

Also, have a GOOD read through WinForm DataBinding in general as the way your going around this is going to cause you problems later on. BindingContext is an important class to understand but will take some time to do so. You'll make this time back later IN SPADES, so do read on it.

Answer 2

It's kinda strange that you want to update your primary key rather than insert a new record. Anyway, check this answer that talks about Disabling constraint enforcement:

https://stackoverflow.com/a/2499328/1821637

Answer 3

In order to update the mobile number, you need to add an additional text box or otherwise supply the old mobile number.

If you are using a WPF or Windows form, then I would add a new form field for the old mobile number and pass that field to your where clause. This will work provided the old mobile number is an exact match. Given that you are doing varchar(50) then you may get errors for people who entered the data differently, such as (555) 555-5555 versus 555.555.5555.

Answer 4

As said already it doesn't make any sense to update the field to the same value you do the search on. Also you should never write SQL statements like that but use parameters:

private void button4_Click(object sender, EventArgs e)
{


SqlCommand cmd = new SqlCommand(@"UPDATE [dbo].[Table] SET 
      [First] = @first,
      [Last] = @last, 
      [Email] = @email,
      [Category] = @cat
  WHERE (Mobile=@mobile)", con);

cmd.Parameters.Add("@first", SqlDbType.VarChar).Value = textBox1.Text;
cmd.Parameters.Add("@last", SqlDbType.VarChar).Value = textBox2.Text;
cmd.Parameters.Add("@email", SqlDbType.VarChar).Value = textBox4.Text;
cmd.Parameters.Add("@cat", SqlDbType.VarChar).Value = comboBox1.Text;
cmd.Parameters.Add("@mobile", SqlDbType.VarChar).Value = textBox3.Text;

con.Open();
cmd.ExecuteNonQuery();
con.Close();

MessageBox.Show("Updated Successfully");
display();
}