MySQL C# Query trouble - Updating table

Question

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.

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..

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);
                    }
                }
            }

can you please share what error you get or that unexpected behavior of that function. — Varun
– Varun, Commented Sep 11, 2013 at 6:14
"The Update faculty member seems to work perfectly" how could you say that, while your query have @ID parameter but in you didn't add that parameter in command object. — Varun
– Varun, Commented Sep 11, 2013 at 6:19
@Allonaz Are you sure your second query works? Because I don't think so :) — Soner Gönül
– Soner Gönül, Commented Sep 11, 2013 at 14:09

Soner Gönül · Accepted Answer · 2014-04-05 10:25:46Z

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

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 conn = new MySqlConnection(ConnectionString))
using(MySqlCommand cmd = conn.CreateCommand())
{
  //
}

Collectives™ on Stack Overflow

MySQL C# Query trouble - Updating table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related