如何使用我输入数据库的数值从数据库中减去现有值？ [英] How do I subtract an existing value from database with the numerical value I have entered into the database?
问题描述
紧急。我今晚想要它。
我正在建立一个销售产品的网站。我想要做的页面是在用户选择产品，更新用户想要购买的数量并点击Checkout按钮后，它应该通过减去用户订购的数量自动更新数据库中的现有数量。如果不清楚，我会举一个例子。我的产品表有ProductId，ProductName，价格，描述，ProductImage，Seller&ProductStock。产品表有ProductA，库存为10.用户购买3.用户购买3个此类产品后，我希望将表更新为7.用户使用文本框帮助他更新任何数量的他想要。
这是我的代码：
使用System;
使用System.Collections.Generic ;
使用System.Linq;
使用System.Web;
使用System.Web.UI;
使用System.Web.UI.WebControls;
使用System.Configuration;
使用System.Data;
使用System.Data.SqlClient;
使用System.Data.Sql;
公共部分类ViewCart：System.Web.UI。页面
{
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 （数量== 0）
{
Profile.SCart.Items.RemoveAt（e.RowIndex）;
}
其他
{
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寄件人，GridViewCancelEditEventArgs e）
{
grdCart.EditIndex = -1;
ReBindGrid（）;
}
private void ReBindGrid（）
{
grdCart.DataSource = Profile.SCart.Items;
DataBind（） ;
TotalLabel.Text = string.Format（总费用：{0,19：C}，Profile.SCart.Total）;
}
protected void grdCart_SelectedIndexChanged（object sender，EventArgs e）
{
}
protected void Checkout_Click（对象发送者，EventArgs e）
{
string connStr = ConfigurationManager.ConnectionStrings [siteConnectionString]。ConnectionString;
SqlCommand com;
{
foreach（grdCart.Rows中的GridViewRow g1）
{
SqlConnec con 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（）; < br $>
com.ExecuteNonQuery（）;
con.Close（）;
Response.Redirect（Payment.aspx）;
}
}
}
}
在数据库中插入数据OrdersDetails正在工作但不是减法操作。请帮助。
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.
推荐答案
根据我的观点，
在您的更新Commnad中，您提到了@Productname和@UserName，但没有分配任何这个2参数的值。你需要检查这个，然后尝试。
设置参数需要使用SqlCommnad.Parameter.add设置vaue
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
紧急。今晚我想要它。
这非常粗鲁。你不是在支付我们的支持，我们在这里志愿服务我们的时间，无论我们要放弃什么。提出何时需要答案的要求更有可能让你被忽视而不是让你回答。
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.
使用此代码
use this code
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();
这篇关于如何使用我输入数据库的数值从数据库中减去现有值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
