Update in SQL query doesn't work c#

Question

I want to update data in my SQL Server table, this code here works fine in my other project but when I copied it to other project it doesn't work anymore.

Here's my code:

con.Open();

float prc = float.Parse(textBox4.Text);
int sum = int.Parse(textBox3.Text);

string sql = "UPDATE LIB_INVENTORY set  PRICE=(" + prc + "), QUANTITY=([QUANTITY]) + 
    (" + sum + "), BSTATUS='" + textBox5.Text + "' where BOOKNAME='" 
        + textBox1.Text + "' and PUBLISHER='" + textBox2.Text + "'";

SqlCommand cmd = new SqlCommand(sql, con);
cmd.ExecuteNonQuery();
con.Close();

MessageBox.Show("One item updated updated!");

It runs successfully but when I checked the table no data has been successfully updated. I checked my code but it is really the same as my other project that works fine. Can somebody help me?

Please [use Parameters](http://stackoverflow.com/questions/910465/avoiding-sql-injection-without-parameters).. — user2864740, Mar 14 '15 at 18:55
yes, every project has different database, i already checked the connection string and the table used in query. — Kristiani Abistado, Mar 14 '15 at 18:59

score 0 · Answer 1 · answered Mar 14 '15 at 19:13

if no error is there then it means where clause is not fulfilling. i think your has typed like :

where BOOKNAME='"<spaace>+ textBox1.Text+<spaace>"' and PUBLISHER='"<spaace>+ textBox2.Text +<spaace>"'";

so just erase space and try this out.

 string sql = "UPDATE LIB_INVENTORY set  PRICE=("+prc+"), QUANTITY=   ([QUANTITY]) + ("+sum+"), BSTATUS='"+textBox5.Text+"' where BOOKNAME='"+textBox1.Text+"' and PUBLISHER='"+textBox2.Text+"'";

score 0 · Answer 2 · answered Mar 14 '15 at 21:19

as suggested you should really use parameters for your sql query. On top of this do the following :

SqlCommand cmd = new SqlCommand(sql, con);
int nbrUpdates = cmd.ExecuteNonQuery();
con.Close();

if (nbrUpdates>0) MessageBox.Show("One item updated updated!");
else MessageBox.Show(sql);

You can then check if the string in the sql is correct. Also log in to your database manually and check if the data you want to update is in fact there. If it is and the update still does not work, make your code do a select statement for the data you want to update. You still might be accessing the wrong database.

Now to start using sql with parameters like you are supposed to read this :

http://www.csharp-station.com/Tutorial/AdoDotNet/lesson06

Update in SQL query doesn't work c#

2 Answers2