How to deduct product quantity from database after order is proceed

Bojan Serafimovski

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 fills several TextBoxes, inserts currency and fills the drop down list "Quantity" depending on which product on the WebForm1 the button is clicked:

   protected void Page_Load(object sender, EventArgs e)
    {
        string productName = Request.QueryString["productname"];
        txt_product13.Text = productName;

        var dictionary = new Dictionary
        {
    { "@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(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()));
            }

        }
     
    }

For now I have this code for decreasing, can you tell where is my mistake and why does it not decrement the quantity from database?

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

14 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
Accepted answer
okk, so your text is 1,2,3 like this, and i don't know about values so it doesn't matter the below code should work for 100%
1. var dictionary = new Dictionary { { "@ProductName",productName } };
2. var parameters = new DynamicParameters(dictionary);
4. using (var connection = new SqlConnection(CS))
5. {
6. int val= Convert.ToInt32(dropdownlist1.SelectedItem.Text);
7. connection.Open();
8. var sql = "UPDATE ProductsDB SET Quantity = Quantity - " + val+ " WHERE ProductName = @ProductName";
9. connection.Execute(sql,parameters);
11. connection.Close();
12. }
Bojan Serafimovski5y ago
The clearing method of the drop down list dropdownlist1.SelectedIndex = -1; upper in the code was the problem.
However, thank you.
Bojan Serafimovski5y ago
I can't get it what is the problem, it is again the same..

Here is my whole C# code on the page, I may be wrong somewhere upper on the code with some string or integer so it may confuses it..

Here is my code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Configuration;

using System.Drawing;

using System.Net;

using System.Net.Mail;

using System.Text;

using System.Data.SqlClient;

using Dapper;

using System.Globalization;

namespace GraduationThesis

{

public partial class ShopNowPage : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

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

txt_product13.Text = productName;

var dictionary = new Dictionary

{

{ "@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(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()));

}

}

}

public class Product

{

public int ProductID { get; set; }

public string ProductName { get; set; }

public decimal Price { get; set; }

public int Quantity { get; set; }

}

protected void button11_Click(object sender, EventArgs e)

{

try

{

var from = "email1";

var to = "email2";

const string Password = "password";

string mail_subject = txt_subject13.Text.ToString();

string mail_message = "Name and Surname: " + txt_nameandsurname13.Text + "\n";

mail_message += "Your telephone number: " + txt_phone13.Text + "\n";

mail_message += "Your address: " + txt_address13.Text + "\n";

mail_message += "Your e-mail: " + txt_email13.Text + "\n";

mail_message += "Product name: " + txt_product13.Text + "\n";

mail_message += "Product price: " + txt_productprice.Text + "\n";

mail_message += "Quantity of the product: " + dropdownlist1.SelectedValue + "\n";

mail_message += "Payment method: " + dropdownlist13.SelectedValue + "\n";

var smtp = new SmtpClient();

{

smtp.Host = "smtp.gmail.com";

smtp.Port = 587;

smtp.EnableSsl = true;

smtp.DeliveryMethod = SmtpDeliveryMethod.Network;

smtp.Credentials = new NetworkCredential(from, Password);

smtp.Timeout = 20000;

}

smtp.Send(from, to, mail_subject, mail_message);

confirm.Text = "Thank you for your order, our team will deliver the product you ordered at your home address as soon as possible.";

txt_nameandsurname13.Text = "";

txt_phone13.Text = "";

txt_address13.Text = "";

txt_email13.Text = "";

txt_subject13.Text = "";

txt_product13.Text = "";

dropdownlist1.SelectedIndex = -1;

dropdownlist13.SelectedIndex = -1;

}

catch (Exception)

{

confirm.Text = "The order can't be proceed at the moment, please try again later.";

confirm.ForeColor = Color.Red;

}

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

txt_product13.Text = productName;

var dictionary = new Dictionary

{

{"@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();

}

}

}

}
Bojan Serafimovski5y ago
So depending on which product i pressed a button on the main webform the drop down list changes, for example this is the mobile Meizu Pro, for which I have 6 in quantity in database, here it is how it looks.
Whatever value i choose and proceed with order, it only decrements one.
For example if i continue with my order like this there will be 5 in the database.
Sachin Singh5y ago
show me your dropdown list
Bojan Serafimovski5y ago
Still the same, only decrements one, any other ideas?

Sachin Singh5y ago

ok, test this


var sql = "UPDATE ProductsDB SET Quantity = Quantity - " + dropdownlist1.SelectedItem.Value+ 
" WHERE ProductName = @ProductName";

Bojan Serafimovski5y ago
I just saw that whatever quantity form the drop down list I select, it decrements only 1 from the database.
Ex. From one certain product I had 9 in the database and from the drop down list I selected 3, it needs to have 6 in the database now right? Well I don't know why but there is only one decremented so the number is 8..
Any idea how to solve that?
Bojan Serafimovski5y ago
Thank you sir.
The problem actually was because i had a selected hidden list item :
to replace a placeholder of the drop down list and that was the error.

Very grateful for your help, cheers.
Sachin Singh5y ago
please try to correct the syntax as
var sql = "UPDATE ProductsDB SET Quantity = Quantity - " + dropdownlist1.SelectedValue + " WHERE ProductName = @ProductName";
Bojan Serafimovski5y ago
string productName = Request.QueryString["productname"];
var dictionary = new Dictionary
{
{"@ProductName", productName }
};
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 - " + dropdownlist1.SelectedValue + " WHERE ProductName = @ProductName'";
connection.Execute(sql, parameters);
connection.Close();
}

With this code it throws an error:
Sachin Singh5y ago
This is what i mean
var dictionary = new Dictionary { { "@ProductName",productName }  };
var parameters = new DynamicParameters(dictionary);

using (var connection = new SqlConnection(CS))
        {
            connection.Open();
var sql = "UPDATE ProductsDB SET Quantity = Quantity - " + dropdownlist1.SelectedValue + " WHERE ProductName = @ProductName'";
  connection.Execute(sql,parameters);

            connection.Close();
        }
Bojan Serafimovski5y ago
So what does it need to be in your 06 and 07 lines of code at my case?

Because it does not update and decrement the quantity with this code..

Sachin Singh
Sachin Singh5y ago
1. using (var connection = new SqlConnection(CS))
2. {
3. connection.Open();
4. var sql = "UPDATE ProductsDB SET Quantity = Quantity-" + dropdownlist1.SelectedValue + " WHERE ProductName = @ProductName'";
6. // add parameter
7. // execute command
8. connection.Close();
9. }