Why is my inputed data unable to be updated in the database?

Question

I am currently doing a asp.net website whereby the registered user is able to update their particulars in a page called Edit Profile.

The way that the Edit Profile page works is that the registered user's data would be shown via textboxes, with the data being displayed through a session function.

The user would then be able to edit their data, through the same textboxes, and the particular user's newly inputted data should be updated in the database.

This is the code in my .cs file...

int profileid;
string username = "";
protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        if(Session["NonAdmin"] !=null)
        {
            getlogininfo();
            getInfo();
        }
    }
    else
    {
        getInfo();
    }
}   

private void getlogininfo()
{
    username = (String)Session["NonAdmin"];
    MySqlConnection mcon = new MySqlConnection("My connectionString");
    MySqlCommand command = mcon.CreateCommand();
    mcon.Open();
    command.CommandText = "SELECT Username FROM pointofcontact WHERE Username='" + tbUsername.Text + "'";
    MySqlDataReader reader = command.ExecuteReader(); 
    while (reader.Read())
    {
        string u = reader["Username"].ToString();
        if(username == u)
        {
            profileid = Convert.ToInt32(reader["POCID"]);
        }
    }
}

private void getInfo()
{
    username = (String)Session["NonAdmin"];
    MySqlConnection mcon = new MySqlConnection("server=182.50.133.91;user id=Jonathan;password=****;persistsecurityinfo=True;database=ajactrac_;allowuservariables=True");
    MySqlCommand command = mcon.CreateCommand();
    mcon.Open();
    command.CommandText = "SELECT * from pointofcontact WHERE Username ='" + username + "'";
    MySqlDataReader reader = command.ExecuteReader();
    reader.Read();
    tbUsername.Text = reader["Username"].ToString();
    tbOldPassword.Text = reader["Password"].ToString();
    tbFirstName.Text = reader["FirstName"].ToString();
    tbLastName.Text = reader["LastName"].ToString();
    tbMobile.Text = reader["ContactNumber"].ToString();
    tbEmail.Text = reader["EmailAddress"].ToString();
    tbAddress.Text = reader["Address"].ToString();
    tbBackupContact.Text = reader["BackupContactNumber"].ToString();





}

