Updating Value In Database

Question

I have a database table called Product. Inside, there's ID, ProductName, Brand, Description, Price, Cost and Count. I would like to update the Count value using a textbox.

For example, if I type in a value in the textbox, and there is already 10 under the Count, the updated value should be the value in the textbox + 10.

Here are my codes:

protected void btnAdd_Click(object sender, EventArgs e)
    {
        string productName = ddlName.Text;
        string quantity = tbQuantity.Text;

        string strConnectionString = ConfigurationManager.ConnectionStrings["TrimberlandConnectionString"].ConnectionString;
        SqlConnection myConnect = new SqlConnection(strConnectionString);
        string strCommandText = "Select ProductName FROM Product WHERE ProductName = @ProductName";
        SqlCommand cmd = new SqlCommand(strCommandText, myConnect);
        cmd.Parameters.AddWithValue("@ProductName", productName);
        myConnect.Open();
        SqlDataReader reader = cmd.ExecuteReader();

        addStocks(productName, quantity);

        reader.Close();
        myConnect.Close();
    }

    private void addStocks(string productName, string quantity)
    {
        string strConnectionString = ConfigurationManager.ConnectionStrings["TrimberlandConnectionString"].ConnectionString;
        SqlConnection myConnect = new SqlConnection(strConnectionString);
        string strCommandText = "UPDATE Product SET Count = Count + 1 ";

        SqlCommand cmd = new SqlCommand(strCommandText, myConnect);
        cmd.Parameters.AddWithValue("@ProductName", productName);
        cmd.Parameters.AddWithValue("@Count", quantity);

        myConnect.Open();

        int result = cmd.ExecuteNonQuery();

        if (result > 0)
        {
            MessageBox.Show("Stocks have successfully been added.");
        }

        myConnect.Close();
    }

Answer 1

Your code should be:

protected void btnAdd_Click(object sender, EventArgs e)
{
    string productName = ddlName.Text;
    string quantity = tbQuantity.Text;
    string product_id = "";

    string strConnectionString = ConfigurationManager.ConnectionStrings["TrimberlandConnectionString"].ConnectionString;
    SqlConnection myConnect = new SqlConnection(strConnectionString);
    string strCommandText = "Select ID FROM Product WHERE lower(ProductName) = lower(@ProductName)";
    SqlCommand cmd = new SqlCommand(strCommandText, myConnect);
    cmd.Parameters.AddWithValue("@ProductName", productName);
    myConnect.Open();
    product_id = cmd.ExecuteScalar() == null ? "" : cmd.ExecuteScalar().ToString();
    myConnect.Close();
    if (!String.IsNullOrEmpty(product_id))
        addStocks(product_id, quantity);
}

private void addStocks(string productID, string quantity)
{
    string strConnectionString = ConfigurationManager.ConnectionStrings["TrimberlandConnectionString"].ConnectionString;
    SqlConnection myConnect = new SqlConnection(strConnectionString);
    string strCommandText = "UPDATE Product SET Count = Count + @val where ID = @PID ";

    SqlCommand cmd = new SqlCommand(strCommandText, myConnect);
    cmd.Parameters.AddWithValue("@PID", productID);
    cmd.Parameters.AddWithValue("@val", quantity);
    try{
        myConnect.Open();
        cmd.ExecuteNonQuery();
        MessageBox.Show("Stocks have successfully been added.");
    }
    catch(Exception ex){
        //work with exception
    }
    finally{
        myConnect.Close();
    }
}