VB.Net WinForm DataGridView and MS Access Database, Data Update Issue

Mar 22 2023 4:29 PM

Hi,

 

VB.Net WinForm DataGridView and MS Access Database, Data Update Issue

I need a simple code for VB.Net 2022 application which uses an MS Access database file with one Table (Table1) and WinForms application has 4 TextBoxes namely

Username
City
ZIP
Phone

and a Button to add these four TextBox data to DataGridView1

The problem with my code is it loads data from Database1.accdb file to DataGridView1 during Form1 load but in run-time if I try to add new data from the four TextBoxes to DataGridView1 and then try to save the old + new data (update) to the Database1.accdb file it gives error.

I need the smallest possible code with OleDb

The method should use OleDb and MS Access database file created using MS Access (I am using MS Access 2019)

Currently Using This Code

Imports System.Data.OleDb

Public Class Form1
    Public adapter As OleDbDataAdapter
    Public dt As DataTable

    Private dgvCount = 0

    Private Sub loaddata()
        dt = New DataTable()
        Dim ds As DataSet = New DataSet()
        Dim conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Database1.accdb;Persist Security Info=True;")
        Dim selectSql = "SELECT * From Table1"
        conn.Open()
        adapter = New OleDbDataAdapter(selectSql, conn)
        adapter.Fill(dt)
        DataGridView1.DataSource = dt
    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        loaddata()
    End Sub

    Private Sub ButtonAdd_Click(sender As Object, e As EventArgs) Handles ButtonAdd.Click
        Try
            Dim scb = New OleDbCommandBuilder(adapter)
            adapter.Update(dt)

            MessageBox.Show("OK!")
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
    End Sub

    Private Sub dgvAdd()
        For Each row As DataGridViewRow In DataGridView1.Rows
            DataGridView1.Rows.Add()
            DataGridView1.ForeColor = Color.Black
            DataGridView1.Rows(dgvCount).Cells(0).Value = dgvCount
            DataGridView1.Rows(dgvCount).Cells(1).Value = TextBoxUsername.Text
            dgvCount = dgvCount + 1
        Next
    End Sub
End Class

 


Answers (4)