更新查询使数据库中的字段为空 [英] Update query makes the fields empty in the database

问题描述

我以前曾问过有关使用myphpadmin在datagridview中更新数据的问题。您可以通过以下链接参考 - > 更新phpmyadmin第1部分中的数据

代码工作正常，但现在的问题是当我检查localhost中的更新数据时，我看到的都是空字段。下面是我的vb设计的截图。我已经按照我的数据库标记了文本框。屏幕截图中的文本框在运行我的winform时被设置为不可见。

究竟可以是问题？

---

@Kakarot
这是我最初有的

MysqlConn = New MySqlConnection
MysqlConn.ConnectionString =server = localhost; userid = server; password = server; database = heavyisa_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.Te xt&'，Mobile_No ='& txtmobileno.Text& '，Email ='& txtemail.Text& '，Job_Group ='& cbojobgroup.Text& '，Employment_Date ='& dtpemploymentdate.Text& '）WHERE Employee_ID ='& txtemployeeid1.Text& '
 
尝试
 con =新的MySqlCommand（pin，MysqlConn）
 rabit = con.ExecuteReader 
 
 MessageBox.Show（Update Successful ）
 MysqlConn.Close（）
 
 Catch ex As MySqlException 
 MessageBox.Show（ex.Message）
最后
 MysqlConn.Dispose（）
 
结束尝试
  

这里是我目前拥有的（信用到一个ekad先生为此）

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}％'

 尝试
使用MysqlConn作为新的MySqlConnection 
MysqlConn.ConnectionString =server = localhost; userid = server; password = server; database = heavisa_database
 
 MysqlConn.Open（）
 
使用con作为新的MySqlCommand 
 
 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（@姓氏，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，txtresiden ce.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）
结束
 
 con.ExecuteNonQuery（）
结束使用
 
结束使用
 
 MessageBox.Show（更新成功）
 MysqlConn.Close（）
 
 Catch ex As MySqlException 
 MessageBox.Show（ex.Message）
结束尝试
  

第一个代码给我一个错误（参考上面的链接）。第二个代码工作，但它清空我的字段而不是更新。

解决方案

首先，只是一个简单的问题，是code> heavisa_database.new_employee 一个表？正确地命名它们是非常明智的，如果它的一个表和 db 之前可以将 tbl 名称如果是其数据库。只是为了防止混乱。而且，当您更新表中的记录时，我认为您不需要阅读器，如果错误则更正我：p

<好的老实说，我并不太了解ekad先生提供的代码。这是一个可以尝试的代码。而且，在 SET 之后，您并不需要支架。

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

首先，让我们放松一些功能（你可以把它放在你的代码中的任何地方）：

  //记住conn是我们的MySqlConnection 
 Private Sub connect（）
 conn.ConnectionString = connectionString 
 conn.Open（）
 End Sub 
 
 Private Sub disconnect（）
 conn.Close（）
 End Sub 
  

所以，你的更新按钮的事件：

  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& '
 
 //允许创建新命令
 Dim命令作为新的MySqlCommand 
 
 //设置我们的命令的连接
 command.Connection = conn 
 command.CommandText = pin 
 
 command.ExecuteNonQuery（）
 
 disconnect（）
 
 MessageBox.Show（Record saved！ ，成功，MessageBoxButtons.OK，MessageBoxIcon.Information）
  

尝试上面给出的代码，很确定它会奏效Goodluck！

I asked a question previously concerning updating data in a datagridview with myphpadmin. 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.

解决方案

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!

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