How to update database from bound datagridview

See more:

Oracle

DataAdapter

I am trying to update a database from a bound datagridview but I am not having very good luck. I want to first display the data in the datagridview then after making changes to the datagridview, I want to hit an update button to update the database. I have created the code that binds the database to the datagridview and that works fine. What I am having problems with is how to update the database afterwards. I have created a commandbuilder to automatically generate the updatecommand. If I update the datatable programmatically and then call the dataadapter update, it works fine. However, if I try to call the dataadapter update in my button, it gives me the error:

The ConnectionString property has not been initialized.

I think I am close, but I just can't figure out why this isn't working. Do I need to reinitialize something in my button?

What I have tried:

Dim con As System.Data.OleDb.OleDbConnection = New OleDb.OleDbConnection(connectionString)
    Dim da As OleDbDataAdapter
    Dim ds As DataSet
    Dim dt As DataTable


    Private Sub FormPlannedProductionTimes_Shown(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Shown

        Me.DateTimePickerStart.Value = Today

    End Sub


    Private Sub RefreshDGVMonday()

        con = New OleDb.OleDbConnection(connectionString)
        da = New OleDbDataAdapter
        ds = New DataSet
        dt = New DataTable

        Dim DGV As DataGridView = Me.DGVMonday


        Try

            'Create the select command
            da.SelectCommand = New OleDbCommand(Nothing, con)

           
            da.SelectCommand.CommandText = "SELECT MACHINE AS MACH, OEEDATE, SHIFT1 AS S1, SHIFT2 AS S2, SHIFT3 AS S3 " & _
                                            "FROM PLANNEDPRODUCTIONTIMES " & _
                                            "WHERE OEEDATE = TO_DATE('" & DateTimePickerStart.Value & "', 'MM/DD/YYYY HH:MI:SS AM') " & _
                                            "ORDER BY MACH"

            'Create a command builder to auto generate the update command
            Dim cb As New OleDbCommandBuilder(da)
            da.InsertCommand = cb.GetInsertCommand

            'Clear all columns
            DGV.Columns.Clear()

            'Fill the DataAdapter
            da.Fill(ds, "Monday")
            dt = ds.Tables("Monday")

            'Programatically change the values in the datatable
            'then update the data adapter
            'This works fine, however, if I remove these 2 lines
            'and call theupdate in my button below, I get an error
            dt.Rows(0)!s1 = 1
            da.Update(dt)

            'Set the datasource
            DGV.DataSource = dt

            'Set the width
            DGV.Columns("MACH").Width = 50
            DGV.Columns("OEEDATE").Width = 70
            DGV.Columns("S1").Width = 30
            DGV.Columns("S2").Width = 30
            DGV.Columns("S3").Width = 30

         

        Catch ex As Exception
            MsgBox("Could not refresh the production time data because: " & vbNewLine & vbNewLine & _
                  ex.Message & vbNewLine & _
                  "Contact Admin for more assistance.", MsgBoxStyle.Exclamation, "Error")

        Finally
            con.Close()
            con.Dispose()
            da.Dispose()

        End Try


    End Sub

    Private Sub DateTimePickerStart_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateTimePickerStart.ValueChanged
        RefreshDGVMonday()
    End Sub



    Private Sub BtnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnUpdate.Click

        'This gives me an error
        da.Update(dt)

    End Sub