Problem sending datatable updates to the database

[EDN Admin]

First at all, this is my form with my datagridview already populated by a datatable: http://i53.tinypic.com/28as4kh.png
As you guys can see, i have three search methods to populate the datatable, by name, medic, and date. I have no trouble at all when modifying some of the cells and pressing the "atualizar" button down there to send the changes back to db, it updates everything
nice, except by one field, the "DataRemarcada" one, and my guess is the way im retrieving the data from the DB, these are the codes for the two "ok" buttons up there.

<div style="color:Black;background-color:White; <pre>
<span style="color:Green; // Botao Ok da textbox "Medico"
<span style="color:Blue; private <span style="color:Blue; void button2_Click(<span style="color:Blue; object sender, EventArgs e)
{
dataGridAtualizar.DataSource = <span style="color:Blue; null;

data = <span style="color:Blue; new DataTable();
ds = <span style="color:Blue; new DataSet();

<span style="color:Blue; if (conn != <span style="color:Blue; null)
conn.Close();

<span style="color:Blue; string connStr = <span style="color:Blue; string.Format(<span style="color:#A31515; "server=127.0.0.1;user id=root; password=XXXXX; database=agendamentos; pooling=false");

<span style="color:Blue; try
{
conn = <span style="color:Blue; new MySqlConnection(connStr);
conn.Open();
}
<span style="color:Blue; catch (MySqlException ex)
{
MessageBox.Show(<span style="color:#A31515; "Erro ao conectar á base de dados, contate o administrador do sistema. Erro: " + ex.Message);
}

da = <span style="color:Blue; new MySqlDataAdapter(<span style="color:#A31515; "SELECT ID,Nome,Especialidade,Medico,Data,Hora,Retorno,Cancelada,DATE_FORMAT(DataRemarcada, %d/%m/%Y) AS DataRemarcada,HoraRemarcada FROM consultas WHERE Medico = " + comboBox1.Text + <span style="color:#A31515; ";", conn);
cb = <span style="color:Blue; new MySqlCommandBuilder(da);

da.Fill(ds);

<span style="color:Blue; int count = ds.Tables[0].Rows.Count;

<span style="color:Blue; if (count == 0)
{
MessageBox.Show(<span style="color:#A31515; "Não há nenhuma consulta cadastrada com esse médico");
dataGridAtualizar.DataSource = <span style="color:Blue; null;
}
<span style="color:Blue; else
{
da.Fill(data);
dataGridAtualizar.DataSource = data;
}
conn.Close();
}
[/code]
<br/>

<div style="color:Black;background-color:White; <pre>
<span style="color:Green; // Botao Ok da textbox "Nome"
<span style="color:Blue; private <span style="color:Blue; void button1_Click(<span style="color:Blue; object sender, EventArgs e)
{
dataGridAtualizar.DataSource = <span style="color:Blue; null;

data = <span style="color:Blue; new DataTable();
ds = <span style="color:Blue; new DataSet();

<span style="color:Blue; if (conn != <span style="color:Blue; null)
conn.Close();

<span style="color:Blue; string connStr = <span style="color:Blue; string.Format(<span style="color:#A31515; "server=127.0.0.1;user id=root; password=XXXXXX; database=agendamentos; pooling=false");

<span style="color:Blue; try
{
conn = <span style="color:Blue; new MySqlConnection(connStr);
conn.Open();
}
<span style="color:Blue; catch (MySqlException ex)
{
MessageBox.Show(<span style="color:#A31515; "Erro ao conectar á base de dados, contate o administrador do sistema. Erro: " + ex.Message);
}

da = <span style="color:Blue; new MySqlDataAdapter(<span style="color:#A31515; "SELECT ID,Nome,Especialidade,Medico,Data,Hora,Retorno,Cancelada,DATE_FORMAT(DataRemarcada, %d/%m/%Y) AS DataRemarcada,HoraRemarcada FROM consultas WHERE Nome LIKE %" + textBox1.Text.ToString() + <span style="color:#A31515; "%;", conn);
cb = <span style="color:Blue; new MySqlCommandBuilder(da);

da.Fill(ds);

<span style="color:Blue; int count = ds.Tables[0].Rows.Count;

<span style="color:Blue; if (count == 0)
{
MessageBox.Show(<span style="color:#A31515; "Não há nenhuma consulta cadastrada para o nome digitado");
dataGridAtualizar.DataSource = <span style="color:Blue; null;
}
<span style="color:Blue; else
{
da.Fill(data);
bSource = <span style="color:Blue; new BindingSource();
bSource.DataSource = data;
dataGridAtualizar.DataSource = bSource;
}

conn.Close();
}
[/code]

<br/>
I think the problem is the way im parsing the date from the DB format to my country/system format on the dataadapter query, if you are by now guessing "but wy you dont have problems send the changes made on the "Data" field back to DB?" the answer is that
ive already parsed the date when im sending it to the DB via a INSERT command in another forms dataadapter


<div style="color:Black;background-color:White; <pre>
<span style="color:Blue; string datacalendario = dateTimePicker1.Value.ToString(<span style="color:#A31515; "yyyyMMdd");

cmd = <span style="color:Blue; new MySqlCommand(<span style="color:#A31515; "INSERT INTO consultas (Nome,Especialidade,Medico,Data,Hora,Retorno) VALUES " + <span style="color:#A31515; "(" + textBox1.Text + <span style="color:#A31515; "," + comboBox1.Text + <span style="color:#A31515; "," + comboBox2.Text + <span style="color:#A31515; "," + datacalendario + <span style="color:#A31515; "," + textBox3.Text + <span style="color:#A31515; "," + comboBox3.Text + <span style="color:#A31515; ");", conn);
[/code]
<br/>
Regarding the code, i know it would be a proper way if i create a class to use it as my connection string and then simple instance it on the form, and i also know it would be better making SQL querys using parameters, but since this is my first C# app i
just want to make it work

Thanks in advance for the help.





View the full article