MySQL query does not show data [closed]

Question

MySQL query does not show data [closed]

Navigation

#1 by (2 votes)

1

Someone to help me, I'm doing a query in MySQL with C #, but it does not show me any data, this is the query I'm doing:

public DataTable Espera(String Programa, String FI, String FF)
    {
        DataTable dt = new DataTable();
        String query = @"SELECT IL.Cliente, CA.Programa, IL.Estilo,
                                IL.Color, SUM(IL.Total) AS Pares, 
                                CA.Material, CA.Departamento,
                                CA.Fecha_Pago AS Fecha_Pedido 
                         FROM  galaxyfx_produccion.compras_ange AS CA INNER 
                            JOIN galaxyfx_produccion.infolote AS IL 
                            ON CA.Programa = IL.Programa 
                         WHERE (CA.Programa LIKE '%"+ Programa + @"%') 
                         AND (CA.Estado = 'En Espera') 
                         AND (CA.Fecha_Pago BETWEEN '"+ FI 
                              +"' AND '"+ FF + @"') 
                         GROUP BY CA.Material, CA.Programa, 
                                  CA.Departamento, CA.Fecha_Pago";

        dt = daccess.select(query);
        return dt;
    }

I have already modified it several times, but it does not work, besides that if I remove the date range, it works normally.

c# windows-forms mysql-workbench

asked by Macx 02.10.2018 в 00:04

source

1 answer

Check with session :: flash in Laravel How to check checkbox of a table already generated dynamically from a JSON, jquery?

score 2 · Accepted Answer

The problem is this, you can not compare dates like this, NEVER . why ? because the representation of the date depends on each culture / regional configuration. so first you must make sure you are using the same date format in the DB and in the string you receive as a parameter.

Let's say that the values that you receive as a parameter are format dd / mm / yyyy in that case the fields of the bd that are DATE you must convert them to a string with that format.

Think of it as "One thing is you and another is how you dress" a date type has a lot of information and you can show it in different ways, so to make sure you make a good comparison you have to show it in the form you need, in this case 'dd / mm / yyyy'

The bad thing is that each BD does it differently, so you have to deal with that if you're still using the approach you chose in your code, which I should say is not the best.

Par mysql, if you want to show the string as dd / mm / yyyy you should use something like this to show the date of the BD in a different way. DATE_FORMAT(CA.Fecha_Pago, "%d/%m/%Y")

 AND ( DATE_FORMAT(CA.Fecha_Pago, "%d/%m/%Y") BETWEEN '"+ FI +"' AND '"+ FF + @"')

Apart from that there are many other things wrong, based on the fact that the code is extremely insecure.

Try to use parameter binding as shown in this example, if you go to this scheme all conversions can be done uniformly in C # without impregating what DB engine you use.

   static void CountCourses(String connectionString, Int32 year) {  
      String commandText = "Select Count([CourseID]) FROM [MySchool].[dbo].[Course] Where Year=@Year";  
      SqlParameter parameterYear = new SqlParameter("@Year", SqlDbType.Int);  
      parameterYear.Value = year;  

      Object oValue = SqlHelper.ExecuteScalar(connectionString, commandText, CommandType.Text, parameterYear);  
      Int32 count;  
      if (Int32.TryParse(oValue.ToString(), out count))  
         Console.WriteLine("There {0} {1} course{2} in {3}.", count > 1 ? "are" : "is", count, count > 1 ? "s" : null, year);  
   }

And even more important, get away from that and try to use the entity framework and LinQ better

link