Reputation: 107
I am creating a database query and when I add data to database it is updating all rows with a value I have inserted. The code:
string SQL = "Update Stock_Entry Set No_of_Items=No_of_Items+" (Convert.ToDecimal(textBox_Stock.Text));
DBConnection database = new DBConnection();
int rslt = database.updatetValues(SQL);
if (rslt > 0)
I found I have a serious problem in that I never mentioned which row to update. So i edited my coding to
string SQL = "Update Stock_Entry Set No_of_Items=No_of_Items+" + (Convert.ToDecimal(textBox_Stock.Text)) +
"Where Item_Code="+ textBox_ItemId.Text ;
DBConnection database = new DBConnection();
int rslt = database.updatetValues(SQL);
if (rslt > 0)
This method is giving me an error "unable to convert varchar value to int" and mentioning my item code having a varchar value. So i tried this way after removing the varchar item code and this method is working when i don't have any varchar value in item code. But I can't keep my item code column as int as I have to give an alpha character in the item code.
Upvotes: 1
Views: 48
Reputation: 236328
Your current code is vulnerable to SQL Injection attacks. You should use SQL parameters to protect your application from SQL injection:
string sql = @"UPDATE Stock_Entry
SET No_of_Items = No_of_Items + @stock
WHERE Item_Code = @itemId";
Then provide values for parameters (sample with SqlCommand):
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@stock", Convert.ToDecimal(textBox_Stock.Text));
command.Parameters.AddWithValue("@itemId", textBox_ItemId.Text);
command.ExecuteNonQuery();
Upvotes: 0
Reputation: 1245
I presume Item_Code is a Varchar field in the database?
You will need to edit your code to this:
string SQL = "Update Stock_Entry Set No_of_Items=No_of_Items+" + (Convert.ToDecimal(textBox_Stock.Text)) +
"Where Item_Code='"+ textBox_ItemId.Text + "'";
Because you want a varchar value in the where clause, you need to put the value between apostrophes like this:
WHERE Item_Code = 'theitem'
Upvotes: 2