0

Each time a user changes a value in one of the editable columns within my DataGridView, it triggers a recalculation of up to 8 fields. This means the event CellValueChanged Event on the DataGridView will trigger more than 1 time.

I want to create a row in a DataSet.DataTable which will include the new values for all columns after the user has changed a value.

The issue I am having is that the code below creates a row for each column that has had its value changed due to the recalculated columns.

Private Sub PL_DGV_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles PL_DGV.CellValueChanged
        If isLoaded Then
            Dim grid_row As DataRow = Me.DataSet.PL.Rows(e.RowIndex)

            Dim unsaved_row As DataRow = grid_row

            Me.DataSet.PL_UnsavedChanges.ImportRow(unsaved_row)

            unsaved_changes = True
        End If
    End Sub

How could I make it so that if a row already exists, it will just update the values within it each time?

There is no PrimaryKeys assigned to the DataTable within the DataSet, but there are 3 columns which would be used as unique values per row.

[UPDATE BASED ON COMMENTS]

With the code shown above what will happen is that the user will update a value in column 1 >

Event is triggered and new row is created which will include the new value in Column 1 >

Column 2 is recalculated based on changes in Column 1 >

Event is triggered and new row is created which will include the new value in Column 1 and Column 2 >

Column 3 is recalculated based on changes on either of the previously mentioned columns >

Event is triggered and new row is created which will include new value in Column 1, Column 2 and Column 3.

What I want to achieve is to have 1 row which will have all the new values, no need to generate any other rows but one which includes the changes from the user action and any recalculations based on that.

