UPDATING the table

Question

Hi I am just new in this language C# and MySQL .. and I don't know why the value of In and Unit_Value is still 0.
I want to update the column and whenever I add an Item, the quantity of that item should be added to the current value.

This is what I want to Happen: In = (Quantity*Unit_Value)and should be added to the current value. Unit_Value = textBox3.Text(Unit_Value is a fixed value from database in combobox, i get it by the name of selecteditem in combobox).

This is my code:

string constring1 = "datasource=localhost;port=;username=;password=;database=;";
MySqlConnection conDataBase1 = new MySqlConnection(constring1);
MySqlCommand mycommand = new MySqlCommand("select `Unit_Value` from `tbl_inventory_item_unit` where `Unit_Name`='"+metroComboBox2.Text+"';",conDataBase1);
MySqlCommand myincommand = new MySqlCommand (
    "UPDATE `tbl_inventory_in` SET `In`= `In` + '" + metroLabel12.Text + 
    "',`In_Quantity`='" + textBox3.Text + 
    "',`Unit_Value`='" + metroLabel11.Text + 
    "',`Unit_Name`='"+ metroComboBox2.Text + 
    "',`In_Date`='"+ metroLabel8.Text + 
    "'where `Item_ID`='" + textBox4.Text + 
    "';",conDataBase1);

string b;
int c;
int a;

try {
    conDataBase1.Open();
    MySqlDataReader myReader1 = mycommand.ExecuteReader();
    while (myReader1.Read()) {
        b = myReader1.GetValue(0).ToString();
        // c = Int32.Parse(b);
        // c = Convert.ToInt32(c);
        c = Int32.Parse(b);
        a = Int32.Parse(textBox3.Text);
        metroLabel11.Text = c.ToString();
        metroLabel12.Text = (a * c).ToString();
    }
    myReader1.Close();
    MySqlDataReader myinreader = myincommand.ExecuteReader();
    while (myinreader.Read()) {
    }
    MessageBox.Show("Stocks Added");
    conDataBase1.Close();
}

Please read this answer and the linked articles. they describe how to avoid SQL injection attacks, which the above code is currently vulnerable to: http://stackoverflow.com/a/14376963/1991296 — Colin Nichols, Mar 09 '17 at 00:26

score 0 · Answer 1 · answered Mar 09 '17 at 00:57

Do it this way:

String connectionString = "This is your connection string";  
public static int updateTable(string query)
        {

            using (MySqlConnection con = new MySqlConnection(connectionString))
            {
                con.Open();
                using (MySqlCommand cmd = con.CreateCommand())
                {
                   cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    int result = cmd.ExecuteNonQuery();
                    return result;
                }
            }
        }

To use this:
 updateTable("update yourtable set column1=value1, column2 = value2 where primarycolumn = primaryKeyValue");

UPDATING the table

1 Answers1