Insert with monthCalendar in Mysql and C#database

Question

I need to make a insert of the date with the monthCalendar in C# in the database MySQL, only that the format of the date of MySQL is (yyyy-MM-dd) and I’ve already modified the code :

I modified the input mode (yyyy-MM-dd)

cadast.Dataa=Convert.ToDateTime(monthCalendar1.SelectionStart.Date.ToString("yyyy-MM-dd"));

And modified in the class that makes the insert in the database:

string inserir = "INSERT INTO consultas (Dataa,Horario, cd_paciente, cro, cd_procedimento) values (str_to_date('" + cadast.Dataa + "','%Y-%m-%d'),'"+cadast.Horario+ "', '" + cadast.cd_paciente + "','" + cadast.cro + "' ,'" + cadast.cd_procedimento + "')";

And he presents the following error:

Command error Correct datetime value: '30/11/2016 00:00:00' for Function str_to_date.

I modified the insert for:

string inserir = "INSERT INTO consultas (Dataa,Horario, cd_paciente, cro, cd_procedimento) values (str_to_date('" + cadast.Dataa + "','%Y-%m-%d %h:%i:%s %p'),'"+cadast.Horario+ "', '" + cadast.cd_paciente + "','" + cadast.cro + "' ,'" + cadast.cd_procedimento + "')";

and modified in the field of calendario(MonthCalendar) to see if the problem was in him:

cadast.Dataa=
   Convert.ToDateTime(monthCalendar1.SelectionStart.Date.ToString("%Y-%m-%d %h:%i:%s%p"));

Then he presented the following error:

The string was not recognized as a valid Datetime. There is an unknown word that starts at index 0.

It didn’t work either I’ve tried every possible way and so far I haven’t succeeded, someone can help me?

Answer 1

It is always recommended to use Parameterized Query when executing SQL commands that have parameters. This way you avoid security problems such as SQL Injection and breaking need not worry about formatting data, avoiding problems like this one you reported.

Look at an example

using(SqlConnection conn = new SqlConnection(connectionString))
{
  conn.Open();
  using(SqlCommand cmd = new SqlCommand("INSERT INTO consultas (nome, data) values (@nome @data)", conn)) 
  {
    cmd.Parameters.AddWithValue("@nome", MySqlDbType.VarChar).Value = "Jon Snow";
    cmd.Parameters.AddWithValue("@data", MySqlDbType.DateTime).Value = DateTime.Now;
    cmd.ExecuteNonQuery();
  }
}

Note that we are passing an object of the type DateTime for our command without doing any type of formatting.

Answer 2

Good night,

I manage to fix this date problem I will post my code here in case anyone needs help in the future.

The solution was: I continued with the Dataa field in the Mysql database as date and in the programming I left the data as string and changed the date input mode in the monthCalendar so:

//Deixei como string, e mudei o modo de entrada da data no monthCalendar
       cadast.Dataa = monthCalendar1.SelectionStart.Date.ToString("yyyy-MM-dd");

The whole Code went like this:

//classe class cadas_consulta {

//Deixei a data como string
    private string nDataa;



    public string Dataa
    {

        get { return nDataa; }
        set { nDataa = value; }
    }

//classe class dal_consulta { private MySqlConnection conexao;

     public void cadastro(cadas_consulta cadast)
     {
         string caminho = "SERVER=localhost;DATABASE=odonto_system;UID=root;PASSWORD=;";

         try
         {

             conexao = new MySqlConnection(caminho);
             conexao.Open();

             string inserir = "INSERT INTO consultas (Dataa,Horario, cd_paciente, cro, cd_procedimento) values ('" + cadast.Dataa + "','"+cadast.Horario+ "', '" + cadast.cd_paciente + "','" + cadast.cro + "' ,'" + cadast.cd_procedimento + "')";



             MySqlCommand comandos = new MySqlCommand(inserir, conexao);

             comandos.ExecuteNonQuery();
             MessageBox.Show("Consulta Cadastrada com Sucesso!");
             conexao.Close();

         }

         catch (Exception ex)
         {

             throw new Exception("Erro de comandos" + ex.Message);
         }
     }
}

//inserir no botão private void button1_Click(object sender, EventArgs e) {

        cadas_consulta cadast = new cadas_consulta();
        dal_consulta daa = new dal_consulta();

//Deixei como string também, e mudei o modo de entrada da data para padrão do banco MySql cadast.Dataa = monthCalendar1.SelectionStart.Date.ToString("yyyy-MM-dd");

        daa.cadastro(cadast);


    }`

I thank everyone who helped, because your answers help us to study more and research.

Thank you!