vb.net 中 UPDATE 语句访问数据库中的语法错误

Syntax error in UPDATE statement access database in vb.net

提问by user2742837

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:

我刚刚开始学习 VB.net 几个星期。我想制作一个表单并将数据从文本框中发送到 ms access 数据库 (*.accdb) 文件中的特定单元格。但我写的代码给出了以下错误：

Syntax error in UPDATE statement.

UPDATE 语句中的语法错误。

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

回答by Gord Thompson

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

您需要使用OleDbCommandBuilder的.QuotePrefix和.QuoteSuffix属性将表和字段名称括在方括号中。也就是说，而不是仅仅

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 语句

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

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

如果表名或任何字段名恰好是Access SQL 中的保留字，则这是必需的。

回答by Alvin Quezon

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

希望上面提到的这些代码可以帮助您解决问题。祝你有美好的一天和快乐的编码:)

回答by Johbosco Adam

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

通过用方括号括起属性，它似乎有效我已经尝试过了，它有效

回答by uld

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