MySQL C＃查询麻烦 - 更新表

I'am having trouble with this function I'm creating to Update my database. The Update faculty member seems to work perfectly while the Updating of the person tables does not . I'm presuming that the MySQL Query isn't correct for updating the person table.

我正在创建这个函数,我正在创建更新我的数据库。更新教师成员似乎完美地工作,而更新人员表则没有。我假设MySQL查询不正确更新人员表。

Additional INFO: My code is hooked to an GUI mock as of right now for testing purposes . the Update string with @Id.. its just to select which ID I wish to change..

附加信息:我的代码现在已连接到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);
                    }
                }
            }

1 个解决方案

#1

You forget to add @Id parameter in your second sql query.

您忘记在第二个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();

Also use using statement to dispose your MySqlConnection and MySqlCommand like;

还可以使用using语句来配置你的MySqlConnection和MySqlCommand之类的;

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

#1