Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

c# - How To Add Value To A Column And Update Database

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 = " "; 
            }
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

enter image description here


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...