protected void btnContinue_Click(object sender, EventArgs e)
{
    //Declaration of variable to update Profile Image
    string imageName, newContact;
    imageName = FileUpload1.FileName.ToString();
    newContact = tbMobile.Text.ToString();

    FileUpload1.PostedFile.SaveAs(Server.MapPath("~/Images/") + imageName);

    username = (String)Session["NonAdmin"];
    MySqlConnection mcon = new MySqlConnection("server=182.50.133.91;user id=Jonathan;password=****;persistsecurityinfo=True;database=ajactrac_;allowuservariables=True");
    MySqlDataAdapter sda = new MySqlDataAdapter("select * from pointofcontact where Username = '" + username.ToString() + "'", mcon);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    if (dt.Rows.Count.ToString() == "1")
    {

        MySqlCommand command = mcon.CreateCommand();
        MySqlCommand command1 = mcon.CreateCommand();
        MySqlCommand command2 = mcon.CreateCommand();
        MySqlCommand command3 = mcon.CreateCommand();
        MySqlCommand command4 = mcon.CreateCommand();
        MySqlCommand command5 = mcon.CreateCommand();
        MySqlCommand command6 = mcon.CreateCommand();
        MySqlCommand command7 = mcon.CreateCommand();

        command.CommandText = "update pointofcontact set Password = ?pwd where Username = '" + username.ToString() + "'";
        command1.CommandText = "update pointofcontact set FirstName = ?firstname where Username = '" + username.ToString() + "'";
        command2.CommandText = "update pointofcontact set LastName = ?lastname where Username = '" + username.ToString() + "'";
        command3.CommandText = "update pointofcontact set ContactNumber = ?contact where Username = '" + username.ToString() + "'";
        command4.CommandText = "update pointofcontact set EmailAddress = ?email where Username = '" + username.ToString() + "'";
        command5.CommandText = "update pointofcontact set Address = ?address where Username = '" + username.ToString() + "'";
        command6.CommandText = "update pointofcontact set BackupContactNumber = ?backupnumber where Username = '" + username.ToString() + "'";
        command7.CommandText = "update pointofcontact set ProfilePic = ?newimage where Username = '" + username.ToString() + "'";

        mcon.Open();
        if(tbNewPassword.Text == null)
        {
            command.Parameters.AddWithValue("?pwd", tbOldPassword.Text.Trim());
        }
        else
        {
            command.Parameters.AddWithValue("?pwd", tbNewPassword.Text.Trim());
        }
        command1.Parameters.AddWithValue("?firstname", tbFirstName.Text.Trim());
        command2.Parameters.AddWithValue("?lastname", tbLastName.Text.Trim());
        command3.Parameters.AddWithValue("?contact", tbMobile.Text.Trim());
        command4.Parameters.AddWithValue("?email", tbEmail.Text.Trim());
        command5.Parameters.AddWithValue("?address", tbAddress.Text.Trim());
        command6.Parameters.AddWithValue("?backupnumber", tbBackupContact.Text.Trim());
        command7.Parameters.AddWithValue("?newimage", imageName);





        command.ExecuteNonQuery();
        command1.ExecuteNonQuery();
        command2.ExecuteNonQuery();
        command3.ExecuteNonQuery();
        command4.ExecuteNonQuery();
        command5.ExecuteNonQuery();
        command6.ExecuteNonQuery();
        command7.ExecuteNonQuery();

        mcon.Close();

But when i check the database, the only thing that was updated was the Password.

I would appreciate any help with my code, as I myself do not know what I can do to fix my code.

Thanks. :)

And also, I forgot to include that the Image (FileUpload1) always needs to be updated, regardless on whether the user wants to update it or not. — MrStutterz, Aug 16 '16 at 01:32
Be careful when posting code,. You should never post passwords and you should be careful with other information about your database (like IP address & user name). — Jeff Siver, Aug 16 '16 at 01:38
Oh my. If you are going to update multiple fields of a table row, you don't have to execute 1 `update` statement for each field. Just execute a single `update` statement like: `update table set field1 = value1, field2 = value2, ..., fieldN = valueN where ...` — sstan, Aug 16 '16 at 01:42
You DO realize that you set everything to `username.ToString()` in your sample right? — Mark Schultheiss, Aug 16 '16 at 01:57
@JeffSiver Had checked the Password Column in my dabse table and it was changed. — MrStutterz, Aug 16 '16 at 02:17

sujith karivelil · Answer 1 · 2016-08-16T02:06:13.377

You need not to use separate update queries for each columns, you can update multiple columns(separated with commas) by using a single update query with specified condition. And an important advise for you is, you should use parameterized queries instead for this plain text queries to avoid SQL Injection:

using(MySqlConnection mcon = new MySqlConnection(constr))
{
    using(MySqlCommand command = mcon.CreateCommand())
    {
      command.CommandText = " update pointofcontact set Password = @pwd," +
                            " FirstName = @firstname," +
                            " LastName = @lastname," +
                            " ContactNumber = @contact," +
                            " EmailAddress = @email," +
                            " Address = @address," +
                            " BackupContactNumber = @backupnumber" +
                            " where Username = @Username";
      mcon.Open();
      if (tbNewPassword.Text == null)
      {
          command.Parameters.AddWithValue("@pwd", tbOldPassword.Text.Trim());
      }
      else
      {
          command.Parameters.AddWithValue("@pwd", tbNewPassword.Text.Trim());
      }
      command.Parameters.AddWithValue("@firstname", tbFirstName.Text.Trim());
      command.Parameters.AddWithValue("@lastname", tbLastName.Text.Trim());
      command.Parameters.AddWithValue("@contact", tbMobile.Text.Trim());
      command.Parameters.AddWithValue("@email", tbEmail.Text.Trim());
      command.Parameters.AddWithValue("@address", tbAddress.Text.Trim());
      command.Parameters.AddWithValue("@backupnumber", tbBackupContact.Text.Trim());
      command.Parameters.AddWithValue("@Username", username.ToString());

      command.ExecuteNonQuery();
    }
}

A using statement might be in order here...http://stackoverflow.com/questions/5637304/can-i-rely-on-the-using-statement-to-close-my-mysql-connections — Mark Schultheiss, Aug 16 '16 at 01:52
You might also consider use of a string literal `@" update pointofcontact set Password = ?pwd, FirstName = ?firstname,..." for the multi-line sql statement — Mark Schultheiss, Aug 16 '16 at 02:01

Why is my inputed data unable to be updated in the database?

1 Answers1