更新phpMyAdmin中的数据 [英] Updating data in phpMyAdmin

问题描述

我有一个数据库和一个datagridview.我想更新phpMyAdmin中的现有文件.这是我的代码:

I have a database and a datagridview. I want to update existing that is in phpMyAdmin. Here's my code:

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语法错误.

Here's the problem, when I run the program I get the sql syntax error at line 1.

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

I've tried seeing whether I placed an extra single quote and double quotes but everything seems fine. what could I have done wrong?

推荐答案

问题是您的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之间的括号

You need to remove the parenthesis between SET and WHERE as below

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

您还需要使用参数化查询来避免 SQL注入，并使用

You also need to use parameterized query to avoid SQL injection and use Using Statement to make sure the SQLConnection is closed and disposed after the query is executed. Since your query is an update query, you don't need SqlDataReader and you should use ExecuteNonQuery instead of 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

这篇关于更新phpMyAdmin中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！