Problems working with dates in the database using a maskedtextbox in the format dd / MM / yyyy

0

I'm using a MaskedTextBox in the en-BR culture to force the user to fill the date in the correct format (dd / mm / yyyy). Retrieve the string from the MaskedTextBox to insert into the database and record information containing the date of this information. My intention is that the user can not only register some information, but also perform a search by period of time filtering the dates through two MounthCalendar.

The problem is that the information is apparently being recorded in MM / dd / yyyy format, and when I do the per-period search, the database returns the dates with the inverted months with the dates.

For example, if I record an event called "Calibration" with date 02/03/2016 (March 2, 2016), in the database the record stays as February 3, 2016. And if I do a survey in the period from March 1, 2016 to March 31, 2016, the "Calibration" event does not appear in the results. But if you do the research in the period between 01 and 29 February 2016 the event appears as if it was registered on February 3.

I do not know if I'm wrong, but I think the problem is happening at the time of inserting in the database, I'm trying to convert the string that is typed in the MaskedTextBox to the MM / dd / yyyy format but I'm not getting it. p>

I've tried to use the DateTime.ParseExact but the error: "String was not recognized as a valid DateTime."

string dataDoFat = mskData.Text;
System.Globalization.DateTimeStyles none = new System.Globalization.DateTimeStyles();
IFormatProvider format = new System.Globalization.CultureInfo("en-US", true);
DateTime dtime = DateTime.ParseExact(dataDoFat, "MM/dd/yyyy",format);

The code I use to insert the records into the database is this:

            Conexao ca = new Conexao();
            string sql = "";
            sql += " Insert Into SisIndice (FAT, nSerie, idDefeito, DataFat) ";
            sql += " Values ( ";
            sql += " " + txtFat.Text + ", ";
            sql += " '" + txtnSerie.Text + "', ";
            sql += " " + cboDefeito.SelectedValue + ", ";
            sql += " #" + mskData.Text  + "# ";
            sql += " ) ";
            ca.Conectar();
            OleDbCommand cd = new OleDbCommand(sql, ca.cx);
            try
            {
                cd.ExecuteNonQuery();
                MessageBox.Show("Ocorrência registrada.");
            }
            catch (OleDbException x)
            {
                MessageBox.Show("Erro: " + x.Message);
            }
            ca.Desconectar();
            LimparCampos();

And what I use to read the records of the database in the time period chosen by the user is this:

        string DataInicio = mcInicio.SelectionStart.Date.ToString("MM/dd/yyyy");
        string DataFim = mcFim.SelectionStart.Date.ToString("MM/dd/yyyy");

        Conexao ca = new Conexao();
        string sql = "";
        sql += " Select ";
        sql += " s.idProduto, s.FAT, s.nSerie, d.Defeito, s.DataFat ";
        sql += " From ";
        sql += " (SisIndice s INNER JOIN ";
        sql += " Defeitos d ON s.idDefeito = d.idDefeito) ";
        sql += " WHERE(((s.DataFat) BETWEEN ";
        sql += " #" + DataInicio + "# ";
        sql += " and ";
        sql += " #" + DataFim + "#)) ";
        ca.Conectar();
        OleDbDataAdapter da = new OleDbDataAdapter(sql, ca.cx);
        DataSet ds = new DataSet();
        da.Fill(ds, "SisIndice");

        dgvTestGraf.DataSource = ds.Tables["SisIndice"];
        ca.Desconectar();

Actually the solution is to convert the date format inserted in the database? What is the best way to do this?

Thank you in advance!

    
asked by anonymous 07.03.2016 / 21:20

1 answer

1

Leandro,

It is probably reversing the day and month at the time of insertion.

The best way to work with date is to send the parameter in your command.

Command.Parameters.Add(new System.Data.OleDb.OleDbParameter("nome","valor"));

Using this way, OleDb itself will convert to the correct format.

Aside from the code being safer avoiding SQL Injection.

    
08.03.2016 / 03:24