c# - MySQL C# 查询问题 - 更新表

Question

我在创建用于更新我的数据库的函数时遇到了问题。 Update faculty member 似乎工作得很好，而 person 表的 Updating 却不行。我假设 MySQL 查询对于更新人员表不正确。

附加信息:出于测试目的，我的代码现在已连接到 GUI 模拟。带有@Id的更新字符串..它只是为了选择我想改变的ID..

public static void Update(string update,string fName, string lName, string DOB, string postCode, string address, string phoneNumber,
                                        bool isTenured, string qualifications, string previousEmployment)
            {
                MySqlConnection conn;
                MySqlCommand cmd;
                string sql = "UPDATE person SET firstName = @FirstName , lastName = @LastName, DOB = @DOB, phoneNumber = @PhoneNumber, address = @Address, postCode = @PostCode WHERE ID =@Id;";
                GetConnection(out conn, out cmd, sql);

                try
                {
                    cmd.Parameters.AddWithValue("@Id", update);
                    cmd.Parameters.AddWithValue("@FirstName", fName);
                    cmd.Parameters.AddWithValue("@LastName", lName);
                    cmd.Parameters.AddWithValue("@DOB", DOB);
                    cmd.Parameters.AddWithValue("@PhoneNumber", phoneNumber);
                    cmd.Parameters.AddWithValue("@Address", address);
                    cmd.Parameters.AddWithValue("@PostCode", postCode);

                    long id = (long)cmd.LastInsertedId;

                    sql = "UPDATE facultymember SET isTenured = @IsTenured, qualifications = @Qualifications, previousEmployment = @PreviousEmployment WHERE Person_personID=@Id";
                    cmd = new MySqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@IsTenured", isTenured);
                    cmd.Parameters.AddWithValue("@Qualifications", qualifications);
                    cmd.Parameters.AddWithValue("@PreviousEmployment", previousEmployment);
                    cmd.ExecuteNonQuery();

                }

                catch (NullReferenceException nre)
                {
                    MessageBox.Show(nre.Message);
                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
                finally
                {
                    try
                    {
                        MessageBox.Show("Updated");
                        cmd.Connection.Close();
                        conn.Close();
                    }
                    catch (Exception e)
                    {
                        MessageBox.Show(e.Message);
                    }
                }
            }

Answer 1

您忘记在第二个 sql 查询中添加 @Id 参数。

sql = "UPDATE facultymember
       SET isTenured = @IsTenured, qualifications = @Qualifications, previousEmployment = @PreviousEmployment
       WHERE Person_personID=@Id";
                        //   ^^^^
cmd = new MySqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@IsTenured", isTenured);
cmd.Parameters.AddWithValue("@Qualifications", qualifications);
cmd.Parameters.AddWithValue("@PreviousEmployment", previousEmployment);
cmd.Parameters.AddWithValue("@Id", YourIdValue);
cmd.ExecuteNonQuery();

也可以使用 using statement处理你的 MySqlConnection 和 MySqlCommand ；

using(MySqlConnection conn = new MySqlConnection(ConnectionString))
using(MySqlCommand cmd = conn.CreateCommand())
{
  //
}