我想更新发票,发票有多个项目,我从数据库中检索发票项目到DataGridView
,现在用户可以删除项目并添加项目,用户将单击更新按钮更新数据库中的发票。
我的代码:
try
{
using (SQLiteConnection con = new SQLiteConnection(AppSettings.ConnectionString()))
{
con.Open();
for (int j = 0; j < dgv.Rows.Count; j++)
{
using (SQLiteCommand sc = new SQLiteCommand("Update Orders Set [Order_No] = @Order_No,[Order_Type] = @Order_Type,[Order_Date] = @Order_Date,[Customer_Name] = @Customer_Name,[Contact] = @Contact,[Adress] = @Adress,[Delivery_Address] = @Delivery_Address,[Rider] = @Rider,[Items] = @Items,[Price] = @Price,[Qty] = @Qty,[Item_Total] = @Item_Total,[Item_Cat] = @Item_Cat,[SubTotal] = @SubTotal,[Discount] = @Discount,[Total_Amount] = @Total_Amount,[Paid_Amount] = @Paid_Amount,[Change_Due] = @Change_Due,[Delivery_Charges] = @Delivery_Charges Where Order_No = '" + Order_No.Text + "' ", con))
{
sc.Parameters.AddWithValue("@Order_No", Order_No.Text);
sc.Parameters.AddWithValue("@Order_Type", Order_Type.Text);
sc.Parameters.AddWithValue("@Order_Date", Order_Date.Text);
sc.Parameters.AddWithValue("@Customer_Name", Customer_Name.Text);
sc.Parameters.AddWithValue("@Contact", Contact.Text);
sc.Parameters.AddWithValue("@Adress", Address.Text);
sc.Parameters.AddWithValue("@Delivery_Address", Delivery_Address.Text);
sc.Parameters.AddWithValue("@Rider", "");
sc.Parameters.AddWithValue("@Items", dgv.Rows[j].Cells[1].Value);
sc.Parameters.AddWithValue("@Price", dgv.Rows[j].Cells[2].Value);
sc.Parameters.AddWithValue("@Qty", dgv.Rows[j].Cells[3].Value);
sc.Parameters.AddWithValue("@Item_Total", dgv.Rows[j].Cells[4].Value);
sc.Parameters.AddWithValue("@Item_Cat", dgv.Rows[j].Cells[5].Value);
sc.Parameters.AddWithValue("@SubTotal", SubTotal.Text);
sc.Parameters.AddWithValue("@Discount", Discount.Text);
sc.Parameters.AddWithValue("@Total_Amount", Total_Amount.Text);
sc.Parameters.AddWithValue("@Paid_Amount", Paid_Amount.Text);
sc.Parameters.AddWithValue("@Change_Due", Change_Due.Text);
sc.Parameters.AddWithValue("@Delivery_Charges", Del_Charges.Text);
sc.ExecuteNonQuery();
}
}
SuccessBox sb = new SuccessBox();
sb.lbl_Change.Text = Change_Due.Text;
sb.label1.Text = "Successfully Updated";
sb.ShowDialog();
con.Close();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
如果我添加新项目并单击更新按钮,则此查询将用新项目替换我的所有旧项目。
假设我添加了三星S8,这样它就会将我的旧项目替换为三星S8。
结果是:
Samsung S8 1 $750
Samsung S8 1 $750
Samsung S8 1 $750
Samsung S8 1 $750
有办法做到这一点吗?
您必须设置OrderID参数才能更新所需的Item
这将通过DataGrid对象为您执行Insert、Update和Delete操作。
using System;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace InsertUpdateDeleteDemo
{
public partial class frmMain : Form
{
SqlConnection con= new SqlConnection("Data Source=.;Initial Catalog=Sample;Integrated Security=true;");
SqlCommand cmd;
SqlDataAdapter adapt;
//ID variable used in Updating and Deleting Record
int ID = 0;
public frmMain()
{
InitializeComponent();
DisplayData();
}
//Insert Data
private void btn_Insert_Click(object sender, EventArgs e)
{
if (txt_Name.Text != "" && txt_State.Text != "")
{
cmd = new SqlCommand("insert into tbl_Record(Name,State) values(@name,@state)", con);
con.Open();
cmd.Parameters.AddWithValue("@name", txt_Name.Text);
cmd.Parameters.AddWithValue("@state", txt_State.Text);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Inserted Successfully");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Provide Details!");
}
}
//Display Data in DataGridView
private void DisplayData()
{
con.Open();
DataTable dt=new DataTable();
adapt=new SqlDataAdapter("select * from tbl_Record",con);
adapt.Fill(dt);
dataGridView1.DataSource = dt;
con.Close();
}
//Clear Data
private void ClearData()
{
txt_Name.Text = "";
txt_State.Text = "";
ID = 0;
}
//dataGridView1 RowHeaderMouseClick Event
private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
ID = Convert.ToInt32(dataGridView1.Rows[e.RowIndex].Cells[0].Value.ToString());
txt_Name.Text = dataGridView1.Rows[e.RowIndex].Cells[1].Value.ToString();
txt_State.Text = dataGridView1.Rows[e.RowIndex].Cells[2].Value.ToString();
}
//Update Record
private void btn_Update_Click(object sender, EventArgs e)
{
if (txt_Name.Text != "" && txt_State.Text != "")
{
cmd = new SqlCommand("update tbl_Record set Name=@name,State=@state where ID=@id", con);
con.Open();
cmd.Parameters.AddWithValue("@id", ID);
cmd.Parameters.AddWithValue("@name", txt_Name.Text);
cmd.Parameters.AddWithValue("@state", txt_State.Text);
cmd.ExecuteNonQuery();
MessageBox.Show("Record Updated Successfully");
con.Close();
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Select Record to Update");
}
}
//Delete Record
private void btn_Delete_Click(object sender, EventArgs e)
{
if(ID!=0)
{
cmd = new SqlCommand("delete tbl_Record where ID=@id",con);
con.Open();
cmd.Parameters.AddWithValue("@id",ID);
cmd.ExecuteNonQuery();
con.Close();
MessageBox.Show("Record Deleted Successfully!");
DisplayData();
ClearData();
}
else
{
MessageBox.Show("Please Select Record to Delete");
}
}
}
}