question

BojanSerafimoski-5906 avatar image
0 Votes"
BojanSerafimoski-5906 asked BojanSerafimoski-5906 commented

How to deduct product quantity from database after order is proceed

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.















dotnet-csharpdotnet-aspnet-webforms
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Q&A has tags for ASP.NET that you can post to for help.

0 Votes 0 ·

I know, there was some kind of error whenever I tried to include any other tags except this one.. I don't know why..

0 Votes 0 ·
cooldadtx avatar image
1 Vote"
cooldadtx answered

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
   ....
}



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BojanSerafimoski-5906 avatar image
0 Votes"
BojanSerafimoski-5906 answered cooldadtx commented

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();
}

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

You need to move the quantity into your dictionary variable so it is properly referenced by Dapper. You also need to set the quantity before you create that dictionary.

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


Step into the code and set a breakpoint on the assignment to quantity. Make sure the value is right. If it isn't then your UI binding is wrong.

0 Votes 0 ·
BojanSerafimoski-5906 avatar image
0 Votes"
BojanSerafimoski-5906 answered BojanSerafimoski-5906 commented

Hello @cooldadtx.
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()));
                 }
    
             }
             
         }


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

This comes first in the code then afterwards the code that I sent you for decrementing the value.

0 Votes 0 ·
BojanSerafimoski-5906 avatar image
0 Votes"
BojanSerafimoski-5906 answered BojanSerafimoski-5906 commented

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BojanSerafimoski-5906 ,
Do you have solved your problems now? If you still have problems,you could post to us.
Best regards,
Yijing Sun

0 Votes 0 ·

Thank you @YijingSun-MSFT I solved my problem.
Best regards

0 Votes 0 ·