Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click


        Dim TextFieldParser1 As New TextFieldParser("D:\VS_Projects\VB_WF_DataGrid2019\VB_WF_DataGrid2019\organizations-100.txt")

        TextFieldParser1.Delimiters = New String() {","}

        While Not TextFieldParser1.EndOfData
            Dim Row1 As String() = TextFieldParser1.ReadFields()

            If dgvData.Columns.Count = 0 Then
                Dim i As Integer

                For i = 0 To Row1.Count - 1
                    dgvData.Columns.Add(Row1(i), Row1(i))
                Next
            Else
                dgvData.Rows.Add(Row1)
            End If

        End While

        TextFieldParser1.Close()

    End Sub

    Dim prevCellContents As String

    Private Sub dgvData_CellBeginEdit(sender As Object, e As DataGridViewCellCancelEventArgs) Handles dgvData.CellBeginEdit
        prevCellContents = dgvData.CurrentCell.Value
    End Sub

    Private Sub dgvData_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles dgvData.CellEndEdit

        Dim currentCellContents As String = dgvData.CurrentCell.Value
        Dim rowID As String
        Dim changedFieldIndex As Int16

        If currentCellContents <> prevCellContents Then
            ' the cell changed
            rowID = dgvData.Item(0, dgvData.CurrentCell.RowIndex).Value
            changedFieldIndex = dgvData.CurrentCell.ColumnIndex

            If UpdateCSVFile(rowID, changedFieldIndex, currentCellContents) Then
                MsgBox($"You changed the value from {prevCellContents} to {currentCellContents}")
            Else
                ' the save failed, put the old value back in the cell
                dgvData.CurrentCell.Value = prevCellContents
            End If
        Else
            MsgBox("Edit has been cancelled")
        End If

    End Sub

    Private Function UpdateCSVFile(recID As String, columnNumber As Int16, newValue As String) As Boolean

        Try
            Dim rwNumber As Long = 0
            Dim parser As New TextFieldParser("F:\temp\organizations-100.txt")
            parser.Delimiters = New String() {","}

            Dim tempFile As New StreamWriter("F:\temp\temp.txt")
            Dim tempFileFields As New List(Of String)
            Dim foundID As Boolean

            While Not parser.EndOfData
                Dim fields As String() = parser.ReadFields()

                ' if the record id matches, replace the value
                foundID = fields(0) = recID
                Dim fld As String
                For i = 0 To fields.Count - 1
                    fld = fields(i)
                    If fld.IndexOf(",") > 0 Then
                        ' the field itself contains a comma, it must be surrounded by """" to write correctly
                        ' with streamwriter
                        If foundID And i = columnNumber Then
                            ' this is the line that has the edit field value
                            fld = """" & newValue & """"
                        Else
                            fld = """" & fld & """"
                        End If
                    Else
                        If foundID And i = columnNumber Then
                            fld = newValue
                        End If
                    End If
                    tempFileFields.Add(fld)
                Next

                tempFile.WriteLine(String.Join(",", tempFileFields))
                tempFileFields.Clear()
            End While

            parser.Close()
            tempFile.Close()

            ' rename the old file as a backup with date time stamp
            ' rename the temp file as the file name
            Dim dateTimeStr As String = Format(Now, "MMddyyHmmss")
            My.Computer.FileSystem.RenameFile("F:\temp\organizations-100.txt", $"organizations-100-{dateTimeStr}.txt")
            My.Computer.FileSystem.RenameFile("F:\temp\temp.txt", "organizations-100.txt")

            Return True
        Catch ex As Exception
            MsgBox($"Save failed. {ex.Message}")
            Return False
        End Try

    End Function

    Private Sub dgvData_CellMouseClick(sender As Object, e As DataGridViewCellMouseEventArgs) Handles dgvData.CellMouseClick
        Static lastSelectedCell As String

        If lastSelectedCell Is Nothing Then lastSelectedCell = ""

        Try
            If lastSelectedCell.Length() > 0 Then
                ' reset the previously selected cell's background
                dgvData.Rows(lastSelectedCell.Split(",")(0)).Cells(lastSelectedCell.Split(",")(1)).Style.SelectionBackColor = Color.White

            End If
            dgvData.Rows(e.RowIndex).Cells(e.ColumnIndex).Style.SelectionBackColor = Color.Blue
            lastSelectedCell = e.RowIndex.ToString & "," & e.ColumnIndex  ' remember this selection

        Catch ex As Exception
            Exit Try
        End Try

    End Sub