Reputation: 21
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.
Upvotes: 0
Views: 201
Reputation: 9
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!
Upvotes: 0
Reputation: 9
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
Upvotes: 0