SQL UPDATE is changing all records

Question

I created a program for our Thesis which uses MySQL server for record keeping. all functions (delete, save, add) are working except the UPDATE. When clicking the UPDATE button, the UPDATED RECORD replace all recently added records and duplicates all of the records on the datagrid.

newpatient is mysql table

PatientManagementSystem is name of the Database

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 " &
                                    "Lastname='{0}', " &
                                    "Firstname= '{1}', " &
                                    "Middlename= '{2}', " &
                                    "Age= '{3}', " &
                                    "Gender= '{4}', " &
                                    "Address= '{5}', " &
                                    "Occupation= '{6}', " &
                                    "Month= '{7}', " &
                                    "Day= '{8}', " &
                                    "Year= '{9}'",
                                    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()

Your update SQL is missing a WHERE clause which would limit the row(s) to be affected. Example: `...WHERE PatientId = 7` to only udate that one patient record. Also SQL should never be concatenated like that, use Parameters instead. If the patient's last name is "O'Brian" it will bomb (many other more serious actions can happen too). You should also dispose of DBObjects when done with them. See: [this](http://stackoverflow.com/a/29187199/1070452) — Ňɏssa Pøngjǣrdenlarp, Sep 13 '15 at 16:43
@Plutonix, this is an answer. What's more, it's a good answer. Why not submit it as an answer rather than a comment? — O. Jones, Sep 13 '15 at 16:51
Study the link I posted in the first comment. It covers everything mentioned a) SQL WHERE clause b) parameters c) disposing of DBOjects, d) abusing ticks e) other vital links — Ňɏssa Pøngjǣrdenlarp, Sep 14 '15 at 00:22
I do not see any field in there that could be primary key, like for example ParientID. The update should end with a where clause. Example : update newPatient set name = 'john doe' WHERE PatientID = 123 — GuidoG, Sep 14 '15 at 10:46
@wingedpanther yes i forgot to include an ID for every records, that ID need for WHERE. am i right? I'll fix it now — exeCUTE, Sep 14 '15 at 12:28
@GuidoG yup that's one of the possible cause of the problem. i'll add an ID field now. :) — exeCUTE, Sep 14 '15 at 12:29

SQL UPDATE is changing all records

0 Answers0