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

Jayanth Devarayanadurga

Hi,

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jayanth Devarayanadurga3y ago
Same issue after Amit's new code. Doesn't solve the issue.

Amit Mohanty3y ago

Check this

Private Sub addButton_Click(sender As Object, e As EventArgs) Handles addButton.Click
    Dim conn As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Path\To\Database.accdb;")

    Dim sql As String = "INSERT INTO Table1 (Username, City, ZIP, Phone) VALUES (@Username, @City, @ZIP, @Phone)"

    Dim cmd As New OleDbCommand(sql, conn)
    cmd.Parameters.AddWithValue("@Username", usernameTextBox.Text)
    cmd.Parameters.AddWithValue("@City", cityTextBox.Text)
    cmd.Parameters.AddWithValue("@ZIP", zipTextBox.Text)
    cmd.Parameters.AddWithValue("@Phone", phoneTextBox.Text)
    conn.Open()
    cmd.ExecuteNonQuery()
    conn.Close()

    loadData()
End Sub

Jayanth Devarayanadurga3y ago
It works but with this exception message.

Syntax error in INSERT INTO statement

Naimish Makwana3y ago

Try below code that should solve the update issue you're facing:

Imports System.Data.OleDb

Public Class Form1

    Private adapter As OleDbDataAdapter
    Private dt As DataTable

    Private Sub loaddata()
        dt = New DataTable()
        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"
        adapter = New OleDbDataAdapter(selectSql, conn)
        Dim cb As OleDbCommandBuilder = New OleDbCommandBuilder(adapter)
        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 newRow As DataRow = dt.NewRow()
            newRow("Username") = TextBoxUsername.Text
            newRow("City") = TextBoxCity.Text
            newRow("ZIP") = TextBoxZIP.Text
            newRow("Phone") = TextBoxPhone.Text
            dt.Rows.Add(newRow)

            adapter.Update(dt)

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

End Class

Thanks

Naimish