Insert date and currency data of a maskedTextBox in MySQL

0

When trying to insert the data entered from a maskedTextBox object, one of type data and another of type currency, in MySQL database, where I have a table named test, created according to the command below:

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

In my form, type WindowsFormApplication , created in Visual Studio Community Edition 2015, I have a save button that has the following code:

 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 you click the save button, the following MySQL error message appears as below:

  

Error: MySql.Data.MySqlClient.MySqlException (0x80004005): You have an 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 = '1, 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 Test_Mascara_Data_Preco.Form1.TsbSalvarTes.Clck (Object sender,   EventArgs e) in C: \ Users \ profe \ Documents \ VisualStudio2015 \ Projects \ Test_Mask_Data_Preco \ Test_Mask_Data_Preco \ Form1.cs: line 76

I could not locate the problem.

    
asked by anonymous 01.05.2016 / 23:16

1 answer

1

There is not much information so I will not give a definitive answer. The problem is that columns are probably of type datetime and decimal (at least they should be something like this) and are writing a text. So the solution is to convert this to the proper type before attempting to burn. It would look something like this:

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

If you need you can set a date format or the value . Other solutions are possible . A simple ToString() might be more appropriate .

But if there is any chance data that is invalid for conversion will result in an exception , which is not ideal. It would be best to use TryParse() on the date or in value .

It has other problems in the code, but it is not the focus of the question.

    
01.05.2016 / 23:51