Select does not display data in listview

Question

I cannot get feedback from my select in the database. Where I show the data in a listview.

using (_connection = new MySql.Data.MySqlClient.MySqlConnection("Database=roubo_furto; Data Source=192.168.0.17;User Id=RFID;Password=1234;SslMode=None;"))
            {
                System.Text.EncodingProvider ppp;
                ppp = System.Text.CodePagesEncodingProvider.Instance;
                Encoding.RegisterProvider(ppp);

                _connection.Open();
               var cmd = new MySqlCommand("SELECT distinct id, Carro, Placa, " +
      "Fabricante, Ano, Cor, Data FROM tcc " +
      "WHERE DATE_FORMAT(Data,'%d/%m/%Y') = STR_TO_DATE(" + txtDate.Date.ToString("dd/MM/yyyy") + 
      ", '%d/%m/%Y')", _connection);

                using (var reader = cmd.ExecuteReader())
                {
                    while (reader.Read())


                        listView.Items.Add("Ocorrencia: Nº " + reader.GetString(0) + "    " + "\nData: " + Convert.ToDateTime(reader.GetString(7)).ToString("dd/MM/yyyy") + "    " + "\nCarro: " + reader.GetString(2) + "    " + "\nPlaca: " + reader.GetString(3) + "    " + "\nCor: " + reader.GetString(6) + "    " + "\nAno: " + reader.GetString(5) + "    " + "\nFabricante: " + reader.GetString(4) + "\n\n");


                }
            }

        <DatePicker x:Name="txtDate" Grid.ColumnSpan="2" HorizontalAlignment="Left" Margin="0,208,0,0" VerticalAlignment="Top" Width="415" ToolTipService.ToolTip="" MonthFormat="{}{month.solo.full}">

Answer 1

More details are missing in the question, more I believe that the problem may be in relation to the format of the date that is configured on the system, I have had many problems with it, of being configured with a standard date format on the client machine and mysql using another standard. In these cases MYSQL cannot find the data, because it converts the value of the data field to string in one format (by default it is 'YYYY-MM-DD') and the filter data is in another ('DD/MM/YYYY' for example).

There are two ways to resolve the issue by formatting the date field and forcing data into the same format or using parameters in the middle of the SQL command and in the Mysqlcommand Object.

Method 1, forcing the format of the Date field

You can force the Date field format with the help of STR_TO_DATE and DATE_FORMAT methods. Below is an example of how you should be forcing a 'DD/MM/YYYY' format'.

var cmd = new MySqlCommand("SELECT distinct id, Carro, Placa, " +
          "Fabricante, Ano, Cor, Data FROM tcc " +
          "WHERE DATE_FORMAT(Data,'%d/%m/%Y') = STR_TO_DATE(" + txtDate.Date.ToString("dd/MM/yyyy") + 
          ", '%d/%m/%Y')", _connection);

See more details in the str_to_date method documentation: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_str-to-date and on DATE_FORMAT: https://www.w3schools.com/sql/func_date_format.asp

Method 2, using parameters

Just use Parameters in Mysqlcommand:

var cmd = new MySqlCommand("SELECT distinct id, Carro, Placa, " +
          "Fabricante, Ano, Cor, Data FROM tcc " +
          "WHERE Data = @dataParametro", _connection);

cmd.Parameters.AddWithValue("@dataParametro", txtDate.Date);

It would be ideal for you to check how the data is recorded in the database, because if the data is being recorded in the database as DateTime (Date with time). For the command to work the contents of the component txtDate shall contain the same value (including hh:mm:ss) that the row of the database.