CODEWITHSUNDEEP
c#mysql
user3387822
user3387822

Reputation: 15

C# textbox to update sql database

Relatively new user to using C# and I want to update existing data within an sql database. I do understand that the line DataRow dr = dTable.NewRow(); actually adds a new row but I want the command that updates the selected row with the data from the text box. Thanks in advance.

MySqlDataAdapter dAdapter = new MySqlDataAdapter("SELECT * FROM myDatabase WHERE username ='" + txtUserName.Text + "'", myConn);
DataTable dTable = new DataTable();
dAdapter.Fill(dTable);

if (dTable.Rows.Count == 0)
{
     DialogResult drError;
     drError = MessageBox.Show("That user name does not exist, please create a user.", "User name does not exist", MessageBoxButtons.OK, MessageBoxIcon.Error);

     if (drError == DialogResult.Yes)
     {
           this.Close();
     }
 }
 else if (dTable.Rows.Count == 1)
 {
      DataRow dr = dTable.NewRow();
      dr["day1consumed"] = Convert.ToInt32(txtCCDay1.Text);
      dr["day1burned"] = Convert.ToInt32(txtCBDay1.Text);
      dr["day2consumed"] = Convert.ToInt32(txtCCDay2.Text);
      dr["day2burned"] = Convert.ToInt32(txtCBDay2.Text);
      dr["day3consumed"] = Convert.ToInt32(txtCCDay3.Text);
      dr["day3burned"] = Convert.ToInt32(txtCBDay3.Text);
      dr["day4consumed"] = Convert.ToInt32(txtCCDay4.Text);
      dr["day4burned"] = Convert.ToInt32(txtCBDay4.Text);
      dr["day5consumed"] = Convert.ToInt32(txtCCDay5.Text);
      dr["day5burned"] = Convert.ToInt32(txtCBDay5.Text);
      dr["day6consumed"] = Convert.ToInt32(txtCCDay6.Text);
      dr["day6burned"] = Convert.ToInt32(txtCBDay6.Text);
      dr["day7consumed"] = Convert.ToInt32(txtCCDay7.Text);
      dr["day7burned"] = Convert.ToInt32(txtCBDay7.Text);
      dTable.Rows.Add(dr);
      MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dAdapter);

      int iRowsAffected = dAdapter.Update(dTable);
      dAdapter.Dispose();

      DialogResult drReply;
      drReply = MessageBox.Show("Your data has been saved", "Record updated", MessageBoxButtons.OK, MessageBoxIcon.Information);

      if (drReply == DialogResult.Yes)
      {
             this.Close();
      }
      else
      {
             MessageBox.Show("Error saving data", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
       }
  }
}

Upvotes: 1

Views: 740

Answers (1)

Steve
Steve

Reputation: 216243

Your actual code adds a new row, instead you need to take the actual row retrieved by your SELECT command and change it to your new values.

So you just change this line

DataRow dr = dTable.NewRow();

in 

DataRow dr = dtTable.Rows[0];

and start to update the values.
At the end of the update you don't need to add the row to the actual row collection of the table (it is already there and you have modified it).

Thus this line should be removed 

// dTable.Rows.Add(dr);
MySqlCommandBuilder commandBuilder = new MySqlCommandBuilder(dAdapter);
int iRowsAffected = dAdapter.Update(dTable);
....

Said that, I wish to point you to a serious problem in the SELECT command text. You use a string concatenation to form the command and this is a well known security problem called Sql Injection. You need to use a parameterized query when you want to take a user typed value and query a database 

string cmdText = "SELECT * FROM myDatabase WHERE username =@name"
MySqlDataAdapter dAdapter = new MySqlDataAdapter(cmdText,myConn);
dAdapter.SelectCommand.Parameters.Add("@name", MySqlDbType.VarChar).Value = txtUserName.Text

.....

Upvotes: 2

Related Questions