pls help on datagrid view insert /update operation

Question

Hi all, i have a table employee;[MSACESS database] it has an ID which is auto generated. in datagrid view, the id field is hidden. name and department values user should enter. so for current row , if there is no ID value, then it should be insert. else , if there is an id value is present it should be update. my code below not looking into that. can you please help me private void DgvWizard_CellValueChanged( object sender, DataGridViewCellEventArgs e) { //insert or update into database the values from the datagridview if (dgvWizard.CurrentRow != null ) { using (OleDbConnection Connection = new OleDbConnection(connectionString)) { Connection.Open(); DataGridViewRow dgvRow = dgvWizard.CurrentRow; try { if (dgvRow.Cells[ "txtEmpID" ].Value==DBNull.Value) cmd = new OleDbCommand(cmdText: "insert into employees(name,contact) values (@name,@contact)" , Connection); //if employeeid is null, then it should insert the current row else cmd = new OleDbCommand(cmdText: "update employees set name =@name, contact=@contact where empid=@empid" , Connection); //if employee id is not null, it means its an existing record and use update query cmd.Parameters.AddWithValue( "@name" , dgvRow.Cells[ "txtEmployeeName" ].Value == DBNull.Value ? "" : dgvRow.Cells[ "txtEmployeeName " ].Value.ToString()); cmd.Parameters.AddWithValue( "@empid " , Convert.ToInt32(dgvRow.Cells[ "txtEmpID " ].Value == DBNull.Value ? "" : dgvRow.Cells[ "txtEmpID " ].Value)); cmd.Parameters.AddWithValue( "@contact" , dgvRow.Cells[ "txtContactName" ].Value == DBNull.Value ? "" : dgvRow.Cells[ "txtContactName" ].Value.ToString()); cmd.ExecuteNonQuery(); MessageBox.Show( "Success!" ); } catch (Exception ee) { } } } }

Gowtham Ramar · Answer

you have any exception

James Axsom · Answer

Form Class { String id = string .empty; String valueBeingChanged = string .empty. Private void dgvWizard_CellValueChanged( object sender, DataGridViewCellEventArgs e) { DataGridViewRow selectedRow = dgvWizard.CurrentRow; Id = ( string ) row.Cells[“ID”].Value; DataGridViewRow selectedCell = dgvWizard.CurrentCell; valueBeingChanged = ( string ) cell.Value; } Private void btnSave_Click( object sender, EventArgs e) { // All your data access and save logic here // use the values id and valueBeingChanged for a save/update // at end of the operation clear values id and valueBeingChanged } } Anna, your approach for the desired functionality is off. There’s two ways to treat a datagridview control. 1) Bind it to a datasource which is what you need to achieve your desired functionality. 2) Treat the datagrid as if it were just any list control with disconnected datasource, which how you have it right now. In order for the datagrid to know a row has changed it has to be bound to a data table, the first option. You would use the adapter object to accept the changes and save the changes to the database in the event anything changed in the grid no matter what row. Peruse your code. You have all your data access logic embedded in this grid’s cell value changed event. Every time a cell, not matter what row is being edited, you are opening a database connection and either insert a row with incomplete data or change a row and worse yet you are not even closing the database connection. If you continue with your present design, then its best to move your save logic into a button click event, like a save button. You can use the cell value changed event like you are doing now, but without the embedded data access logic. The role this event should identify which row and which cell is being changed. You can do that by using: The datagrid’s CurrentRow property and CurrentCell property You can also use with less effectiveness the DataGridViewCellEventArgs and identify the column and row index, but again that’s not too effective, but can it can work. The datagrid uses a class representing a row called DataGridViewRow. Here is how you get and use the selected row in the grid DataGridViewRow selectedRow = dgvWizard.CurrentRow; The datagrid uses a class represent a cell called DataGridViewCell. Here is how you get and use the selected cell in the grid. DataGridViewCell selectedCell = dgvWizard.CurrentCell; With these two classes you can manipulate your grid by getting and setting data in the selected row or cell. However since your grid is disconnected, your save logic has to separated outside the Cell Value Changed event and saved on a different event trigger like a button click. Here is an example of what a binded datagrid looks like in code: DataTable entityTable = new DataTable (); BindingSource bind = new BindingSource (); bind . DataSource = entityTable ; DataGridView ServersTable = new DataGridView (); dgvWizard . AutoGenerateColumns = false ; dgvWizard . DataSource = entityTable ; dgvWizard . DataSource = bind ; dgvWizard . Refresh (); Prior to this code you will open a connection. You will use an adapter object. You will fill the datatable via the data adapter. It’s this data adapter that knows when a datagrid view has changed or not. You would have command objects associated with the adapter object to handle the inserts and updates. Here is an example of treating a datagrid view like any list control. dgvWizard.DataSource = entityTable; No binding! The data adapter has no knowledge of what’s going on in the grid. The data adapter is just mechanism to fill the data table, that’s it. My personal preference is to treat the grid like any other list control as opposed to binding it. It’s more efficient performance wise, but it does require more code on your part to manage what data has changed and what data requires to be inserted into the table. What that means you have to write code to manage the grid’s state. State being what row changed or what row is going to be inserted. An update example: You edit a cell in a row. You identity the row using DataGridViewRow. You get the row’s unique primary key field value so that you know which row to update. You get the cell you changed using DataGridViewCell You get the value you changed the cell to. You click a button and save the data back to the table as an update. Question: How will the button click event know what data is being collected in grid’s cell value change event? Answer: You assign the values to a module scope variables, like a class field variable, inside the grid’s cell value change event. Now that button event know what data is going to be updated it sends it back to the database. The most important end step is to clear the data in your class field variables or module scope variables, because if you don’t, lingering data from a previous operation may screw you up. Example: Form Class { String id = string .empty; String valueBeingChanged = string .empty. Private void dgvWizard_CellValueChanged( object sender, DataGridViewCellEventArgs e) { DataGridViewRow selectedRow = dgvWizard.CurrentRow; Id = ( string ) row.Cells[“ID”].Value; DataGridViewRow selectedCell = dgvWizard.CurrentCell; valueBeingChanged = ( string ) cell.Value; } Private void btnSave_Click( object sender, EventArgs e) { // All your data access and save logic here // use the values id and valueBeingChanged for a save/update // at end of the operation clear values id and valueBeingChanged } } The insert row operation is the same format. You’ll need to collect all the data in the row, so won’t need to use DataGridCell. You will need a variable for each column in the table. I could go on with Single Responsibility Principle and Law of Demeter, but even though that stuff is cool, let’s just get your code working first, then we’ll refactor it later.