error in select mysql c #

Question

error in select mysql c #

Navigation

#1 by (1 votes)

0

I have the following method that consults a range of dates and returns an email associated with a client

but when you run it from the application, it does not return anything but if you execute it from the mysql command line if it works

  DateTime actual = DateTime.Now;
                DateTime fechaprimera = Convert.ToDateTime(actual, new CultureInfo("es-ES"));

                DateTime tiempo = DateTime.Now;
                DateTime fecha = Convert.ToDateTime(tiempo, new CultureInfo("es-ES"));
                fecha = fecha.AddMonths(1);

                StringBuilder sb = new StringBuilder();
                MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT b.correo_empresa FROM tb_extintores a INNER JOIN tb_empresa b ON a.rut_cliente = b.rut_empresa WHERE a.fecha_vencimiento BETWEEN '"+fechaprimera+ "' AND '"+tiempo+"'", conexion.obtenerConexion());

if I execute this line in mysql extract the data

SELECT DISTINCT b.correo_empresa FROM tb_extintores a INNER JOIN tb_empresa b ON a.rut_cliente = b.rut_empresa WHERE a.fecha_vencimiento BETWEEN '2017-11-29' AND '2017-11-30'

the complete method is as follows

    public void cargar()
            {

                try
                {
                    DateTime actual = DateTime.Now;
                    DateTime fechaprimera = Convert.ToDateTime(actual, new CultureInfo("es-ES"));

                    DateTime tiempo = DateTime.Now;
                    DateTime fecha = Convert.ToDateTime(tiempo, new CultureInfo("es-ES"));
                    fecha = fecha.AddMonths(1);

                    StringBuilder sb = new StringBuilder();
                    MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT b.correo_empresa FROM tb_extintores a INNER JOIN tb_empresa b ON a.rut_cliente = b.rut_empresa WHERE a.fecha_vencimiento BETWEEN '"+fechaprimera+ "' AND '"+tiempo+"'", conexion.obtenerConexion());

                    using (MySqlDataReader dr = cmd.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            //Este if hace que en todas las iteraciones salvo en la primera, se añada una coma ',' antes de el siguiente texto
                            if (sb.Length > 0) sb.Append(",");
                            sb.Append(dr["correo_persona"].ToString());
                        }
                    }
                    txtpara.Text = sb.ToString();
                    conexion.obtenerConexion().Close();
                }
                catch (Exception f) { 
MessageBox.Show(f.Message + f.StackTrace, "errror tipo 11");
}
            }

solution:

public void cargar()
        {

            try
            {


                StringBuilder sb = new StringBuilder();
                {

                        using (var cmd = new MySqlCommand("SELECT DISTINCT b.correo_empresa FROM tb_extintores a INNER JOIN tb_empresa b ON a.rut_cliente = b.rut_empresa WHERE a.fecha_vencimiento BETWEEN @inicio AND @fin", conexion.obtenerConexion()))
                        {
                        cmd.Parameters.AddWithValue("@inicio", DateTime.Now.Date);
                        cmd.Parameters.AddWithValue("@fin", DateTime.Now.Date.AddMonths(1));


                        using (MySqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                //Este if hace que en todas las iteraciones salvo en la primera, se añada una coma ',' antes de el siguiente texto
                                if (sb.Length > 0) sb.Append(",");
                                sb.Append(dr["correo_empresa"].ToString());
                            }
                        }

                        txtpara.Text = sb.ToString();

                        conexion.obtenerConexion().Close();
                    }
                }
            }
            catch (Exception f) {

            }
        }

mysql c#

asked by sebastian bizama inostroza 29.11.2017 в 19:46

source

1 answer

How to go through the items in a RecyclerView How can I use the contents of a variable that originated in a foreach, outside of it in PHP?

score 1 · Accepted Answer

You have a few problems.

First, it seems that in your query, your intention was to use fechaprimera (now) and fecha (1 month in the future). But instead, you're using fechaprimera (now) and tiempo (now). This is a problem, because both variables contain DateTime.Now .

Second, I do not understand why you do so many conversions to the dates. This is not necessary.

And finally, although you can technically achieve queries by concatenating variables directly in SQL, never do that, particularly with dates. You expose yourself to subtle errors and to SQL injection. Use parameters:

using (var cmd = new MySqlCommand("SELECT DISTINCT b.correo_empresa FROM tb_extintores a INNER JOIN tb_empresa b ON a.rut_cliente = b.rut_empresa WHERE a.fecha_vencimiento BETWEEN @inicio AND @fin", conexion.obtenerConexion()))
{
    cmd.Parameters.AddWithValue("@inicio", DateTime.Now);
    cmd.Parameters.AddWithValue("@fin", DateTime.Now.AddMonths(1));

    using (MySqlDataReader dr = cmd.ExecuteReader())
    {
        // ...
    }
}

Additionally, something that I repeat whenever I see it, it is not good practice to use global connections. To avoid too.