Editing and saving datagridview functionality in window forms?

Question

I am working on a datagridview in a windows c form c application I am loading data from a database, now I want the user to be able to edit the cell value and save the value in the database, how to change the cell value and how can I save the value in the database?

SqlConnection con = new SqlConnection("user id=sa;password=123;database=employee"); SqlDataAdapter da = new SqlDataAdapter("select * from UserReg", con); DataSet ds = new DataSet(); da.Fill(ds, "p"); dataGridView1.DataSource = ds.Tables["p"]; 

Answer 1

One way to update a database using a DataGridView is to use DataGridView events:

 DataGridView.CellBeginEdit DataGridView.CellValidating DataGridView.CellEndEdit 

Let's say: private DataGridView dgv; Add event handlers

 dgv.CellBeginEdit += dgv_CellBeginEdit; dgv.CellValidating += dgv_CellValidating; dgv.CellEndEdit += dgv_CellEndEdit; private void dgv_CellBeginEdit(Object sender, DataGridViewCellCancelEventArgs e) { //Here we save a current value of cell to some variable, that later we can compare with a new value //For example using of dgv.Tag property if(e.RowIndex >= 0 && e.ColumnIndex >= 0) { this.dgv.Tag = this.dgv.CurrentCell.Value; //Or cast sender to DataGridView variable-> than this handler can be used in another datagridview } } private void dgv_CellValidating(Object sender, DataGridViewCellValidatingEventArgs e) { //Here you can add all kind of checks for new value //For exapmle simple compare with old value and check for be more than 0 if(this.dgv.Tag = this.dgv.CurrentCell.Value) e.Cancel = true; //Cancel changes of current cell //For example used Integer check int32 iTemp; if (Int32.TryParse(this.dgv.CurrentCell.Value, iTemp) = True && iTemp > 0) { //value is ok } else { e.Cancel = True; } } Private Sub dgvtest1_CellEndEdit(Object sender, DataGridViewCellEventArgs e) { //Because CellEndEdit event occurs after CellValidating event(if not cancelled) //Here you can update new value to database } 

Answer 2

Try to do this:

  private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e) { //after you've filled your ds, on event above try something like this try { da.Update(ds); } catch (Exception ex) { MessageBox.Show(ex.Message); } } 

Answer 3

I used OleDb, but you can use SQL. Here is the code that I usually use for it.

 OleDbCommand sCommand; OleDbDataAdapter sAdapter; OleDbCommandBuilder sBuilder; OleDbConnection connection; DataSet sDs; DataTable sTable; string myMode = ""; private void BtnLoad_Click(object sender, EventArgs e) { string query = "SELECT * FROM [Table]"; connection = new OleDbConnection(connectionString); connection.Open(); sCommand = new OleDbCommand(query, connection); sAdapter = new OleDbDataAdapter(sCommand); sBuilder = new OleDbCommandBuilder(sAdapter); sDs = new DataSet(); sAdapter.Fill(sDs, "Table"); sTable = sDs.Tables["Table"]; connection.Close(); DataGrid.DataSource = sTable; DataGrid.ReadOnly = true; DataGrid.SelectionMode = DataGridViewSelectionMode.FullRowSelect; } private void BtnAdd_Click(object sender, EventArgs e) { DataGrid.ReadOnly = false; myMode = "add"; } private void BtnEdit_Click(object sender, EventArgs e) { DataGrid.ReadOnly = false; myMode = "edit"; } private void BtnDelete_Click(object sender, EventArgs e) { myMode = ""; if (MessageBox.Show("Do you want to delete this row ?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes) { DataGrid.Rows.RemoveAt(DataGrid.SelectedRows[0].Index); sAdapter.Update(sTable); } } private void BtnSave_Click(object sender, EventArgs e) { if (myMode == "add") { sAdapter.Update(sTable); MessageBox.Show("Prices Are Successfully Added.", "Saved.", MessageBoxButtons.OKCancel, MessageBoxIcon.Information); } else if (myMode == "edit") { string query = "UPDATE Table_Name SET " + "Column1 = '" + DataGrid.SelectedRows[0].Cells[0].Value.ToString() + "' ," + "Column2 = " + DataGrid.SelecteddRows[0].Cells[1].Value.ToString() + ", " + "WHERE CONDITION"; connection = new OleDbConnection(connectionString); connection.Open(); sCommand = new OleDbCommand(query, connection); sAdapter = new OleDbDataAdapter(sCommand); sBuilder = new OleDbCommandBuilder(sAdapter); sDs = new DataSet(); sAdapter.Fill(sDs, "Table"); sTable = sDs.Tables["Table"]; connection.Close(); DataGrid.DataSource = sTable; DataGrid.ReadOnly = true; } } 

Answer 4

See DataGridView Events . You need to subscribe to the corresponding event and process it accordingly. Namely, you are interested in DataGridView.CellValueChanged .

 dataGridView1.CellValueChanged += ValueChangedHandler; private void ValueChangedHandler(object sender, DataGridViewCellEventArgs e) { // do what is appropriate here. }