Update OleDb from DataGridView as Validated Using CommandBuilder

Question

I just found out about the CommandBuilder, and thought it sounded straight foward and easy to use. Clearly I'm still missing things. I've got a DataGridView that successfully updates dbSet table called Customers. But it's not updating to the actual database file:

...
Dim ConMain As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\dummy_data.accdb")
...
Private Sub CustomerDataGridView_RowValidated(ByVal sender As Object, _
    ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
    Handles CustomerDataGridView.RowValidated

    Me.CustomersTableAdapter.Update(Me.Dummy_dataDataSet.Customers)
    Dim CustomerAdapter As New OleDbDataAdapter("Select * From Customers", ConMain)
    Dim ObjComander As New OleDbCommandBuilder(CustomerAdapter)
    CustomerAdapter.Update(Dummy_dataDataSet, "Customers")

End Sub

It doesn't throw an error, and any changes I've made are kept in the memory (I can open and close the form and the changes will remain), but they are not actually written to the DB. What am I missing?

@TonyShih I'm not sure. I didn't set Vitual Mode to True. Should I? — Atl LED, Mar 19 '13 at 12:53
@TonyShih On my version, that didn't seem to have an effect? — Atl LED, Mar 19 '13 at 18:54

score 0 · Answer 1 · answered Mar 19 '13 at 09:24

0

Try this

Dim ConMain As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\dummy_data.accdb")
Dim m_DtCustomer As New DataTable
Dim CustomerAdapter As OleDbDataAdapter
Dim m_Bsource As New BindingSource

'Populate the datagridview

Sub FillDataGrid()

    CustomerAdapter = New OleDbDataAdapter("Select * From Customers", ConMain)
    m_DtCustomer.Clear()
    CustomerAdapter.Fill(m_DtCustomer)
    m_Bs.DataSource = m_DtCustomer
    CustomerDataGridView.Datasource = m_Bs
End Sub

'Update changes to the database

Sub UpdateDatabase()

    Dim ObjComander As New OleDbCommandBuilder(CustomerAdapter)
    CustomerAdapter.Update(m_DtCustomer)

End Sub

Private Sub CustomerDataGridView_RowValidated(ByVal sender As Object, _
ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) _
Handles CustomerDataGridView.RowValidated

    Call Me.UpdateDatabase()

End Sub

answered Mar 19 '13 at 09:24

Winston Madiano

87
2
11

I think there is a small typo, or I'm not understanding something. Should m_Bs.DataSource be m_Bsource? – Atl LED Mar 19 '13 at 13:50
You can remove the m_Bs and directly set the m_Dtcustomer as the datasource for the datagridview. – Winston Madiano Mar 20 '13 at 01:45
I get that for that line, but I think the one above it should read mBsource. If that's right then I keep getting: Error: Object reference not set to an instance of an object. On UpdateDatabase() – Atl LED Mar 20 '13 at 01:58
even if I move 'CustomerAdapter = New OleDbDataAdapter("Select * From Customers", ConMain)' in the Update sub, I'm actually not getting it to write (in fact it's now lost when the form as closed, in my solution that was retained). – Atl LED Mar 20 '13 at 02:08
Try to call the FillDatagrid sub on form_load. – Winston Madiano Mar 20 '13 at 02:24
I already had that. Again I've got it so it won't throw the error, but it's not updating the DB (if I close Excel and Open it in Access there aren't any changes to the table). It should read `m_Bsource.DataSource = m_DtCustomer` and `CustomerDataGridView.DataSource = m_Bsource` right? – Atl LED Mar 20 '13 at 02:33

Update OleDb from DataGridView as Validated Using CommandBuilder

1 Answers1