Mixing
SQL Update not working for primary key
SQL Update not working for primary key
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.
Mobile
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?
Perhaps there is a trigger on the table that prevents updating the field or your database has column-level permissions.
– Gordon Linoff
Jun 29 at 16:47
i added table definition
– user3398379
Jun 29 at 16:49
Is there a foreign key on the Mobile column? If so, what is the update rule of that foreign key?
– Paul Williams
Jun 29 at 16:51
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables
– marc_s
Jun 29 at 17:02
4 Answers
4
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
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.
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.
yes what should i do?
– user3398379
Jun 29 at 16:59
i've updated the post, look see.
– Quibblesome
Jun 29 at 17:04
@user3398379, you should simply remove the mobile from the set of updated fields AND MORE IMPORTANTLY, drop using this style of string concatenation and use parameters. It was your luck to get the error this way.
– Cetin Basoz
Jun 29 at 17:05
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();
}
nope still not working this
– user3398379
Jun 29 at 17:17
What does "nope still not working" mean? What is the error message? Does that mobile number you enter exists? BTW, it would be easier if you used LINQ.
– Cetin Basoz
Jun 29 at 17:20
same happening. mobile number is not updating..
– user3398379
Jun 29 at 17:24
Oh my :) There is nothing to update there. You are searching by mobile number and updating other fields. If you need to update mobile number, then you should include it in the update list PLUS make sure you don't have any other constraints that prevent that. Otherwise you would need to disableenable constraint checking (Using mobile number as the primary key is not trivial for the novice).
– Cetin Basoz
Jun 29 at 17:28
i change my project and got a Auto incrementing ID,Then set that ID as primary key. now work is done
– user3398379
Jun 30 at 6:11
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Can you share the table definition?
– Afonso
Jun 29 at 16:47