mysql - 更新 Datagrid 中除 ID 之外的列中的所有数据(MySQL 数据库 VB.NET)

Question

这是我的程序的功能。这是一个患者管理系统，系统能够添加、删除和更新，但我编码了一个自动生成随 secret 钥，它将创建随 secret 钥，我在 ID 上未检查主键专栏 Mysql database ，数据类型为INT ，所以这个程序会生成一个随机的IDs添加记录时自动生成 ID，而不是 MySql 数据库自动生成 ID(按顺序排列的数字)。问题是当我单击更新按钮时，它告诉我的语法不正确，有时会显示“主键的重复输入键'xxxxx'”，这意味着它重复或替换或保存再次ID。我的问题是

如何在不更改或影响 ID 键的情况下更新列中的所有记录？

Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click

    MySqlConn = New MySqlConnection
    MySqlConn = New MySqlConnection("server=localhost;user id=root;password=root;database=PatientManagementSystem;")
    MySqlConn.Open()

    Dim cmd As MySqlCommand = MySqlConn.CreateCommand

    cmd.CommandText = String.Format("UPDATE newpatient SET " &
                                    "ID='{0}', " &
                                    "Lastname='{1}', " &
                                    "Firstname= '{2}', " &
                                    "Middlename= '{3}', " &
                                    "Age= '{4}', " &
                                    "Gender= '{5}', " &
                                    "Address= '{6}', " &
                                    "Occupation= '{7}', " &
                                    "Month= '{8}', " &
                                    "Day= '{9}', " &
                                    "Year= '{10}'",
                                    txtID.Text,
                                    txtFirstname.Text,
                                    txtFirstname.Text,
                                    txtMiddlename.Text,
                                    txtAge.Text,
                                    cmbGender.SelectedItem,
                                    txtAddress.Text,
                                    txtOccupation.Text,
                                    cmbMonth.SelectedItem,
                                    cmbDay.SelectedItem,
                                    cmbYear.SelectedItem)
    Dim affectedRows As Integer = cmd.ExecuteNonQuery

    If affectedRows > 0 Then

        MsgBox("Record successfully updated!", MsgBoxStyle.Information, "Success")
    Else
        MsgBox("Updating record failed.", MsgBoxStyle.Critical, "Failed")

    End If
    MySqlConn.Close()
 End Sub

 Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

    'AUTO GENERATE RANDOM IDs
    Dim random As New Random
    Dim id As Integer
    id = (random.Next(100000000, 1000000000))
    txtID.Text = id

    MySqlConn = New MySqlConnection
    MySqlConn = New MySqlConnection("server=localhost;user id=root;password=root;database=PatientManagementSystem;")
    MySqlConn.Open()

    Dim cmd As MySqlCommand = MySqlConn.CreateCommand
    cmd.CommandText = String.Format("INSERT INTO newpatient (ID, Lastname, Firstname, Middlename, Age, Gender, Address, Occupation, Month, Day, Year )" &
                                    "VALUES ('{0}' , '{1}' ,'{2}' ,'{3}' ,'{4}' ,'{5}' , '{6}', '{7}', '{8}', '{9}', '{10}' )",
                                    txtID.Text,
                                    txtLastname.Text,
                                    txtFirstname.Text,
                                    txtMiddlename.Text,
                                    txtAge.Text,
                                    cmbGender.SelectedItem,
                                    txtAddress.Text,
                                    txtOccupation.Text,
                                    cmbMonth.SelectedItem,
                                    cmbDay.SelectedItem,
                                    cmbYear.SelectedItem)
    Dim affectedrows As Integer = cmd.ExecuteNonQuery()
    If affectedrows > 0 Then
        MsgBox("Record successfully saved!", MsgBoxStyle.Information, "Success")
        Dim adapter As New MySqlDataAdapter
        Dim DataSet As New DataTable
        Dim binding As New BindingSource

        'REFRESH DATAGRID
        Try
            frmOld_Patient.Show()
            Dim query As String
            Dim cmd1 As MySqlCommand
            query = "SELECT * FROM PatientManagementSystem.newpatient"
            cmd1 = New MySqlCommand(query, MySqlConn)
            adapter.SelectCommand = cmd1
            adapter.Fill(DataSet)
            binding.DataSource = DataSet
            frmOld_Patient.dgvRecords.DataSource = binding
            adapter.Update(DataSet)
            MySqlConn.Close()
            frmOld_Patient.Select()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            MySqlConn.Dispose()
        End Try
    Else
        MsgBox("Saving record failed.", MsgBoxStyle.Critical, "Failed")
    End If
    MySqlConn.Close()

End Sub

Answer 1

您的 Update 语句 没有 Where 条件，因此它将使用相同的 ID 更新表中的所有行，这也是您得到该结果的原因错误

编辑:只需将 WHERE 添加到更新语句中，如下所示

cmd.CommandText = String.Format("UPDATE newpatient SET " &
                                "ID='{0}', " &
                                "Lastname='{1}', " &
                                "Firstname= '{2}', " &
                                "Middlename= '{3}', " &
                                "Age= '{4}', " &
                                "Gender= '{5}', " &
                                "Address= '{6}', " &
                                "Occupation= '{7}', " &
                                "Month= '{8}', " &
                                "Day= '{9}', " &
                                "Year= '{10}'" &
                                "WHERE ID={11}, ' here we add where
                                txtID.Text,
                                txtFirstname.Text,
                                txtFirstname.Text,
                                txtMiddlename.Text,
                                txtAge.Text,
                                cmbGender.SelectedItem,
                                txtAddress.Text,
                                txtOccupation.Text,
                                cmbMonth.SelectedItem,
                                cmbDay.SelectedItem,
                                cmbYear.SelectedItem,
                                yourID)