DataGridView : Update SQL Database when cell value changed

Question

I have created a form which contains DataGridView and have created first column as checkbox column and fullrowselect is True.
I want like when I click the checkbox of the row or change the checkbox value of the selected row, that particular record should be updated in the database...

I wrote the query properly but facing some problems mentioned below.

Now I first tried in cell click event...

 Private Sub DGVHelp_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DGVHelp.CellClick
    Try
        If PubHelpCode = "ACTMAST" And e.ColumnIndex = 0 Then 'if only checkbox column is clicked
            cmd = New SqlCommand("Update FAMPAR SET Tag = '" & DGVHelp(0, e.RowIndex).Value & "' where Account_Code = '" & DGVHelp(1, e.RowIndex).Value & "' ", con1)
            ExecuteQuery(con1, "EDITCHECKBOX") ' Its my UDF
            DGV_Reset()' This clears the datagrid and fetch the data again
        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

When I click the checkbox cell (First column), the event first runs and then the value of the cell is changed... i.e during cellclick event, the DGVHelp(0, e.RowIndex).Value doesn't change its value if its ticked or unticked.

I even tried cellvaluechanged event but it hanged the program by some continuous process that I don't know...

Which event should I use to complete my task??

If any other opinion/idea relating to this task is there then please share...

The Code OneDrive link shared below..
Solution Code link

When you click on a DataGridViewCheckBox, the changes which you make on the cell doesn't commit immediately to the data source until you finish editing the cell, then changes will be pushed to data source. The CellClick or CellContentClick will be OK, but you need to commit changes to data source, otherwise the value which you read is not the value which you see. Take a look at this post:DataGridView CheckBox column doesn't apply changes to the underlying bound object — Reza Aghaei
Don't look for any other events, just use CellClick or CellContentClick this way. — Reza Aghaei

Hadi Hadi · Accepted Answer · 2016-11-15T13:37:37

You have to use DataGridView.CellValueChanged event. This is the best event to work with in your case. It is not a good idea to use MouseClick event.

https://msdn.microsoft.com/en-us/library/system.windows.forms.datagridview.cellvaluechanged(v=vs.110).aspx

Inside the event sub use the DataGridViewCellEventArgs property e.columnIndex to check that the colunn edited is the one containing the checkbox. Otherwise you have to check your code.

it is better to use a Using clause when working with sqlcommand class

Using cmd as New SqlCommand("Update FAMPAR SET Tag = '" & DGVHelp(0, e.RowIndex).Value & "' where Account_Code = '" & DGVHelp(1, e.RowIndex).Value & "' ", con1)

        ExecuteQuery(con1, "EDITCHECKBOX") ' Its my UDF

End Using

EDIT 1:

After checking you solution. it is not so clear but you can do some workaround.

1- Define a boolean variable m_BoolValueChanged

Private m_BoolValueChanged as Boolean = True

2- Use this variable in DGVHelp_CellValueChanged Sub to prevent infinite loop. like to following:

Private Sub DGVHelp_CellValueChanged(sender As Object, e As DataGridViewCellEventArgs) Handles DGVHelp.CellValueChanged
    Try
        If PubHelpCode = "ACTMAST" And e.ColumnIndex = 0 Then

            If m_BoolValueChanged Then

            m_BoolValueChanged = False


            cmd = New SqlCommand("Update FAMPAR SET Tag = '" & DGVHelp(0, e.RowIndex).Value & "' where Account_Code = '" & DGVHelp(1, e.RowIndex).Value & "' ", con1)
            ExecuteQuery(con1, "EDITCHECKBOX")
            Dim ri As Integer = DGVHelp.CurrentRow.Index
            DGV_Reset()
            DGVHelp.Rows(ri).Selected = True

            m_BoolValueChanged = False


            End If


        End If
    Catch ex As Exception
        MsgBox(ex.ToString)
    End Try
End Sub

You may find better solution but this will helps

DataGridView : Update SQL Database when cell value changed

2 Answers