How do I subtract an existing value from database with the numerical value I have entered into the database?

Question

1.00/5 (2 votes)

See more:

Its Urgent. I want it by tonight.

I am making a website that sells the products. My page that I want to do is after the user chooses a product, updates the quantity that the user wants to buy & clicks on the Checkout button, it should automatically update the existing quantity in the database by subtracting with the quantity the user ordered. If its not clear, I ll give an example. My Product Table has ProductId, ProductName, Price, Description, ProductImage, Seller & ProductStock. The Product Table has ProductA which has a stock of 10. The user buys 3. After the user buys 3 such products, I want the table to be updated to 7. The user makes use of the textbox that helps him to update any quantity that he wants.

This is my code below:
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.Data;
using System.Data.SqlClient;
using System.Data.Sql;

public partial class ViewCart : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

if (Profile.SCart == null)
{
Profile.SCart = new ShoppingCartExample.Cart();
}
if (!Page.IsPostBack)
{
ReBindGrid();
}
if (Profile.SCart.Items == null)
{
TotalLabel.Visible = false;
}
}
protected void grdCart_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string connStr2 = ConfigurationManager.ConnectionStrings["siteConnectionString"].ConnectionString;
SqlConnection con2 = new SqlConnection(connStr2);
con2.Open();
SqlCommand com2;
TextBox txtQuantity = (TextBox)grdCart.Rows[e.RowIndex].Cells[2].Controls[0];
int Quantity = Convert.ToInt32(txtQuantity.Text);
if (Quantity == 0)
{
Profile.SCart.Items.RemoveAt(e.RowIndex);
}
else
{
Profile.SCart.Items[e.RowIndex].Quantity = Quantity;
com2 = new SqlCommand("UPDATE Product SET ProductStock = ( (SELECT ProductStock FROM Product WHERE ProductName = @ProductName) - " + (txtQuantity.Text) + " ) WHERE UserName = @UserName");
com2.ExecuteNonQuery();
}

con2.Close();
grdCart.EditIndex = -1;
ReBindGrid();
}
protected void grdCart_RowEditing(object sender, GridViewEditEventArgs e)
{
grdCart.EditIndex = e.NewEditIndex;
ReBindGrid();
}
protected void grdCart_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
Profile.SCart.Items.RemoveAt(e.RowIndex);
ReBindGrid();

}
protected void grdCart_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{
grdCart.EditIndex = -1;
ReBindGrid();
}
private void ReBindGrid()
{
grdCart.DataSource = Profile.SCart.Items;
DataBind();
TotalLabel.Text = string.Format("Total Cost:{0,19:C}", Profile.SCart.Total);
}
protected void grdCart_SelectedIndexChanged(object sender, EventArgs e)
{

}
protected void Checkout_Click(object sender, EventArgs e)
{
string connStr = ConfigurationManager.ConnectionStrings["siteConnectionString"].ConnectionString;

SqlCommand com;

{

foreach (GridViewRow g1 in grdCart.Rows)

{

SqlConnection con = new SqlConnection(connStr);

com = new SqlCommand("insert into OrderDetails(OrderDetailsID,Product,Quantity,Price,Total) values ('" + g1.Cells[0].Text + "','" + g1.Cells[1].Text + "','" + g1.Cells[2].Text + "','" + g1.Cells[3].Text + "','" + g1.Cells[4].Text + "')", con);

con.Open();

com.ExecuteNonQuery();

con.Close();

Response.Redirect("Payment.aspx");

}

}

}

}

The inserting of data in the OrdersDetails are working but not the subtraction operation. Pls help.

Posted 14-Jul-14 17:46pm

Salman Ali Shah

Add a Solution

Comments

Dilan Shaminda 15-Jul-14 0:26am

This query UPDATE Product SET ProductStock = ( (SELECT ProductStock FROM Product WHERE ProductName = @ProductName) - " + (txtQuantity.Text) + " ) WHERE UserName = @UserName seems fine. I have run it on SQL server with my tables.Just run your query with hard-coded values for subtracting quantity and check whether you are getting any error on SQL server or not..

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

purvivani · Answer 1 · 2014-07-14T18:17:00

Solution 1

As per my view,
In Your update Commnad you have mention @Productname and @UserName ,but not assign any value to this 2 parameter.You need to check for this and then try .
for setting parameter you need to set vaue using SqlCommnad.Parameter.add

Posted 14-Jul-14 18:17pm

purvivani

Comments

Salman Ali Shah 15-Jul-14 0:35am

Can you please code it for me? I have no idea.

Dilan Shaminda 15-Jul-14 0:42am

You can pass parameters like this.But remember we are not here to code for you..Happy Coding :-)

cmd.Parameters.AddWithValue("@ProductName", Value);

Salman Ali Shah 15-Jul-14 0:53am

I m not a very good coding person. But i ll try. If it doesnt work, then i ll approach you.

purvivani 15-Jul-14 1:31am

I just give u hint for this.
you can pass productName instead of @productName variable. and declare productName as below way
string productName = grdCart.Rows[e.RowIndex].Cells[1].Text
after your line
Profile.SCart.Items[e.RowIndex].Quantity = Quantity;
I do not find the value you want to pass in @username from your code.
hope ,this will help you.

Dave Kreskowiak · Answer 2 · 2014-07-14T18:32:00

Solution 2

Quote:
Its Urgent. I want it by tonight.

This is EXTREMELY rude. You're not paying for support and we're here volunteering our time, whatever we have to give away. Making demands about when you want an answer is far more likely to get you ignored than it is to get you answer.

Posted 14-Jul-14 18:32pm

Dave Kreskowiak

Comments

Salman Ali Shah 15-Jul-14 0:36am

Its my project. It has very early deadline. Thats why. If u think its rude, then I m sorry.

Dave Kreskowiak 15-Jul-14 8:07am

Procrastination on your part is not a justification for being demanding.

PrakashCs.net · Answer 3 · 2014-07-14T19:25:00

use this code

C#

SqlConnection conn = new SqlConnection(connectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand("UPDATE PRD SET PRD.ProductStock = (PRD.ProductStock - @Quantity) FROM Product PRD WHERE PRD.ProductName = @ProductName", conn);
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Add("@Quantity", SqlDbType.Int).Value = Qty;
            cmd.Parameters.Add("@Productname", SqlDbType.VarChar, 50).Value = Productname;
            cmd.ExecuteNonQuery();
            conn.Close();

How do I subtract an existing value from database with the numerical value I have entered into the database?

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us