使用Microsoft JET OLEDB 4.0添加用户记录vb.net 2010数据库(MSaccess)

Question

你好，我在数据库中保存记录有问题，当我调试程序时，它说"OLEDB异常未处理，更新语句中语法错误"你能帮我吗?

这是正确的代码吗?

Imports System.Data.OleDb
Public Class Form2
    Dim contr As String = "Provider= Microsoft.Jet.OLEDB.4.0; Data Source= C:UsersAdministratorDesktopUsers.mdb;"
    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    End Sub
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim con As New OleDbConnection(contr)
        con.Open()
        Dim cmd As New OleDbCommand("UPDATE Usertable SET Username = @Username, Password = @Password WHERE id = @id", con)
        cmd.Parameters.AddWithValue("@Username", TextBox1.Text)
        cmd.Parameters.AddWithValue("@Password", TextBox2.Text)
        cmd.Parameters.AddWithValue("@Id", Label3.Text)
        cmd.ExecuteNonQuery()
        con.Close()
    End Sub
    End Class

Answer 1

Password为MS-Access保留关键字。用方括号括起来

Dim cmd As New OleDbCommand("UPDATE Usertable SET Username = @Username, " & _ 
                            "[Password] = @Password WHERE id = @id", con)

Answer 2

Access/OLE不喜欢命名参数。您需要使用?占位符:

UPDATE Usertable SET Username = ?, Password = ? WHERE id = ?

然后，要将每个参数值与占位符匹配，必须按照占位符在查询中使用的顺序将它们添加到Parameters集合中，而不是按名称。