Syntax error in UPDATE statement access database in vb.net

Question

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()

Do you have a primary key defined for the table 'flow1'?

Steve
– Steve

2013-09-03 12:37:06 +00:00
Commented Sep 3, 2013 at 12:37 — Steve
– Steve, Commented Sep 3, 2013 at 12:37

K.Dᴀᴠɪs · Accepted Answer · 2023-07-21 01:41:24Z

3

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.

edited Jul 21, 2023 at 1:41

K.Dᴀᴠɪs

10.1k13 gold badges36 silver badges46 bronze badges

answered Oct 30, 2014 at 13:26

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ania Warzecha · Accepted Answer · 2013-09-22 18:34:52Z

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 :)

shA.t · Accepted Answer · 2015-03-15 16:59:01Z

0

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

edited Mar 15, 2015 at 16:59

shA.t

17k5 gold badges59 silver badges121 bronze badges

answered Mar 15, 2015 at 16:18

Johbosco Adam

1

1 Comment

Stuart Siegler Over a year ago

You can use the { } button to enclose code in a block. It makes it more readable

CubeJockey · Accepted Answer · 2015-08-19 16:26:54Z

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

Lesego Masiane · Accepted Answer · 2022-03-25 09:57:02Z

0

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.

answered Mar 25, 2022 at 9:57

Lesego Masiane

1

Collectives™ on Stack Overflow

Syntax error in UPDATE statement access database in vb.net

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related