Save edited data in rows

Question

I am showing a database table in a datagridview . I can correctly save records from datagridview to database in sql.

Now I want to change and modify some records and save these changes to the database. How can i do this? I am using a datasource binding bound to a dataset with 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 easily save new records in the database and datagridview , but I cannot edit existing records. After pressing the save button after editing, it displays the previous value again. Please, help.

Answer 1

Your database is not controlled by your application; he is not going to send any event back to your application when the data has been changed. You must actively query the database for changes.

A more typical approach with a DataGridView is to first apply the changes to your local copy of the data, and then move the changes to the database using the DataAdapter. This avoids updating the entire local dataset at any time when making changes. See Updating data sources using DataAdapters (ADO.NET).

The main sequence:

• Connect to the data source, use DataAdapter.Fill () to populate the data table.
• Define UpdateCommand or InsertCommand, which defines how the data in the local DataTable will be transferred to the database.
• Add row to local DataTable
• Call DataAdapter.Update () to dump the updates to the database.

Answer 2

You just have to check if the record exists in your table first using the "Select command" command

"Select * from centraldb.dbo.CPDM0020 where Code = '" + Code + "'";

you can use this function:

  public bool IsExistRecord(string Query) { try { DataTable DT = new DataTable(); SqlCommand CMD = new SqlCommand(Query, Connection); SqlDataAdapter DA = new SqlDataAdapter(CMD); DA.Fill(DT); if (DT.Rows.Count > 0) return true; else return false; } catch (Exception ex) { return false; } } 

if the record exists, execute the update request; if it does not exist, execute the insert query.

Answer 3

Try using the following code>

 try { var row = gvTransactions.CurrentRow; int ID= int.parse(row.Cells[0].Value.ToString()); string abc=row.Cells[0].Value.ToString(); } catch(exception ex) { } 

Get values ​​like this.

Note: do not write anything to the catch block.

and then execute the insert / update request according to your needs.

Answer 4

make update button:

 private void btnUpdate_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 select_qry = "Select * from centraldb.dbo.CPDM0020 Where Code = '" + Code + "'"; if(IsExistRecord(select_qry)) { string update_qry = Update centraldb.dbo.CPDM0020 set Code,Currency_Name='" + currency_Name + "',Base='" + boolBase + "',Local_per_Base,Base_per_Local='" + base_per_Local + "' where code='" + code +"'"; if (this.ExecuteSql(update_qry)) { MessageBox.Show("Record Updated Successfully."); } else { MessageBox.Show("Update Failed"); } } } //code taken from Mohammad abumazen public bool IsExistRecord(string Query) { DataTable DT = new DataTable(); SqlCommand CMD = new SqlCommand(Query, Connection); SqlDataAdapter DA = new SqlDataAdapter(CMD); DA.Fill(DT); if (DT.Rows.Count > 0) return true; else return false; } 

Answer 5

Since you are using a dataSet , you can create commands in tableApdater . Here's an article about Creating a Data Access Layer , since you're in winforms , you can apply this article in your project.

I hope this image gives you a hint for save , update , edit , delete .