mysql - 在 phpMyAdmin 中更新数据

Question

我有一个数据库和一个数据 GridView 。我想更新 phpMyAdmin 中的现有内容。这是我的代码:

Private Sub btnupdate_Click(sender As Object, e As EventArgs) Handles btnupdate.Click

    MysqlConn = New MySqlConnection
    MysqlConn.ConnectionString = "server=localhost;userid=server;password=server;database=heavisa_database"
    Dim rabit As MySqlDataReader

    MysqlConn.Open()

    Dim pin As String
    pin = "UPDATE heavisa_database.new_employee SET (Employee_ID = '" & txtemployeeid.Text & "', Nat_ID = '" & txtnatid.Text & "', First_Name = '" & txtfirstname.Text & "', Middle_Name = '" & txtmiddlename.Text & "', Surname = '" & txtsurname.Text & "', NSSF_No = '" & txtnssfno.Text & "', KRA_Pin = '" & txtkrapin.Text & "', NHIF_No = '" & txtnhifno.Text & "', Residence = '" & txtresidence.Text & "', Mobile_No = '" & txtmobileno.Text & "', Email = '" & txtemail.Text & "', Job_Group = '" & cbojobgroup.Text & "', Employment_Date = '" & dtpemploymentdate.Text & "') WHERE Employee_ID like '%{0}%'"

    Try
        con = New MySqlCommand(pin, MysqlConn)
        rabit = con.ExecuteReader

        MessageBox.Show("Update Successful.")
        MysqlConn.Close()

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    Finally
        MysqlConn.Dispose()

    End Try

End Sub

问题来了，当我运行程序时，第 1 行出现 sql 语法错误。

我已经尝试查看是否放置了额外的单引号和双引号，但一切似乎都很好。我可能做错了什么？

Answer 1

问题是你的SQL查询

UPDATE heavisa_database.new_employee 
SET (Employee_ID = ..., Nat_ID = ..., First_Name = ...,
     Middle_Name = ..., Surname = ..., NSSF_No = ...,
     ....
     ....
     .... )
WHERE Employee_ID like '%{0}%'

您需要删除 SET 和 WHERE 之间的括号，如下所示

UPDATE heavisa_database.new_employee 
SET Employee_ID = ..., Nat_ID = ..., First_Name = ...,
     Middle_Name = ..., Surname = ..., NSSF_No = ...,
     ....
     ....
     .... 
WHERE Employee_ID like '%{0}%'

你还需要使用参数化查询来避免SQL injection并使用 Using Statement确保 SQLConnection 在执行查询后关闭并处理。由于您的查询是更新查询，因此您不需要 SqlDataReader，您应该使用 ExecuteNonQuery 而不是 ExecuteReader

Private Sub btnupdate_Click(sender As Object, e As EventArgs) Handles btnupdate.Click

    Dim pin As String
    pin = "UPDATE heavisa_database.new_employee SET Employee_ID = @Employee_ID, Nat_ID = @Nat_ID, First_Name = @First_Name, Middle_Name = @Middle_Name, Surname = @Surname, NSSF_No = @NSSF_No, KRA_Pin = @KRA_Pin, NHIF_No = @NHIF_No, Residence = @Residence, Mobile_No = @Mobile_No, Email = @Email, Job_Group = @Job_Group, Employment_Date = @Employment_Date WHERE Employee_ID like '%{0}%'"

    Try
        Using MysqlConn As New MySqlConnection
            MysqlConn.ConnectionString = "server=localhost;userid=server;password=server;database=heavisa_database"

            Using con As New MySqlCommand(pin, MysqlConn)

                With con
                    con.Parameters.AddWithValue("@Employee_ID", txtemployeeid.Text)
                    con.Parameters.AddWithValue("@Nat_ID", txtnatid.Text)
                    con.Parameters.AddWithValue("@First_Name", txtfirstname.Text)
                    con.Parameters.AddWithValue("@Middle_Name", txtmiddlename.Text)
                    con.Parameters.AddWithValue("@Surname", txtsurname.Text)
                    con.Parameters.AddWithValue("@NSSF_No", txtnssfno.Text)
                    con.Parameters.AddWithValue("@KRA_Pin", txtkrapin.Text)
                    con.Parameters.AddWithValue("@NHIF_No", txtnhifno.Text)
                    con.Parameters.AddWithValue("@Residence", txtresidence.Text)
                    con.Parameters.AddWithValue("@Mobile_No", txtmobileno.Text)
                    con.Parameters.AddWithValue("@Email", txtemail.Text)
                    con.Parameters.AddWithValue("@Job_Group", cbojobgroup.Text)
                    con.Parameters.AddWithValue("@Employment_Date", dtpemploymentdate.Text)
                End With

                MysqlConn.Open()
                con.ExecuteNonQuery()
            End Using

        End Using

        MessageBox.Show("Update Successful.")

    Catch ex As MySqlException
        MessageBox.Show(ex.Message)
    End Try

End Sub