Select does not display data in listview

Question

Select does not display data in listview

Navigation

#1 by (2 votes)

0

I can not get my return from my select in the bank. Where do 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");


                }
            }

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

mysql c# uwp

asked by anonymous 07.04.2017 / 00:11

1 answer

Display return of a SQL query in Excel How to include php code inside the DELIMITER?

score 2 · Accepted Answer

More details are missing in the question, but I believe that the problem may be related to the format of the date that is configured on the system, I have had many problems with it, being configured with a date format pattern on the client machine, and mysql uses another pattern. In these cases MYSQL can not find the data because it converts the value of the date field to string in a 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 entry in the same format or using parameters in the middle of the SQL command and the MySqlCommand object.

Method 1, forcing the Data field format

You can force the formatting of the Date field using the STR_TO_DATE and DATE_FORMAT methods. Here's an example of how you should force 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 str_to_date method documentation for more details: link and about DATE_FORMAT: link

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 to check how the data is written to the database, as if the data is being written to the database as DateTime (Date with time). For the command to work the contents of the txtDate component must contain the same value (including hh:mm:ss ) as the database row.