如何为列添加值并更新数据库 [英] How To Add Value To A Column And Update Database

问题描述

我正在尝试将 dataGridViewAFp1 中 Annual_Fees 列的值添加到文本框 txtp1AF 中的新值 AF2.我确实转换为整数的文本.然后添加 ResultAF 的结果被转换回字符串 updatedAF，这是数据库中要更新的值.我确实将变量 AF11 初始化为 0.查询确实更新了选定的列.这个想法是获取 AF2 中的任何值，并将其添加到该列和行中的任何值中，具体地我放入 AF11 中的 数据库 因此更新值 updatedAF.这是我到目前为止所做的，但似乎没有用.这些值没有相加.

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 = " "; 
            }

推荐答案

假设其他一切都在您这边工作正常，主要问题是您使用的 Update 命令包含在您的cmd 变量，您之前用于更新数据库，尝试用​​更新的值填充您的 dt.相反，您必须为此使用 Select 命令，如下所示:

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:

改变这个:

DataTable dt = new DataTable();
SqlDataAdapter adap = new SqlDataAdapter(cmd);
adap.Fill(dt);
//dataGridViewAFp1.DataSource = dt;

为此:

 DataTable dt = new DataTable();
 SqlDataAdapter adap = new SqlDataAdapter("select * from P1", conn);
 adap.Fill(dt);
 dataGridViewAFp1.DataSource = dt;

添加完整的工作代码.在此示例中，从 100.00 到 200.00(watch) 的前 2 个数据行更新 ListPrice 列(列索引 9).我正在使用您的代码，只是更改了表名和列名.

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屋！