Update query makes the fields empty in the database

Question

I asked a question previously concerning updating data in a datagridview with phpMyAdmin. You can refer to it by following link -->Updating data in phpmyadmin part 1

The code works quite OK, but now the problem is that when I check for the updated data in localhost all I see are empty fields. Below is the screenshot of my vb in design. I have labelled the textboxes as per my database. The textboxes in the screenshot are set to be invisible on running my winform.

What exactly could be the problem?

---

@Kakarot Here is what I initially had

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 = '" & txtemployeeid1.Text & "'"

    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

And here is what I currently have (credit goes to one Mr. ekad for it)

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"

            MysqlConn.Open()

            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

                con.ExecuteNonQuery()
            End Using

        End Using

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

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

The first code gave me an error (refer to the link above). The second code works but it's emptying my fields instead of updating.

Answer 1

First things first, just a simple question, is heavisa_database.new_employee a table? It's really wise to properly name them, you can put tbl before your desired name if its a table, and db before the name if its a database. Just to prevents confusions. And I think you dont need a reader when updating records in your table, correct me if I'm wrong :p

Okay, to be honest, I don't really understand much the code that Mr. ekad provided. Here's a code you can try. And oh, you don't really need the bracket after the SET.

//Global Vars
Dim connectionString As String = "server=localhost;userid=server;password=server;database=heavisa_database"
Dim conn As New MySqlConnection

First, let's make some functions for ease (you can put this anywhere in your code):

//remember that conn is our MySqlConnection
Private Sub connect()
    conn.ConnectionString = connectionString
    conn.Open()
End Sub

Private Sub disconnect()
    conn.Close()
End Sub

So, event for your update button:

connect()

Dim pin As String
pin = "UPDATE 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 = '" & txtemployeeid1.Text & "'"

    //lets create new command
    Dim command As New MySqlCommand

    //sets the connection for our command
    command.Connection = conn
    command.CommandText = pin

    command.ExecuteNonQuery()

    disconnect()

    MessageBox.Show("Record saved!", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

Try the given code above and I'm pretty sure It'll work. Goodluck!

Answer 2

How did you updated your tables in your database? It should be like: (Im gonna take my code for an old project that I did)

Private Sub disconnect()
    conn.Close()
End Sub

Private Sub connect()
    conn.ConnectionString = connectionString
    conn.Open()
End Sub

Private Sub btnSaveEdit_Click(sender As Object, e As EventArgs) Handles btnSaveEdit.Click

connect()
Dim query as string
Dim command As New MySqlCommand

query = "Update `tblcandidates` set firstname = '" & txtEditFname.Text & "', lastname = '" & txtEditLName.Text & "', position='" & cboEditPosition.Text & "', fullname = '" & fullname & "' where recNum = '" & txtRec.Text & "'"



command.Connection = conn
command.CommandText = query

command.ExecuteNonQuery()

disconnect()
End Sub