如何增加价值一列，并更新数据库 [英] How To Add Value To A Column And Update Database
问题描述
我试图从添加列的 Annual_Fees 在 dataGridViewAFp1 来的新值 AF2 价值文本框 txtp1AF.Text ，我没有转换为整数。再从另外的结果 ResultAF 转换回字符串的 updatedAF 这就是在数据库中更新的价值。我没有初始化变量 AF11 0查询并更新所选列。我们的想法是让任何值在 AF2 并把它添加到任何值是该列和行明确，我把在 AF11 在数据库因此更新值 updatedAF 。这是我迄今所做的，但似乎并不奏效。值不相加。
私人无效btnEnterAFp1_Click（对象发件人，EventArgs五）
{
如果（string.IsNullOrWhiteSpace（txtp1AF的.text））
{
MessageBox.Show（请输入费，哎呀，MessageBoxButtons.OK，MessageBoxIcon.Warning）;
}
，否则
{
如果（dataGridViewAFp1.Rows [0] .Cells [Annual_Fees]。值！= NULL）
{
康恩。打开（）;
的SqlCommand CMD =新的SqlCommand（）;
cmd.Connection =康恩;
int.TryParse（dataGridViewAFp1.Rows [0] .Cells [Annual_Fees] Value.ToString（），出AF11）;
AF2 = Convert.ToInt32（txtp1AF.Text）;
ResultAF = AF11 + AF2;
字符串updatedAF = Convert.ToString（ResultAF）;
cmd.CommandText = @更新设置P1 = Annual_Fees AF @哪里Sponsorship_Status ='赞助商'OR Sponsorship_Status ='非商业性'OR Sponsorship_Status ='原赞助商';
cmd.Parameters.AddWithValue（@ AF，updatedAF）;
cmd.ExecuteNonQuery（）;
DataTable的DT =新的DataTable（）;
SqlDataAdapter的ADAP =新SqlDataAdapter的（CMD）;
adap.Fill（DT）;
//dataGridViewAFp1.DataSource = DT;
conn.Close（）;
MessageBox.Show（记录更新成功）;
txtp1AF.Text =;
}
假定其他一切工作正常就在你身边，主要的问题是，你正在使用包含在你的 CMD 变量相同更新命令，即你之前使用更新的数据库，要尽量填补你的 DT 与更新的值。相反，你必须使用选择命令用于此目的，如下所示：
更改此：
DataTable的DT =新的DataTable（）;
SqlDataAdapter的ADAP =新SqlDataAdapter的（CMD）;
adap.Fill（DT）;
//dataGridViewAFp1.DataSource = DT;
这样：
DataTable的DT =新的DataTable（）;
SqlDataAdapter的ADAP =新的SqlDataAdapter（SELECT * FROM P1，康恩）;
adap.Fill（DT）;
dataGridViewAFp1.DataSource = DT;
编辑：添加完整的工作代码。在此示例中，列 ListPrice （列索引9）从 100.00 到<$更新为第2数据行C $ C> 200.00 （手表）。我用你的代码，只是改变表和列名。
私人无效btnEnterAFp1_Click（对象发件人，EventArgs五）
{
SqlConnection的康恩=新的SqlConnection（@ 数据源=（的LocalDB）\MSSQLLocalDB; AttachDbFilename = | DataDirectory目录| \AdventureWorks2014.mdf;集成安全性= TRUE;连接超时= 30）;
十进制AF2;
十进制AF11;
十进制ResultAF;
如果（string.IsNullOrWhiteSpace（txtp1AF.Text））
{
MessageBox.Show（请输入费，哎呀，MessageBoxButtons.OK， MessageBoxIcon.Warning）;
}
，否则
{
如果（dataGridViewAFp1.Rows [0] .Cells [9] .value的！= NULL）
{
conn.Open （）;
的SqlCommand CMD =新的SqlCommand（）;
cmd.Connection =康恩;
decimal.TryParse（dataGridViewAFp1.Rows [0] .Cells [9] .Value.ToString（），出AF11）;
AF2 = Convert.ToDecimal（txtp1AF.Text）;
ResultAF = AF11 + AF2;
字符串updatedAF = Convert.ToString（ResultAF）;
cmd.CommandText = @更新Production.Product设置ListPrice = @ AF其中Name ='可调赛'OR名称='球轴承';
cmd.Parameters.AddWithValue（@ AF，updatedAF）;
INT N = cmd.ExecuteNonQuery（）;
DataTable的DT =新的DataTable（）;
SqlDataAdapter的ADAP =新的SqlDataAdapter（SELECT * FROM Production.Product，康恩）;
adap.Fill（DT）;
dataGridViewAFp1.DataSource = DT;
conn.Close（）;
MessageBox.Show（记录更新成功）;
txtp1AF.Text =;
}
}
}
的 
I am trying to add the value of the column Annual_Fees in dataGridViewAFp1 to a new value AF2 from the textbox txtp1AF.Text that I did convert to integer. Then the result from the addition ResultAF is converted back to string updatedAF and that's the value to be updated in the database. I did initialize variable AF11 to 0. The query does update the selected columns. The idea is to get whatever value is in AF2 and add it to whatever value is in that column and row specifically which i put in AF11 in the database hence the updated value updatedAF. Here's what I have done so far but doesn't seem to be working. The values are not adding up.
private void btnEnterAFp1_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(txtp1AF.Text))
{
MessageBox.Show("Please Enter fee","Oops",MessageBoxButtons.OK,MessageBoxIcon.Warning);
}
else
{
if (dataGridViewAFp1.Rows[0].Cells["Annual_Fees"].Value != null)
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
int.TryParse(dataGridViewAFp1.Rows[0].Cells["Annual_Fees"].Value.ToString(), out AF11);
AF2 = Convert.ToInt32(txtp1AF.Text);
ResultAF = AF11 + AF2;
String updatedAF = Convert.ToString(ResultAF);
cmd.CommandText = @"Update P1 set Annual_Fees=@af where Sponsorship_Status = 'Sponsored' OR Sponsorship_Status = 'Unsponsored' OR Sponsorship_Status = 'Formerly Sponsored' ";
cmd.Parameters.AddWithValue("@af", updatedAF);
cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(cmd);
adap.Fill(dt);
//dataGridViewAFp1.DataSource = dt;
conn.Close();
MessageBox.Show("Record Updated Successfully ");
txtp1AF.Text = " ";
}
Assuming everything else is working fine on your side, the main problem is that you're using the same Update command contained in your cmd variable, that you used before to update the database, to try to fill your dt with the updated values. Instead you must use a Select command for that purpose, as follows:
Change this:
DataTable dt = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(cmd);
adap.Fill(dt);
//dataGridViewAFp1.DataSource = dt;
to this:
DataTable dt = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter("select * from P1", conn);
adap.Fill(dt);
dataGridViewAFp1.DataSource = dt;
EDIT: Adding full working code. In this sample, column ListPrice (column index 9) is updated for the first 2 data rows from 100.00 to 200.00(watch). I'm using your code, just changing table and column names.
private void btnEnterAFp1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\AdventureWorks2014.mdf;Integrated Security=True;Connect Timeout=30");
decimal AF2;
decimal AF11;
decimal ResultAF;
if (string.IsNullOrWhiteSpace(txtp1AF.Text))
{
MessageBox.Show("Please Enter fee", "Oops", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
if (dataGridViewAFp1.Rows[0].Cells[9].Value != null)
{
conn.Open();
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
decimal.TryParse(dataGridViewAFp1.Rows[0].Cells[9].Value.ToString(), out AF11);
AF2 = Convert.ToDecimal(txtp1AF.Text);
ResultAF = AF11 + AF2;
String updatedAF = Convert.ToString(ResultAF);
cmd.CommandText = @"Update Production.Product set ListPrice=@af where Name = 'Adjustable Race' OR Name = 'Bearing Ball'";
cmd.Parameters.AddWithValue("@af", updatedAF);
int n = cmd.ExecuteNonQuery();
DataTable dt = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter("select * from Production.Product", conn);
adap.Fill(dt);
dataGridViewAFp1.DataSource = dt;
conn.Close();
MessageBox.Show("Record Updated Successfully ");
txtp1AF.Text = " ";
}
}
}
这篇关于如何增加价值一列，并更新数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
