pls help on datagrid view insert /update operation

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:

 

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.