SQL update query not updating table data

Question

Here is my code and it shows successful but it does not update row in table.

cm = new SqlCommand("update table_products set pname = @pname, pdesc = @pdesc, bid = @bid, cid = @cid, cost = @cost, price = @price, reorder = @reorder where pcode like @pcode", con);

cm.Parameters.AddWithValue("@pcode", tft_pcode.Text);
cm.Parameters.AddWithValue("@barcode", tft_barcode.Text);
cm.Parameters.AddWithValue("@pname", tft_pname.Text);
cm.Parameters.AddWithValue("@pdesc", tft_pdescription.Text);
cm.Parameters.AddWithValue("@bid", bid);
cm.Parameters.AddWithValue("@cid", cid);
cm.Parameters.AddWithValue("@cost", Double.Parse(tft_cost.Text));
cm.Parameters.AddWithValue("@price", Double.Parse(tft_price.Text));
cm.Parameters.AddWithValue("@reorder", int.Parse(tft_reorder.Text));

cm.ExecuteNonQuery();
con.Close();

MessageBox.Show("Product updated successfully");

You should check out [Can we stop using AddWithValue() already?](http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) and stop using `.AddWithValue()` - it can lead to unexpected and surprising results... — marc_s, May 31 '20 at 15:56
What does your `tft_pcode.Text` value look like? Do you get any results when you do a `SELECT * FROM dbo.table_products WHERE pcode LIKE (that value)` ?? — marc_s, May 31 '20 at 16:10
@marc_s though I'd prefer AddWithValue to SQL injectability any day! — Caius Jard, May 31 '20 at 16:41

score 1 · Accepted Answer · answered May 31 '20 at 16:22

1

cm.ExecuteNonQuery(); returns the number of rows affected. In your case it's probably 0 (but still worth getting the value and checking).

In your case, it sounds like where pcode like @pcode on the end of your query, is not matching anything in the table.

var rowsUpdated = cm.ExecuteNonQuery();
con.Close();

if(rowsUpdate>0) MessageBox.Show("Product updated successfully");
else MessageBox.Show("Product NOT updated successfully");

answered May 31 '20 at 16:22

Neil

11,059
3
31
56

i have tried pcode = @pcode also tried comparing pcode with value from textbox_pcode, but still not helpful – hot spot May 31 '20 at 16:24
1

That's not the issue, it's the VALUE of pcode (or rather the value of `tft_pcode.Text`) that doesn't match anything in the table. – Neil May 31 '20 at 16:25
Implement the change recommended by Neil and tell us the result – Caius Jard May 31 '20 at 16:42
Thanks for your help you were correct it wasnt matching anything because i had issue that when this form was loading it was creating new pcode and i was trying matching it with old value. Anyway i have fixed the issue – hot spot May 31 '20 at 16:44

score 0 · Answer 2 · answered May 31 '20 at 16:49

If you're using a file based database like a sql server mdf file that is being dynamically attached to your db server when you run your project you should be aware that the db your program is updating is not necessarily the same file you're looking in with your management studio. Take a look at your connection string- if it mentions DataDirectory then it's likely it's the database file in the bin/Debug or bin/Release (depending on your active build config) folder that is being updated, not the one in your project folder. The db in your project folder is copied out to bin/* every tine you run your project. This has classically been an awesome cause of "my program says it updates my db but it doesn't" - either the db is being replaced with a clean one every time the program is run or the dev is looking in one db file and the program is updating another

score 0 · Answer 3 · answered May 31 '20 at 17:31

I solved the issue The problem was that i was trying to match pcode(application) with pcode(database), which wasn't doing so. That was because when my update form was loading i had the pcode generate method in its event, so i was matching with old pcode value , what it actually was doing is that, it was matching with newly generated pcode. So i removed pcode generate method from form_load event and the issue was solved. Thank you All for your time.

SQL update query not updating table data

3 Answers3