I have a SQLite database with a table named 'hosts'. This table has two text columns, where column2 (col2) is represented as a DataGridViewCheckBoxColumn (see Sub dgvConfig).
Private Sub dgvConfig(ByRef dgv As DataGridView)
Try
Dim col1 As New DataGridViewTextBoxColumn
col1.Name = "col1"
col1.HeaderText = "Host"
col1.DataPropertyName = "myKey"
Me.dgvRmServers.Columns.Add(col1)
Dim col2 As New DataGridViewCheckBoxColumn
col2.Name = "col2"
col2.HeaderText = "Active"
col2.DataPropertyName = "myValue"
col2.TrueValue = "true"
col2.FalseValue = "false"
Me.dgvRmServers.Columns.Add(col2)
dgvRmServers.AllowUserToAddRows = False
dgvRmServers.AllowUserToDeleteRows = False
dgvRmServers.ReadOnly = False
dgvRmServers.DataSource = _ConfigDb.DataSet.Tables("hosts")
Catch ex As Exception
End Try
End Sub
As you can see, the DataGridView property ReadOnly is set to False.
I use an EventHandler named CurrentCellDirtyStateChanged.
Private Sub dataGridView_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) Handles dgvRmServers.CurrentCellDirtyStateChanged
Try
If dgvRmServers.IsCurrentCellDirty Then
dgvRmServers.CommitEdit(DataGridViewDataErrorContexts.Commit)
_ConfigDb.SqliteUpdateKvp("hosts", _dsxConfigDb.DataSet.Tables("hosts").Rows(dgvRmServers.CurrentCell.RowIndex)("myKey"), dgvRmServers.Rows(dgvRmServers.CurrentCell.RowIndex).Cells(1).Value)
End If
Catch ex As Exception
End Try
End Sub
The next function is used for saving the updated rows back to the database.
Public Function SqliteUpdateKvp(ByVal tbl As String, ByVal myKey As String, ByVal myValue As String) As Boolean
Try
Using con As New SQLiteConnection(_conn)
con.Open()
Using da As New SQLiteDataAdapter("SELECT * FROM hosts", con)
Using cb As New SQLiteCommandBuilder(da)
da.UpdateCommand = cb.GetUpdateCommand
da.Update(_ConfigDb.DataSet, tbl)
End Using
End Using
con.Close()
End Using
Catch ex As Exception
End Try
End Function
Now the situation is, that the updates theoretically work, but not directly after program start. Every next action executes the previous action, so I think it has to do with my dataset respectively it`s initialization from the beginning.
That's why I added the function for the initial filling of my dataset.
Public Function ReadMyConfDb(ByRef ds As DataSet) As Boolean
Try
Using con As New SQLiteConnection(_conn)
con.Open()
Using tr As SQLiteTransaction = con.BeginTransaction()
Using da As New SQLiteDataAdapter("SELECT name FROM sqlite_master WHERE type= 'table'", _conn)
Using cb As New SQLiteCommandBuilder(da)
da.Fill(ds, "confDbTables")
End Using
End Using
End Using
con.Close()
End Using
Using con As New SQLiteConnection(_conn)
con.Open()
Using tr As SQLiteTransaction = con.BeginTransaction()
For Each row As DataRow In ds.Tables("confDbTables").Rows
Using da As New SQLiteDataAdapter(String.Format("SELECT * FROM {0}", row("name")), _conn)
Using cb As New SQLiteCommandBuilder(da)
da.Fill(ds, row("name"))
End Using
End Using
Next
tr.Commit()
End Using
con.Close()
End Using
If ds.Tables.Count > 0 Then
Return True
Else
Return False
End If
Catch ex As Exception
Return False
End Try
End Function
In the meantime, I was able to solve the problem by myself with the help of two links from the Internet (one of them from Stackoverflow).
The problem was due to the fact that at the time I updated the database, a commit flag was apparently not set internally.
When debugging, I could see that the DataSet had been updated correctly, but a flag was still missing, which meant that the changes were not written and no error message was displayed. This situation was very confusing.
Link 1: http://www.codingeverything.com/2013/01/firing-datagridview-cellvaluechanged.html
Link 2: DataGridView row is still dirty after committing changes
From link 1 I first took the use of the events, while link 2 provided a valuable tip regarding the use of a BindingSource and a Handler.
Since my problem already exists in a very similar way in an old message on Stackoverflow (System.Data.SQLite.SQLiteDataAdapter.Update keeps database a step behind), which has not received a single reply, I would like to publish my solution to the problem here.
First the function dgvConfig which now uses a global BindingSource.
Private Sub dgvConfig(ByRef dgv As DataGridView)
Try
Dim col1 As New DataGridViewTextBoxColumn
col1.Name = "col1"
col1.HeaderText = "Host"
col1.DataPropertyName = "myKey"
Me.dgvRmServers.Columns.Add(col1)
Dim col2 As New DataGridViewCheckBoxColumn
col2.Name = "col2"
col2.HeaderText = "Active"
col2.DataPropertyName = "myValue"
col2.TrueValue = "true"
col2.FalseValue = "false"
bs.DataSource = _dsxConfigDb.DataSet.Tables("hosts")
Me.dgvRmServers.Columns.Add(col2)
Me.dgvRmServers.AutoGenerateColumns = False
Me.dgvRmServers.SelectionMode = DataGridViewSelectionMode.FullRowSelect
Me.dgvRmServers.AllowUserToAddRows = False
Me.dgvRmServers.AllowUserToDeleteRows = False
Me.dgvRmServers.DataSource = _bs
AddHandler _bs.ListChanged, AddressOf CustomersBindingSource_ListChanged
Catch ex As Exception
End Try
End Sub
Next are the EventHandlers I used.
CustomersBindingSource_ListChanged:
Public Sub CustomersBindingSource_ListChanged(sender As Object, e As ListChangedEventArgs)
Try
_dsxConfigDb.SqliteUpdateKvp("hosts", _dsxConfigDb.DataSet.Tables("hosts").Rows(dgvRmServers.CurrentCell.RowIndex)("myKey"), dgvRmServers.Rows(dgvRmServers.CurrentCell.RowIndex).Cells(1).Value)
Catch ex As Exception
End Try
End Sub
DataGridView_CurrentCellDirtyStateChanged:
Private Sub DataGridView_CurrentCellDirtyStateChanged(sender As System.Object, e As EventArgs) Handles dgvRmServers.CurrentCellDirtyStateChanged
If dgvRmServers.IsCurrentCellDirty Then
dgvRmServers.EndEdit(DataGridViewDataErrorContexts.Commit)
End If
End Sub
DataGridView_CellValueChanged:
Private Sub DataGridView_CellValueChanged(sender As DataGridView, e As DataGridViewCellEventArgs) Handles dgvRmServers.CellValueChanged
If e.RowIndex = -1 Then
Exit Sub
Else
If dgvRmServers.IsCurrentCellDirty Then
_bs.EndEdit()
End If
End If
End Sub
Finally, the function that writes my changes back to the database.
Public Function SqliteUpdateKvp(ByVal tbl As String) As Boolean
Try
Using con As New SQLiteConnection(_conn)
con.Open()
Using da As New SQLiteDataAdapter("SELECT * FROM hosts", con)
Using cb As New SQLiteCommandBuilder(da)
'da.UpdateCommand = cb.GetUpdateCommand
Dim testo = da.Update(_dsxConfigDb.DataSet, tbl)
End Using
End Using
con.Close()
End Using
Return True
Catch ex As Exception
_dsxLogger.WriteMessage(ex.Message, TraceEventType.Error)
Return False
End Try
End Function
Now it is possible to write the changed states of the CheckBoxes correctly into the database without any problems.
Summarized _bs.EndEdit() did the trick.