Datagridview loading database multiple times

Question

There is a datagridview and a button on a form. Same button loads data into datagridview (If Button1.Text = "CHANGE DATABASE" Then) and saves changes. Everything works fine until button is clicked again to load data, then database is loaded again after previous load (if database has 3 rows, those 3 rows are shown again, and again in datagridview on every button click - duplicated but not in Access file only in datagridview).

Also second time I click to UPDATE CHANGES database I get this error : Concurrency violation.

    Imports System.Data.OleDb
Public Class Form1

    Dim DataSet1 As New DataSet()

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PD_D.mdb"
        Dim SQLString As String = "SELECT * FROM DIV"
        Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
        Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)

        Using OleDBConn1
            If Button1.Text = "CHANGE DATABASE" Then
                OleDbDataAdapter1.Fill(DataSet1, "DIV")
                DataGridView1.DataSource = DataSet1.Tables("DIV")

                Button1.Text = "UPDATE CHANGES"
                Button1.ForeColor = Color.DarkRed


            Else

                If DataSet1.HasChanges Then

                    Using con = New OleDbConnection(ConnString)

                        Me.Validate()
                        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(OleDbDataAdapter1)
                        OleDbDataAdapter1.SelectCommand = New OleDbCommand(SQLString, con)
                        OleDbDataAdapter1.DeleteCommand = builder.DataAdapter.DeleteCommand
                        OleDbDataAdapter1.InsertCommand = builder.DataAdapter.InsertCommand
                        OleDbDataAdapter1.UpdateCommand = builder.DataAdapter.UpdateCommand
                        OleDbDataAdapter1.Update(DataSet1.Tables("DIV").GetChanges())


                    End Using

                Else
                    'OleDBConn1.Close()
                    GoTo ext
                End If

ext:
                '
                Button1.Text = "CHANGE DATABASE"
                Button1.ForeColor = Color.Black


            End If


        End Using

    End Sub

Answer 1

You are getting duplicate entries due to your DataSet's DataTable (DIV) being filled every time you click on your button.

The following line in your button 1 click will solve it:

DataSet1 = New DataSet()

Your code will look like this afterwards:

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\PD_D.mdb"
    Dim SQLString As String = "SELECT * FROM DIV"
    Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
    Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)

    Using OleDBConn1
        If Button1.Text = "CHANGE DATABASE" Then
            DataSet1 = New DataSet() 'Creates new instance of DataSet1
            OleDbDataAdapter1.Fill(DataSet1, "DIV")
            DataGridView1.DataSource = DataSet1.Tables("DIV")

            Button1.Text = "UPDATE CHANGES"
            Button1.ForeColor = Color.DarkRed
        Else
            If DataSet1.HasChanges Then
                Using con = New OleDbConnection(ConnString)

                    Me.Validate()
                    Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(OleDbDataAdapter1)
                    OleDbDataAdapter1.SelectCommand = New OleDbCommand(SQLString, con)
                    OleDbDataAdapter1.DeleteCommand = builder.DataAdapter.DeleteCommand
                    OleDbDataAdapter1.InsertCommand = builder.DataAdapter.InsertCommand
                    OleDbDataAdapter1.UpdateCommand = builder.DataAdapter.UpdateCommand
                    OleDbDataAdapter1.Update(DataSet1.Tables("DIV").GetChanges())
                End Using
            Else
                'OleDBConn1.Close()
                GoTo ext
            End If
ext:
                '
                Button1.Text = "CHANGE DATABASE"
                Button1.ForeColor = Color.Black
        End If
    End Using
End Sub