CODEWITHSUNDEEP
c#sqldatabasewinforms
Xeroth
Xeroth

Reputation: 107

Issue while adding a value to database in already available column

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

Answers (2)

Sergey Berezovskiy
Sergey Berezovskiy

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

Brendan
Brendan

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

Related Questions