Records won't update in datagridview

195 views Asked by At

i'm having a problem in updating records on my datagridview. when i tried to update the records in the datagridview in form 2,the record changes but the datagridview in form1 wont update, the update i made on the records stays (permanently, but just on the datagridview 1 in form 2. it wont show on datagridview in form 1).

oprdg = datagridview1 on form 1, the code below is the code for updating in form 2

here's my code:

  Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
    provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
    'Change the following to your access database location
    dataFile = "C:\Users\DELL\Downloads\ODRNEWSYSTEM\ODRNEWSYSTEM\Database1.accdb"
    connString = provider & dataFile
    myConnection.ConnectionString = connString
    myConnection.Open()
    Try
        Me.Validate()
        Me.OPRBindingSource.EndEdit()
        Me.OPRTableAdapter.Update(Me.Database1DataSet.OPR)
        MsgBox("Update successful")
        myConnection.Close()
    Catch ex As Exception
        MsgBox("Update failed")
    End Try
End Sub

i tried some code like the code below but an error occured,, the error is pointing on the "da.fill(dt)" part.. i forgot what the error is though.

Private Sub RefreshData()
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\DELL\Downloads\ODRNEWSYSTEM\ODRNEWSYSTEM\Database1.accdb"
        Dim myConnection As OleDbConnection = New OleDbConnection()
        myConnection.ConnectionString = connString
        Dim da As New OleDb.OleDbDataAdapter("SELECT OPROffice as [OPROffice], " & _
                                             "ID as [ID], Elements,Objectives, Measures,Baseline_as_of_2013,Target_by_2016,Actual_Accomplishments_2014,Actual_Accomplishments_2015,Actual_Accomplishments_2016,Initiatives " & _
                                             " FROM OPR ORDER BY ID", myConnection)
        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in data table into datagridview
        Me.OPRDataGridView.DataSource = dt
        Form1.oprdg.DataSource = dt
        'close connection
        myConnection.Close()
    End Sub

here is another code which the error is like the error in the above code:

Public Sub Gridrefresh(ByVal s As String, ByVal dgv As DataGridView)
        Dim dt As New DataTable()
        Dim cmd As New OleDbCommand("select * from  " + s + "", myConnection)
        cmd.CommandType = CommandType.Text
        Dim da As New OleDbDataAdapter(cmd)
        da.Fill(dt)
        OPRDataGridView.DataSource = dt
    End Sub

and here's an image for the problem in my system :

https://i.stack.imgur.com/QgNNq.png

2

There are 2 answers

2
AudioBubble On

Before you assign a connection to the data adapter and do a fill you need a myConnection.Open()

With your first example btnUpdate_Click has the code ever worked?

Have a look at the article https://msdn.microsoft.com/en-us/library/system.data.common.dataadapter.update%28v=vs.110%29.aspx

0
qaZ D On

i found an answer. i tried to use this code:

Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        Try
            Dim dt As New DataTable
            Dim str As String = String.Empty
            provider = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source ="
            'Change the following to your access database location
            dataFile = "C:\Users\DELL\Downloads\ODRNEWSYSTEM\ODRNEWSYSTEM\Database1.accdb"
            connString = provider & dataFile
            myConnection.ConnectionString = connString
            myConnection.Open()
            Me.Validate()
            Me.OPRBindingSource.EndEdit()
            Me.OPRTableAdapter.Update(Me.Database1DataSet.OPR)
            Dim cmd = New OleDb.OleDbCommand("UPDATE [OPR] SET [OPROffice] = @OPROffice, [Elements] = @Elements, [Objectives] = @Objectives, [Measures] = @Measures, [Baseline_as_of_2013] = @Baseline_as_of_2013, [Target_by_2016] = @Target_by_2016, [Actual_Accomplishments_2014] = @Actual_Accomplishments_2014, [Actual_Accomplishments_2015] = @Actual_Accomplishments_2015, [Actual_Accomplishments_2016] = @Actual_Accomplishments_2016, [Initiatives] = @Initiatives Where ID = @ID", myConnection)
            cmd.Parameters.AddWithValue("@OPROffice", OPROfficeTextBox.Text)
            cmd.Parameters.AddWithValue("@Elements", ElementsTextBox.Text)
            cmd.Parameters.AddWithValue("@Objectives", ObjectivesTextBox.Text)
            cmd.Parameters.AddWithValue("@Measures", MeasuresTextBox.Text)
            cmd.Parameters.AddWithValue("@Baseline_as_of_2013", Baseline_as_of_2013TextBox.Text)
            cmd.Parameters.AddWithValue("@Target_by_2016", Target_by_2016TextBox.Text)
            cmd.Parameters.AddWithValue("@Actual_Accomplishments_2014", Actual_Accomplishments_2014TextBox.Text)
            cmd.Parameters.AddWithValue("@Actual_Accomplishments_2015", Actual_Accomplishments_2015TextBox.Text)
            cmd.Parameters.AddWithValue("@Actual_Accomplishments_2016", Actual_Accomplishments_2016TextBox.Text)
            cmd.Parameters.AddWithValue("@Initiatives", InitiativesTextBox.Text)
            cmd.Parameters.AddWithValue("@ID", IDTextBox.Text)
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("Update failed")
        Finally
            myConnection.Close()
        End Try
        RefreshData()
        OPRDataGridView.RefreshEdit()
    End Sub



Private Sub RefreshData()
        Dim connString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\DELL\Downloads\ODRNEWSYSTEM\ODRNEWSYSTEM\Database1.accdb"
        Dim myConnection As OleDbConnection = New OleDbConnection()
        myConnection.ConnectionString = connString
        Dim da As New OleDb.OleDbDataAdapter("SELECT OPROffice as [OPROffice], " & _
                                             "ID as [ID], Elements,Objectives, Measures,Baseline_as_of_2013,Target_by_2016,Actual_Accomplishments_2014,Actual_Accomplishments_2015,Actual_Accomplishments_2016,Initiatives " & _
                                             " FROM OPR ORDER BY ID", myConnection)
        Dim dt As New DataTable
        'fill data to datatable
        da.Fill(dt)

        'offer data in data table into datagridview
        Me.OPRDataGridView.DataSource = dt
        Form1.oprdg.DataSource = dt
        'close connection
        myConnection.Close()
    End Sub