Save the edited data in rows

Question

I show database table in a datagridview. I can save the records properly from datagridview to database in sql.

Now, I want to modify and change some records and save these changes in database. How can I do this? I'm using a binding datasource attached to a dataset with a datatable.

private void Form1_Load(object sender, EventArgs e)
{
    this.cPDM0020TableAdapter.Fill(this.cpdm_dataset.CPDM0020);
}

private void btnSave_Click(object sender, EventArgs e)
{
    string code = dataGridView1[0, dataGridView1.CurrentCell.RowIndex].Value.ToString().ToUpper();
    string currency_Name = dataGridView1[1, dataGridView1.CurrentCell.RowIndex].Value.ToString().ToUpper();
    string boolBase = dataGridView1[2, dataGridView1.CurrentCell.RowIndex].Value.ToString();
    string local_per_Base = dataGridView1[3, dataGridView1.CurrentCell.RowIndex].Value.ToString();
    string base_per_Local = dataGridView1[4, dataGridView1.CurrentCell.RowIndex].Value.ToString();
    string insert_sql = "INSERT INTO centraldb.dbo.CPDM0020(Code,Currency_Name,Base,Local_per_Base,Base_per_Local)VAL‌​UES('" + 
        code + "','" + 
        currency_Name + "','" + 
        boolBase + "','" + 
        local_per_Base + "','" + 
        base_per_Local + "')";

    if (this.ExecuteSql(insert_sql))
    {
        MessageBox.Show("Record Inserted Successfully.");
    }
    else
    {
        MessageBox.Show("Insert Failed");
    }
}

public bool ExecuteSql(string command)
{

    SqlCommand sqlCommand = new SqlCommand(command, connection);
    connection.Open();
    sqlCommand.ExecuteNonQuery();
    this.cPDM0020TableAdapter.Fill(this.cpdm_dataset.CPDM0020);
    dataGridView1.DataSource = cpdm_dataset.CPDM0020;
    sqlCommand.Dispose();
    connection.Close();
    return true;
}

I can save the new entries easily in database and datagridview, but I cannot edit the already present records..On clicking save button after editing, it shows the previous value again. Please help.

Answer 1

Your database is not controlled by your app; it is not going to send some event back to your application when data has changed. You have to actively requery the database for changes.

The more typical approach with a DataGridView is to first apply the changes to your local copy of the data, then push the changes back to the database using a DataAdapter. This avoids refreshing the entire local dataset anytime a change is made. See Updating Data Sources with DataAdapters (ADO.NET).

The basic sequence is:

1. Connect to data source, use DataAdapter.Fill() to fill your data table
2. Define an UpdateCommand or InsertCommand that defines how data in the local DataTable will be pushed to the database
3. Add a row to your local DataTable
4. Call DataAdapter.Update() to push the updates to the database.