VB.Net affect changes to a table

Question

I am using a datagridview to display table data and changing values of a particular cell. Depending on requirement I may need to change such values for more than one row.

I am trying to use datagridview1.CellValueChanged to populate a Dataset (i.e. create a collection of changes made) and subsequently saving the changes by clicking on a command button.

My problem is that though for each change, the sub is being called ONLY the last change is being saved. I was thinking of using the Dataset to store multiple records where the values are changed and then SAVE all the rows in the Dataset in the database table (using Update).

Could there be some solution to my predicament.

PS. Before trying this (ADO.net dataset) I was updating a temporary table and then using that I was updating the database.

Grateful for a solution please.

Code:::
Private Sub dGridVwCreaCode_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dGridVwCreaCode.CellValueChanged

    Dim qryStr_CodeShtText_Changed As String
    Dim var_CodeID_Changed As Long

    var_CodeID_Changed = dGridVwCreaCode(e.ColumnIndex - 2, e.RowIndex).Value


    qryStr_CodeShtText_Changed = "SELECT Code_ID, Code, Code_Descrip FROM Code_SAP " & _
        "WHERE (Code_SAP.Code_ID = " & var_CodeID_Changed & ")"

    var_CodeShtText_Changed = dGridVwCreaCode(e.ColumnIndex, e.RowIndex).Value.ToString

    If Not CatGenieConnPublic.State = ConnectionState.Open Then
        CatGenieConnPublic.Open()
    End If
    da_CodeShtText_Changed = New OleDb.OleDbDataAdapter(qryStr_CodeShtText_Changed, CatGenieConnPublic)
    da_CodeShtText_Changed.Fill(ds_CodeShtText_Changed, "Code_SAP")
    cb_CodeShtText_changed = New OleDb.OleDbCommandBuilder(da_CodeShtText_Changed)

ds_CodeShtText_Changed.Tables("Code_SAP").Rows(1).Item("Code_Descrip") = var_CodeShtText_Changed

To save the changes (following sub being called from a Button_Click): Private Sub Save_Changed_CodeShtText() da_CodeShtText_Changed.Update(ds_CodeShtText_Changed, "Code_SAP") MsgBox("Changes saved to database....", vbOKOnly + vbInformation)

    If CatGenieConnPublic.State = ConnectionState.Open Then
        CatGenieConnPublic.Close()
    End If
    'SET BOOLEAN TO FALSE AS CHANGED VALUES HAVE BEEN SAVED
    bool_CellVal_HasChanged = False
End Sub

PS. Somehow I am not able to place all the code lines together, pl pardon me.

Unknown Unknown · Accepted Answer · 2015-07-21T13:19:32

What I was missing out on was incrementing the "row" count in the code line:

ds_CodeShtText_Changed.Tables("Code_SAP").Rows(rowNum_Increment - 1).Item("Code_Descrip") = var_CodeShtText_Changed

So every time the user changes data in the particular cell, rows number in incremented by "1" and is collected in the dataset.

VB.Net affect changes to a table

1 Answers