Pawel
  • 141
  • 1
  • 10
  • 1
    Presumably the `CellValueChanged` event is raised for the column the user modifies first, then for all the calculated columns that change. You could just add the new audit row on that first event and then assume that every event thereafter that is raised for one of the calculated columns will be an edit to the row you just added rather than a new row. For example, let's say that the value in column 0 affects the values in columns 2 & 3. When `CellValueChanged` is raised for column 0, add a new audit row and whenever it is raised for column 2 or 3, update the last audit row. – jmcilhinney Jul 23 '21 at 08:43
  • That said, depending on your specific needs, maybe you should not be handling the grid event at all and, instead, be handling the `RowChanging` and/or `RowChanged` events of the `DataTable` bound to the grid. If there is no such `DataTable`, there probably should be. – jmcilhinney Jul 23 '21 at 08:45
  • Yes, that is my issue, I can't have multiple rows, but either 1 row, or an audit of all changes which could in theory be formatted like Value of column 1, 2, 3, then column name of the cell which triggered `CellValueChanged`, then cell value. This is so that I can add functionality to "Save Changes" when user wishes to, committing the changes within the DGV to a SQL table behind it. Also, yes, I should be using a TableAdapter, but I don't think I can due to SQL that populates the DGV is not a SQL Table, but a SQL View drawing data from multiple tables. – Pawel Jul 23 '21 at 08:50
  • I don't think you read my comment properly. I offered a solution that (I believe) should enable you to create the one row. – jmcilhinney Jul 23 '21 at 09:12
  • You can still use a data adapter or table adapter to save data when it comes from a view. You just can't have the SQL code to save generated for you. You can still write that code yourself though. If the database is SQL server, your `InsertCommand` and `UpdateCommand` can contain as many SQL statements as you like, enabling you to modify as many tables as you like. – jmcilhinney Jul 23 '21 at 09:14
  • Your request makes no sense; you have a row, you change a value, the calculated column updates.. You don't then need to make another up to date row out of it; it is already a row and is already up to date – Caius Jard Jul 23 '21 at 10:10
  • @jmcilhinney I might of not understood what you mean exactly, but the first comment mentioning an "audit log" pushed me towards the solution which I show now in the updated question. I'm not entirely sure if that is what you meant by the new audit row. – Pawel Jul 23 '21 at 10:26
  • @CaiusJard the issue is that I didn't want all the rows to be created like I explained in the updated question, I just wanted 1 row which has all the cells updated, not a build-up of rows which include next update and next update and next... Just 1 row containing the updates for the whole row user just updated, and it recalculated values. – Pawel Jul 23 '21 at 10:28
  • That is what I'm saying you have. In the datatable there is a row that meets your requirements; you've actively caused a problem with the code in the question - remove the code – Caius Jard Jul 23 '21 at 10:39
  • *"Event is triggered and new row is created which will include the new value in Column 1"*. Yes. *"Event is triggered and new row is created which will include the new value in Column 1 and Column 2"*. No! Don't create a new row. Edit the last row you created. Only create a new row for the change in column 1. Edit that row for the changes to all the other columns. – jmcilhinney Jul 23 '21 at 10:39
  • *What I want to achieve is to have 1 row which will have all the new values* - you **already have a row that is exactly this**. DataTable rows track their original values and the most updated values; just ask the existing row for its Original or Current values. All the code in the question is wrong and actively causing a problem; throw it away – Caius Jard Jul 23 '21 at 10:42
  • @jmcilhinney Yes, that is my question... "If DataRow exists in DataTable, update values within it" - so like the subject says, if the row exists (because it was created by the first trigger of the Event after the user updated a value) then on next trigger update the row instead of creating a new one, if it the event is still for the same row - how? – Pawel Jul 23 '21 at 10:43
  • @CaiusJard Are you saying, that with the code I have, when the Event triggers 2nd time it will overwrite the row that was created when it ran for the 1st time? That is what this question is for, I thought the NewRow() will just add a new row and I don't know how to update the existing one... :( – Pawel Jul 23 '21 at 10:44
  • 1
    No, I'm saying that when you download a row from a database and its data is `"John, 23, "New York"` and then you change the name, and 5 minutes later you change the age, and 23 seconds later you change the city, you can ask the row for its Current values and it will say `"Paul", 27, "Chicago"`, and you can ask it for its original values and it will say `"John", 23, "New York"`. Datarows already have change tracking built in; you're trying to implement it again – Caius Jard Jul 23 '21 at 10:45
  • @CaiusJard I see, so I guess the `DataRow` object has some properties like `DataRow.CurrentValue`, `DataRow.PreviousValue` which I am not aware of, although I guess that it will provide me only with either the original value, or what is currently displaying in the grid? – Pawel Jul 23 '21 at 10:53
  • 1
    That does appear to be what you want.. If you need more levels, i.e. every typo the user has made and corrected during their repeated edits, then you need something like what you're saying you don't want - i.e. a datarow for every iteration the user has been through (if e.g. you're implementing multi level undo),. When you save a row, AccceptChanges is called and the Current values become the Original values. See the overloads of [Item](https://learn.microsoft.com/en-us/dotnet/api/system.data.datarow.item?view=net-5.0) that take a DataRowVersion – Caius Jard Jul 23 '21 at 10:59
  • I understand now @CaiusJard I will look into it, but for the moment I will carry on with an audit record for each update the user does. – Pawel Jul 23 '21 at 11:44
  • @jmcilhinney not sure whether you'd like to post your first comment as an answer to the question as that has pushed me towards the solution I used to overcome this issue therefore could be marked as the solution. – Pawel Jul 23 '21 at 11:44
  • 1
    If I consider my response detailed enough to be an answer then I post it as an answer from the start. If I'm just providing information for you to follow to find your own solution, then I'll just post a comment. You should then post your own answer based on what you come up with. – jmcilhinney Jul 23 '21 at 11:54

1 Answers1

0

After the discussion within the comments the solution to my issue, or rather what I did to overcome it was the below:

Dim grid_row As DataRow = Me.DataSet.PL.Rows(e.RowIndex)

Dim Column1 = Variable1
Dim Column2 = grid_row.Item("Column2").ToString().Trim()
Dim Column3 = grid_row.Item("Column3").ToString().Trim()

Dim Updated_column_name = Me.DataSet.PL.Columns(e.ColumnIndex).ColumnName
Dim Updated_value = grid_row.Item(Updated_column_name).ToString()

Dim row As DataRow = Me.DataSet.PL_ChangesLog.NewRow()
row("price_list") = Price_list
row("warehouse") = Warehouse
row("product_code") = Product
row("column_name") = Updated_column_name
row("updated_value") = Updated_value
row("timestamp") = DateTime.Now()
row("username") = Environment.UserName()

Me.DataSet.PL_ChangesLog.Rows.Add(row)

unsaved_changes = True

This allows me to create a DataTable holding a row of data for each cell that has had its value updated, whether recalculated via code or by user action.

Based on that I can pick out the last audit and use that as the "Save Changes" to commit the changes to the SQL Database.

Pawel
  • 141
  • 1
  • 10