How to deduct product quantity from database after order is proceed

Question

Hello guys.
I have 2 asp.net WebForms, WebForm1 contains a button that redirects into WebForm2 which contains a contact form that needs to be filled to proceed an order.
I have a drop down list in it that is connected to the database, and depending on which product a button on the WebForm1 is clicked, the current quantity is displayed from the specific product from the database.
After the ordering, I need to decrease/deduct the product quantity from the database depending on how many products on the drop down list were selected.

How to decrease the product quantity after the order is proceed?
Here is the code that takes the quantity from the DataBase:

        string productName = Request.QueryString["productname"];
        txt_product13.Text = productName;



        var dictionary = new Dictionary<string, object>
        {
    { "@ProductName", productName }
          };

        var parameters = new DynamicParameters(dictionary);
        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;

        using (var connection = new SqlConnection(CS))
        {
            connection.Open();
            var sql = "SELECT * FROM ProductsDB WHERE ProductName = @ProductName";
            var product = connection.QuerySingle<Product>(sql, parameters);

// this applies the currency on the Price field.

            CultureInfo EuroCulture = new CultureInfo("fr-FR");
            txt_productprice.Text = product.Price.ToString("c", EuroCulture);

// this fills the Quantity drop drop down list.

            for (int i = 1; i <= product.Quantity; i++)
            {
                dropdownlist1.Items.Add(new ListItem(i.ToString(), i.ToString()));
            }

        }

    }

For now, I have this code for decreasing but I can't get it why it does not decrease

        string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
        string productName = Request.QueryString["productname"];
        using (var connection = new SqlConnection(CS))
        {
            connection.Open();
            var sql = "UPDATE ProductsDB SET Quantity = WHERE ProductName = @ProductName" + dropdownlist1.SelectedValue + "'";
            connection.Close();
        }

Any help is welcome, thank you.

Answer 1

Your SQL command is wrong. It is also a prime candidate for SQL injection. Please never, never do this on any query anywhere. This is always wrong.

First the SQL command issue. The syntax for an update is UPDATE <table> SET { <column> = <value> }+ WHERE <condition>. In your query you don't set quantity to any value. This is going to generate a SQL error if you were to run the query itself. Furthermore you are appending the value of a dropdown to the product name clause and this is going to be wrong. You need 2 pieces of data here: new quantity and product name.

The SQL injection issue is because you never, ever concatenate values from a user into a SQL command. This makes it incredibly easy to completely destroy your database. Always use parameters, even when you trust the input.

The final issue is that you create a SQL command but never execute it. Hence the quantity will not change. I'm not sure what data access library you're using so I don't know the exact command you need to run.

using (var connection = new SqlConnection(CS))
{
   connection.Open();

   // Based upon your UI it appears you are displaying the quantity in a dropdown, so if the user selects 3 then they want 3 items so you 
   // need to subtract that from the current quantity (note you should probably ensure that multiple users don't subtract more quantity then you have)
   var quantity = (int)dropdownlist1.SelectedValue;   //Assuming it is an int here
   var productName = "?";
   var cmd = new SqlCommand("UPDATE ProductsDB SET Quantity = Quantity - @quantity WHERE ProductName = @ProductName", conn);
   cmd.Parameters.AddWithValue("@quantity", quantity);
   cmd.Parameters.AddWithValue("@productName", productName);

   //Do the actual update
   conn.ExecuteNonquery(cmd);

   //Get the updated quantity from the DB using your regular query
   ....
}

Answer 2

Thank you for the answer, but since i am using Dapper, this code somehow does not work for me, the conn throws an error, as well as the executenonquery can't be found, so I came to this problem now.
It decrements the value from my database only by 1, no matter what quantity i choose from the drop down list, I cant figure out why..

Here is my code now

string productName = Request.QueryString["productname"];
txt_product13.Text = productName;
var dictionary = new Dictionary<string, object>
{
{"@ProductName", productName }
};
var parameters = new DynamicParameters(dictionary);
string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
using (var connection = new SqlConnection(CS))
{
int val = Convert.ToInt32(dropdownlist1.SelectedItem.Text);
connection.Open();
var sql = "UPDATE ProductsDB SET Quantity = Quantity - " + val + " WHERE ProductName = @ProductName";
connection.Execute(sql, parameters);
connection.Close();
}

Answer 3

Hello @Michael Taylor .
So I tried your method but again, it does not works.
Upper in the code I have the same approach for getting the currency and I have one "for" for getting the quantity from database starting from 1 to its max value.
In case it matters, here is my code, I tried many methods and approaches but it deducts the quantity by 1 every time, I am really desperate.

protected void Page_Load(object sender, EventArgs e)  
        {  
            string productName = Request.QueryString["productname"];  
            txt_product13.Text = productName;  
  
            var dictionary = new Dictionary<string, object>  
            {  
        { "@ProductName", productName }  
              };  
  
            var parameters = new DynamicParameters(dictionary);  
            string CS = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;  
  
            using (var connection = new SqlConnection(CS))  
            {  
                connection.Open();  
                var sql = "SELECT * FROM ProductsDB WHERE ProductName = @ProductName";  
                var product = connection.QuerySingle<Product>(sql, parameters);  
                CultureInfo EuroCulture = new CultureInfo("fr-FR");  
                txt_productprice.Text = product.Price.ToString("c", EuroCulture);  
  
                for (int i = 1; i <= product.Quantity; i++)  
                {  
                    dropdownlist1.Items.Add(new ListItem(i.ToString(), i.ToString()));  
                }  
  
            }  
           
        }  

Answer 4

The clearing method of the drop down list dropdownlist1.SelectedIndex = -1; upper in the code was the problem.
However, thank you.