DataGridView更新数据库 [英] DataGridView Updating database
问题描述
i我有两张桌子1）purchaseMaster和2）购买详情
和我在单张表格上的记录使用在两个表中都很常见的purchaseOrderNo。
purchaseOrderNo是购买大师的主键
purchaseOrderNO是forieng键
工作正常
但我点击该按钮后我有更新按钮我想同时从两个表中更新记录而不创建新记录但是在特别购买详细信息中更新现有记录。我正在获取购买详情记录到datagridview1 upadating into datagridview1并想要更新到数据库.......
请帮帮我.... ...
实际上我正在编码，但它只更新第一条记录，每当尝试更新第二条记录时，它都会给出错误异常，例如....下面
变量名'@P_O_No'已经是dec LARED。变量名在查询批处理或存储过程中必须是唯一的。
.......编码是.....
Hi,
i am having two tables 1) purchaseMaster and 2) Purchase details
and i am serching record on single form by using purchaseOrderNo which is common in both table.
purchaseOrderNo is primary keyfor Purchase Master
purchaseOrderNO is forieng key
that working fine
but i am having update button after clicking on that button i want update records from both table simultaneously without creating new record but updating existing record in specially purchase details.while i am fetching purchase details record into datagridview1 upadating into datagridview1 and want to update into database .......
please help me.......
actually i am having coding but it only update first record and whenever trying to update second records it will giving error exception like....below
The variable name '@P_O_No' has already been declared. Variable names must be unique within a query batch or stored procedure.
.......and coding is.....
private void btnUpdate_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd = new SqlCommand("POmaster1",con);
cmd.Parameters.AddWithValue("P_O_No", cmbpono.Text);
string dd = dateTimePicker1.Value.ToString("yyyy-MM-dd");
cmd.Parameters.AddWithValue("P_O_Date", Convert.ToDateTime(dd));
cmd.Parameters.AddWithValue("SuppId", txtSuppId.Text);
cmd.Parameters.AddWithValue("SuppName", cmbSuppName.Text);
cmd.Parameters.AddWithValue("Pay_Duration_Days", txtPay_Duration_Days.Text);
cmd.Parameters.AddWithValue("Tax", txtTax.Text);
cmd.Parameters.AddWithValue("Grand_Total", txtGrand_Total.Text);
cmd.Parameters.AddWithValue("Param", 2);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
MessageBox.Show("Record Updated Sucsesfully");
con.Close();
/*con.Open();
{
DataSet ds = new DataSet();
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
SqlDataAdapter da = new SqlDataAdapter("insert into PurchaseOrderDetails values('" + dataGridView1.Rows[i].Cells[0].Value + "','" + dataGridView1.Rows[i].Cells[1].Value + "','" + dataGridView1.Rows[i].Cells[2].Value + "','" + dataGridView1.Rows[i].Cells[3].Value + "','" + dataGridView1.Rows[i].Cells[4].Value + "','" + dataGridView1.Rows[i].Cells[5].Value + "','" + dataGridView1.Rows[i].Cells[6].Value + "')", con);
da.Fill(ds, "Practice");
}
con.Close();
}*/
con.Open();
SqlCommand cmd2 = new SqlCommand();
cmd2.Connection = con;
string myCmd = string.Empty;
foreach (DataGridViewRow myDgrow in dataGridView1.Rows)
{
myCmd = "Update PurchaseOrderDetails set ProdName='"+myDgrow.Cells["ProdName"].Value+"',ProdQty='" + myDgrow.Cells["ProdQty"].Value + "',ProdDiscription='" + myDgrow.Cells["ProdDiscription"].Value + "',Prod_Unit_Price='" + myDgrow.Cells["Prod_Unit_Price"].Value + "',Amount='" + myDgrow.Cells["Amount"].Value+"' where ProdId='" + myDgrow.Cells["ProdId"].Value +"'";
cmd2.Parameters.AddWithValue("@P_O_No", myDgrow.Cells["P_O_No"].Value);
cmd2.Parameters.AddWithValue("@ProdId", myDgrow.Cells["ProdId"].Value);
cmd2.Parameters.AddWithValue("@ProdName", myDgrow.Cells["ProdName"].Value);
cmd2.Parameters.AddWithValue("@ProdQty", myDgrow.Cells["ProdQty"].Value);
cmd2.Parameters.AddWithValue("@ProdDiscription", myDgrow.Cells["ProdDiscription"].Value);
cmd2.Parameters.AddWithValue("@Prod_Unit_Price", myDgrow.Cells["Prod_Unit_Price"].Value);
cmd2.Parameters.AddWithValue("@Amount", myDgrow.Cells["Amount"].Value);
cmd2.CommandText = myCmd;
cmd2.ExecuteNonQuery();
dataGridView1.Update();
myCmd = string.Empty;
}
DataTable details = new DataTable();
SqlDataAdapter da1 = new SqlDataAdapter();
try
{
da1.Update(details);
}
catch (Exception exceptionObj)
{
MessageBox.Show(exceptionObj.Message.ToString());
}
}
private void btnDelete_Click(object sender, EventArgs e)
{
con.Open();
SqlCommand cmd1 = new SqlCommand("Delete PurchaseOrderDetails where P_O_No='" + txtpo.Text + "'", con);
cmd1.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand("POmaster1", con);
cmd.Parameters.AddWithValue("P_O_No", cmbpono.Text);
string dd = dateTimePicker1.Value.ToString("yyyy-MM-dd");
cmd.Parameters.AddWithValue("P_O_Date", Convert.ToDateTime(dd));
cmd.Parameters.AddWithValue("SuppId", txtSuppId.Text);
cmd.Parameters.AddWithValue("SuppName", cmbSuppName.Text);
cmd.Parameters.AddWithValue("Pay_Duration_Days", txtPay_Duration_Days.Text);
cmd.Parameters.AddWithValue("Tax", txtTax.Text);
cmd.Parameters.AddWithValue("Grand_Total", txtGrand_Total.Text);
cmd.Parameters.AddWithValue("Param", 3);
cmd.CommandType = CommandType.StoredProcedure;
/* SqlCommand cmd2 = new SqlCommand("Delete PurchaseOrderMaster where P_O_No='"+cmbpono.Text+"'", con);*/
cmd.ExecuteNonQuery();
MessageBox.Show("Record Deleted Sucsesfully");
con.Close();
}
推荐答案
由于您正在循环，只需清除参数，然后再尝试将其重新添加。 cmd2.Parameters（）。Clear（）。
Since you are looping, just clear your parameters before trying to add them back in. cmd2.Parameters().Clear().
当你完成一个表的添加/更新时，你应该处理你的命令，当你想要添加到另一个表中时，重新创建cmd。
When you are done with adding/updating with one table you should dispose your command and when you want to add into another table recreate the cmd.
cmd.dispose()
这篇关于DataGridView更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
