MySqlConnection ExecuteNonQuery更新错误

Question

从OleDB更改为MySqlConnect后，我遇到了ExecuteNonQuery在UPDATE sql中抛出错误的问题-现有代码在使用提交的OleDB行更新access DB时工作正常：

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, ...