Insert date and currency data of a maskedTextBox into Mysql

Question

When trying to enter the typed data of an object maskedTextBox, one of the date type and another of the currency type, in the Mysql database, where I have a table called test, created as command below:

    CREATE TABLE `teste`
(
`tes_id` Int NOT NULL AUTO_INCREMENT
`tes_data` Date,
`tes_preco` Decimal,
PRIMARY KEY (`tes_id`)
)

On my form, like WindowsFormApplication, created in Visual Studio Community Edition 2015, I have a save button that has the code below:

 if (incluir)
 {
     string sqlInsert = "INSERT INTO teste (tes_data, tes_preco) " + "VALUES (STR_TO_DATE(@TesData,'%d/%m/%Y'), @TesPreco)";
     MySqlConnection con = new MySqlConnection(conexaoMySQL);
     MySqlCommand cmd = new MySqlCommand(sqlInsert, con);
     cmd.Parameters.AddWithValue("@TesData", txtTes_Data.Text);
     cmd.Parameters.AddWithValue("@TesPreco", txtTes_Preco.Text);
     cmd.CommandType = CommandType.Text;
     con.Open();
     try
     {
         int i = cmd.ExecuteNonQuery();
         if (i > 0) MessageBox.Show("Dado Incluido com Sucesso !!!!");
     }
     catch (Exception ex)
     {
         MessageBox.Show("Erro: " + ex.ToString());
     }
     finally
     {
         con.Close();
     }
 }
 else
 {
     string sqlAlterar = "UPDATE teste SET str_to_date(@TesData,'%d/%m/%Y'), tes_preco=@TesPreco WHERE tes_id=@TesId";
     MySqlConnection con = new MySqlConnection(conexaoMySQL);
     MySqlCommand cmd = new MySqlCommand(sqlAlterar, con);
     cmd.Parameters.AddWithValue("@TesId", txtTes_Id.Text);
     cmd.Parameters.AddWithValue("@TesData", txtTes_Data.Text);
     cmd.Parameters.AddWithValue("@TesPreco", txtTes_Preco.Text);
     cmd.CommandType = CommandType.Text;
     con.Open();
     try
     {
         int i = cmd.ExecuteNonQuery();
         if (i > 0)
             MessageBox.Show("Dado Atualizado com Sucesso !!!!");
     }
     catch (Exception ex)
     {
         MessageBox.Show("Erro: " + ex.ToString());
     }
     finally
     {
         con.Close();
     }
 }
 tsbIncluirTes.Enabled = true;
 tsbSalvarTes.Enabled = false;
 tsbCancelarTes.Enabled = false;
 tsbExcluirTes.Enabled = false;
 tstBuscarTesId.Enabled = true;
 tsbBuscarTeste.Enabled = true;
 txtTes_Data.Enabled = false;
 txtTes_Preco.Enabled = false;
 txtTes_Id.Text = "";
 txtTes_Data.Text = "";
 txtTes_Preco.Text = "";

When clicking save button, comes the following Mysql error message as below:

Error: Mysql.Data.Mysqlclient.Mysqlexception (0x80004005): You have on error in your Sql syntax; check the manual that Corresponds to your Mysql server version for the right syntax to use near '('11/03/1965','%d/%m/f%Y'),. tes_preco='l,5' WHERE tes_id= '" at line 1 in Mysql.Data.Mysqlclient.MySqlStream.Readpacket() in Mysqi.Data.Mysqlclient.NativeDriver.Getresult(lnt32& affectedRow, Int64& insertedId) in Mysql.Data.Mysqlclient.Driver.Getresult(lnt32l statementId, Int32& affectedRows, Int64& insertedId) in Mysql.Data.Mysqlclient.Driver.Nextresult(lnt32 statementId, Boolean force) in Mysql.Data.Mysqlclient.MySqlDataReader.Nextresult() in Mysql.Data.Mysqlclient.MySqICommand.Executereader(Commandbehavior behavior) in Mysql.Data.Mysqlclient.MySqICommand.Executenonquery() in Teste_mascara_data_preco.Form1.TsbSalvarTes.Clck(Object Sender, Eventargs e) na C: Users profe Documents Visualstudio2015 Projects Teste_mascara_data_preco Teste_mascara_data_preco Form1.Cs:line 76

I couldn’t locate the problem.

Answer 1

There is not much information so I won’t give a definitive answer. The problem is that probably the columns are of the type datetime and decimal (at least it should be something like that) and is saving a text. So the solution is to convert it to the appropriate type before trying to save. It would be something like that:

Convert.ToDatetime(txtTes_Data.Text)
Convert.ToDecimal(txtTes_Preco.Text)

I put in the Github for future reference.

If you need me may establish a specific format for the date or the value. Other solutions are possible. A simple ToString() may be more appropriate.

But if there’s any chance of data being invalid for the conversion will result in exception, which is not ideal. Then it would be better to use a TryParse() on the date or in value.

There are other problems in the code (for example a wrong typing may break the application), but it is not the focus of the question.