I am experimenting with DataSets and DataAdapters in Visual Studio. So, I have a toy table (teacher) in MySQL with two columns: id (PK) and AM (int(11)). In a WinForm, a textbox is bound to AM:
string queryString = "SELECT id, AM from teacher where id=1;";
adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(queryString, conn);
teacher = new DataSet();
adapter.Fill(teacher, "teacher");
textBox6.DataBindings.Add("Text", teacher.Tables[0], "AM");
The binding is one way successful, i.e the textbox displays the value of the column.
I also have a button which, when pressed, will update the Database table with the value the user enters in the textbox:
var updateTeacherCmd = new MySqlCommand("UPDATE teacher SET AM=@AM WHERE id=@id;", conn);
MySqlParameter param1 = new MySqlParameter("@AM", MySqlDbType.Int32, 4, "AM");
updateTeacherCmd.Parameters.Add(param1);
MySqlParameter param2 = new MySqlParameter("@id", MySqlDbType.Int32, 4,"id");
param2.SourceVersion = DataRowVersion.Original;
updateTeacherCmd.Parameters.Add(param2);
adapter.UpdateCommand = updateTeacherCmd;
adapter.Update(teacher,"teacher");
MessageBox.Show(adapter.UpdateCommand.CommandText);
But the teacher table is not updated.
I have succesfully updated the datasource with the use of:
var updateTeacherCmd = new MySqlCommand("UPDATE teacher SET AM=textBox6.Text WHERE id=1;", conn);
updateTeacherCmd.ExecuteNonQuery();
but I would like to know why the datadapter method fails.
3
One small problem: the parameters don’t match
You need to ensure that the parameters in MySqlCommand and MySqlParameter are the same.
In your example, the parameter names set in MySqlCommand are @AM and @id respectively, but the parameter names set in MySqlParameter are @PROYP_MERES and @id respectively.
In a way, you need to change the code:
var updateTeacherCmd = new MySqlCommand("UPDATE teacher SET AM=@AM WHERE id=@id;" , conn);
MySqlParameter param1 = new MySqlParameter("@AM", MySqlDbType.Int32, 11, "AM");
updateTeacherCmd.Parameters.Add(param1);
MySqlParameter param2 = new MySqlParameter("@id", MySqlDbType.Int32, 11, "id");
In addition, you can modify the dataset value to update the database table like this:
MySqlDataAdapter adapter;
DataSet teacher;
MySqlConnection conn =new MySqlConnection("");
private void button1_Click(object sender, EventArgs e)
{
string queryString = "SELECT id, AM from teacher where id=1;";
adapter = new MySqlDataAdapter();
adapter.SelectCommand = new MySqlCommand(queryString, conn);
teacher = new DataSet();
adapter.Fill(teacher, "teacher");
textBox1.DataBindings.Add("Text", teacher.Tables[0], "AM");
}
private void button2_Click(object sender, EventArgs e)
{
var updateTeacherCmd = new MySqlCommand("UPDATE teacher SET AM=@AM WHERE id=@id;", conn);
MySqlParameter param1 = new MySqlParameter("@AM", MySqlDbType.Int32, 11, "AM");
updateTeacherCmd.Parameters.Add(param1);
MySqlParameter param2 = new MySqlParameter("@id", MySqlDbType.Int32, 11, "id");
param2.SourceVersion = DataRowVersion.Original;
updateTeacherCmd.Parameters.Add(param2);
adapter.UpdateCommand = updateTeacherCmd;
if (teacher.Tables["teacher"].Rows.Count > 0)
{
teacher.Tables["teacher"].Rows[0]["AM"] = textBox2.Text;
}
adapter.Update(teacher, "teacher");
MessageBox.Show(adapter.UpdateCommand.CommandText);
}
3
Ok, I have solved the issue.
The trick was to call EndEdit() on the datarow (or dataset) who had changed and then proceed with the update() on the datadapter:
DataRow myrow;
myrow=teacher.Tables[0].Rows[0];
myrow.EndEdit();
var updateTeacherCmd = new MySqlCommand("UPDATE teacher SET AM=@AM WHERE id=1;", conn);
MySqlParameter param1 = new MySqlParameter("@AM", MySqlDbType.Int32, 4, "AM");
updateTeacherCmd.Parameters.Add(param1);
MySqlParameter param2 = new MySqlParameter("@id", MySqlDbType.Int32, 4,"id");
param2.SourceVersion = DataRowVersion.Original;
updateTeacherCmd.Parameters.Add(param2);
adapter.UpdateCommand = updateTeacherCmd;
adapter.Update(teacher,"teacher");