MySqlConnection 执行非查询更新错误

Question

从 OleDB 更改为 MySqlConnect 后，我​​遇到了 ExecuteNonQuery 在 UPDATE sql 中抛出错误的问题 - 现有代码工作正常，使用提交的 OleDB 行更新访问数据库:

Public Function InsertClassRecord(ByVal UserName As String, ByVal Instructor As String, _
                                  ByVal DateCompleted As Date, ByVal Completed As Boolean, _
                                  ByVal Enrolled As Boolean, ByVal ClassName As String, _
                                  ByVal ClassDate As Date, ByVal WaitListed As Boolean, _
                                  ByVal Walkin As Boolean) As Object

    Dim connStr As String = "Datasource=localhost;Database=MYSQL_Training;uid=username;pwd=password;"

    'Dim conn As New Data.OleDb.OleDbConnection(connStr)
    Dim conn As New MySqlConnection(connStr)

    conn.ConnectionString = connStr
    conn.Open()

    Dim sql As String = "INSERT INTO EnrollmentsTbl (" & _
    "[UserName],[SubmitTime],[ClassTime],[ClassDate],[Enrolled],[ClassName],[WaitListed]," & _
    "[Instructor],[DateCompleted],[Completed],[Walkin]) VALUES " & _
    "(@UserName, @SubmitTime, @ClassTime, @ClassDate, @Enrolled, @ClassName, @WaitListed, " & _
    "@Instructor, @DateCompleted, @Completed, @Walkin) "

    'Dim comm As New Data.OleDb.OleDbCommand(sql, conn)
    Dim comm As New MySqlCommand(sql, conn)

    comm.Parameters.AddWithValue("@UserName", UserName)
    comm.Parameters.AddWithValue("@SubmitTime", DateTime.Now.ToString())
    comm.Parameters.AddWithValue("@ClassTime", "0800")
    comm.Parameters.AddWithValue("@ClassDate", ClassDate)
    comm.Parameters.AddWithValue("@Enrolled", Enrolled)
    comm.Parameters.AddWithValue("@ClassName", ClassName)
    comm.Parameters.AddWithValue("@WaitListed", WaitListed)
    comm.Parameters.AddWithValue("@Instructor", Instructor)
    comm.Parameters.AddWithValue("@DateCompleted", DateCompleted)
    comm.Parameters.AddWithValue("@Completed", Completed)
    comm.Parameters.AddWithValue("@Walkin", Walkin)

    Dim result As Integer = comm.ExecuteNonQuery()
    conn.Close()
    Return True
End Function

以下是更新错误:

Answer 1

看来您采用了一些适用于 SQL Server 的代码，并试图使其适用于 MySQL。

但是，在 MySQL 中不能用方括号括起标识符名称。如果确实需要，请使用反引号 (`UserName`) 或双引号 ("UserName")。

或者更好的是，如果您的列名称没有空格或特殊字符(就像您的情况一样)，那么只有列名称而没有其他任何内容:

... UserName, SubmitTime, ClassTime, ...