C# textbox to update sql database

Question

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

score 2 · Accepted Answer · edited May 23 '17 at 11:43

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

.....

You could also include the MySqlCommandBuilder in a using block as it implements IDisposable — David B, Mar 12 '15 at 14:48
Brilliant, that's perfect for what I wanted. I did know that a new row was being added but just couldn't find the syntax to update an existing row. As for the serious problem about the string concatenation - haven't been taught differently so have to go with what's been provided, but I'll take it on board.. — user3387822, Mar 12 '15 at 17:27
Not good. Teaching to prepare queries in this way is very bad. Do yourself a favor and take a bit of time to learn the correct way. IE. Parameterized Query — Steve, Mar 12 '15 at 17:34

C# textbox to update sql database

1 Answers1