Click here to Skip to main content
15,947,389 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Updated 28-Aug-17 4:10am

1 solution

I figured it out myself. I was mistakenly disposing my database connection in my finally clause after retrieving the data so I removed that code. I guess that is the reason I was getting the connection string error.

I then modified my update button code slightly on now if works fine.

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


      If con.State = ConnectionState.Closed Then con.Open()
       Dim RowsUpdated As Integer = 0

       Try

           RowsUpdated = da.Update(dt) 'Update the database

       Catch ex As Exception

           MsgBox(ex.Message)

       Finally
           con.Close()
       End Try

       MsgBox("RowsUpdated = " & RowsUpdated)


   End Sub
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900