'Syntax error in UPDATE statement access database in vb.net

I have just started learning VB.net for several weeks. i want to make a form and send data from a text box to a specific cell in ms access database (*.accdb) file. but the code i have writen gives the following error:

Syntax error in UPDATE statement.

i have checked several books and spent hours on internet, but no answer!

    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    Dim cnn1 As New OleDb.OleDbConnection
    con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=  
           E:\Ebook\hararat\GUI\Heat Exchanger Designer\heat.accdb"
    con.Open()
    sql = "SELECT * FROM flow1"
    da = New OleDbDataAdapter(sql, con)
    da.Fill(ds, "flow1")
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    ds.Tables("flow1").Rows(1).Item(1) = "name"
    da.Update(ds, "flow1")
    con.Close()

vb.net ms-access

Solution 1:^[1]

You need to use the .QuotePrefix and .QuoteSuffix properties of the OleDbCommandBuilder to wrap table and field names in square brackets. That is, instead of just

Dim cb As New OleDb.OleDbCommandBuilder(da)

you need to do

Dim cb As New OleDb.OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

That will generate an UPDATE statement of the form

UPDATE [TableName] SET [ColumnName]= ...

which is necessary if the table name or any of the field names happen to be reserved words in Access SQL.

Solution 2:^[2]

Try this one

dim sqlupdate as string = "UPDATE tablename SET column_name = '" & textname.text & "' WHERE column_name = '" & textname.text & "'"

Sometimes errors occur when using the following column names: Username, Password, Date, Time, and much more of this type, try to avoid these column names because this might cause the problem of your issue regarding updating tables. Enable for you to update this kind of column name you need to enclose it with [ and ] so it comes like this: [Username], [Date], etc. so the syntax might go like this:

UPDATE tablename SET [Username] = '" & textname.text & "' WHERE column_name = '" & textname.text & "'"

my codes goes like this:

     Open_Con()
            Dim sqlUpdate As String
            Dim sqlUpdatePass As DialogResult
            sqlUpdate = "UPDATE tblAccounts SET [Password] = '" & txtRPassword.Text & "' WHERE [Username] = '" & txtUsername.Text & "'"
            sqlCmd = New OleDbCommand(sqlUpdate, sqlCon)
            Try
                sqlUpdatePass = MessageBox.Show("Are you sure to save this changes?", "Save changes?", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
                If sqlUpdatePass = vbYes Then
                    sqlCmd.ExecuteNonQuery()
                    MsgBox("Changes are now saved", MsgBoxStyle.Information, "New password has been set.")
                    Call ClearAll()
                    Me.Hide()
                Else
                    Exit Sub
                End If
            Catch ex As Exception
                MsgBox("Could not perform this task because " & ex.Message, MsgBoxStyle.Exclamation, "Error")
            End Try
            sqlCmd = Nothing
            sqlCon.Close()

hope this things mention above codes helps your problem. have a nice day and happy coding :)

Solution 3:^[3]

dim sqlupdate as string="UPDATE [tablename] SET [column_name] = '"& textname.text &"' WHERE [column_name] = '"& textname.text &"';"

By enclose attributes with square brackets, it appears to work I have tried it, it works

Solution 4:^[4]

Imports System.Data.OleDb
Imports System.Data
Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'Database2DataSet.identitas' table. You can move, or remove it, as needed.
        Me.IdentitasTableAdapter.Fill(Me.Database2DataSet.identitas)
    End Sub

    Public Sub clean()
        TextBox1.Clear()
        TextBox2.Clear()
        TextBox3.Clear()
        TextBox4.Clear()
    End Sub

    Public Sub read()
        Call openconn()
        str = "select * from identitas"
        dtadapter = New OleDbDataAdapter(str, con)
        Dim dg As New DataTable
        dg.Clear()
        dtadapter.Fill(dg)
        dgv.DataSource = dg
    End Sub

    Public Sub create()
        Call openconn()
        str = "insert into identitas values ('" & TextBox1.Text & "','" & TextBox2.Text & "','" & TextBox3.Text & "','" & TextBox4.Text & "') "
        cmd = New OleDbCommand(str, con)
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MsgBox("data lebet")
        read()
        clean()
    End Sub

    Public Sub update()
        Call openconn()
        str = "UPDATE identitas SET [Nama] = '" & TextBox2.Text & "',[Alamat] = '" & TextBox3.Text & "', [No] = '" & TextBox4.Text & "' where [NIK] = '" & TextBox1.Text & "'"
        cmd = New OleDbCommand(str, con)
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MsgBox("data ter ubah")
        clean()
        read()
    End Sub

    Public Sub delete()
        Call openconn()
        str = "delete from identitas where NIK = '" & TextBox1.Text & "'"
        cmd = New OleDbCommand(str, con)
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        clean()
    End Sub

    Private Sub btnclose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclose.Click
        Me.Close()
    End Sub

    Private Sub btnc_Click(ByVal sender As System.Object, ByVal e As     System.EventArgs) Handles btnc.Click
        create()
    End Sub

    Private Sub btnr_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnr.Click
        read()
    End Sub

    Private Sub btnclean_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclean.Click
        clean()
    End Sub

    Private Sub btnd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnd.Click
        Dim pesan As String = MsgBox("yakin mau hapus = " & TextBox1.Text & "?", MsgBoxStyle.YesNo)
        If pesan = vbYes Then
            delete()
        End If
        read()
    End Sub

    Private Sub btnu_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnu.Click
        update()
    End Sub

End Class

Solution 5:^[5]

I had same problem, this helped.

"Sometimes errors occur when using the following column names: Username, Password, Date, Time, and much more of this type, try to avoid these column names because this might cause the problem of your issue regarding updating tables. Enable for you to update this kind of column name you need to enclose it with [ and ] so it comes like this: [Username], [Date], etc. so the syntax might go like this: "

I renamed the columns in Access (e.g Password1,Username1) as the same words password, username might be reserved in vb.net. Thanks for this response.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Gord Thompson
Solution 2	Ania Warzecha
Solution 3	shA.t
Solution 4	CubeJockey
Solution 5	Lesego Masiane

'Syntax error in UPDATE statement access database in vb.net

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]