Erronea consultation

Question

Erronea consultation

Navigation

#1 by (-1 votes)

0

Hear someone can tell me what my mistake is:

public DataTable Programas(int Programa, String LoteI, String LoteF)
    {
        DataTable dt = null;

        String query = "SELECT DL.Programa, IL.Modelo, IL.Color, IL.Cliente, IL.Linea, DL.Cantidad, DL.Lote, DL.T150, DL.T155, DL.T160, DL.T165, DL.T170, DL.T175, DL.T180, DL.T185, DL.T190, DL.T195, DL.T200, DL.T205, DL.T210, DL.T215," +
                       "DL.T220, DL.T225, DL.T230, DL.T235, DL.T240, DL.T245, DL.T250, DL.T255, DL.T260, DL.T265, DL.T270, DL.T275, DL.T280, DL.T285, DL.T290, DL.T295, DL.T300  " +
                       "FROM InfoLote AS IL INNER JOIN DetalleLote AS DL ON IL.Programa = DL.Programa WHERE (DL.Programa = '" + Programa + "') AND (DL.Lote BETWEEN '"+ LoteI +"' AND '"+ LoteF +"')";
        dt = daccess.select(query);
        return dt;
    }

I've been doing it a lot, I've already changed the sql instruction many times, but it's still not happening, I'm supposed to throw all the corresponding data when I put the program number, but only this result appears:

I already compare it with other codes that I have and apparently there is nothing wrong.

c#

asked by Macx 31.10.2018 в 00:53

source

1 answer

Send data from one Activity to another in Android Studio [duplicated] how to pass the values by means of a button from one TextArea to another TextArea on the same page in ASP [closed]

score -1 · Accepted Answer

You have to use parameters in the code, it is not good to concatenate the values in a string

DataTabla dt = new DataTable(); 
using (SqlConnection conn = new SqlConnection("connectionstring")) 
{ 

    string query = @"SELECT DL.Programa, IL.Modelo, IL.Color, IL.Cliente, IL.Linea, DL.Cantidad, DL.Lote, DL.T150, DL.T155, DL.T160, DL.T165, DL.T170, DL.T175, DL.T180, DL.T185, DL.T190, DL.T195, DL.T200, DL.T205, DL.T210, DL.T215,
                   DL.T220, DL.T225, DL.T230, DL.T235, DL.T240, DL.T245, DL.T250, DL.T255, DL.T260, DL.T265, DL.T270, DL.T275, DL.T280, DL.T285, DL.T290, DL.T295, DL.T300 
                   FROM InfoLote AS IL INNER JOIN DetalleLote AS DL ON IL.Programa = DL.Programa 
                   WHERE (DL.Programa = @Programa) AND (DL.Lote BETWEEN @LoteI AND @LoteF)";

    SqlCommand cmd = new SqlCommand(query, conn); 
    cmd.Parameters.AddWithValue("@Programa", Programa);
    cmd.Parameters.AddWithValue("@LoteI", LoteI);
    cmd.Parameters.AddWithValue("@LoteF", LoteF);

    SqlDataAdapter da = new SqlDataAdapter(cmd); 
    da.Fill(dt); 
}

adapt daccess.select() to be able to pass a list of SqlParameter or otherwise discard it because like this, it is not correct

On the other hand, try to vary the query to see how it changes its response and determine that it does not return records, you could remove the inner join, you could remove the parameters, then add only one and then the other, change to see How do you respond?

If you need the filter to be conditional you can apply something like this

DataTabla dt = new DataTable(); 
using (SqlConnection conn = new SqlConnection("connectionstring")) 
{ 

    string query = @"SELECT DL.Programa, IL.Modelo, IL.Color, IL.Cliente, IL.Linea, DL.Cantidad, DL.Lote, DL.T150, DL.T155, DL.T160, DL.T165, DL.T170, DL.T175, DL.T180, DL.T185, DL.T190, DL.T195, DL.T200, DL.T205, DL.T210, DL.T215,
                   DL.T220, DL.T225, DL.T230, DL.T235, DL.T240, DL.T245, DL.T250, DL.T255, DL.T260, DL.T265, DL.T270, DL.T275, DL.T280, DL.T285, DL.T290, DL.T295, DL.T300 
                   FROM InfoLote AS IL INNER JOIN DetalleLote AS DL ON IL.Programa = DL.Programa 
                   WHERE (DL.Programa = @Programa) 
                   AND ((@LoteI IS NULL) OR (DL.Lote BETWEEN @LoteI AND @LoteF))";

    SqlCommand cmd = new SqlCommand(query, conn); 
    cmd.Parameters.AddWithValue("@Programa", Programa);

    if(opcional) //aqui condicion si aplica o no ese filtro
    {
        cmd.Parameters.AddWithValue("@LoteI", DBNull.Value);
        cmd.Parameters.AddWithValue("@LoteF", DBNull.Value);
    }
    else
    {
        cmd.Parameters.AddWithValue("@LoteI", LoteI);
        cmd.Parameters.AddWithValue("@LoteF", LoteF);
    }

    SqlDataAdapter da = new SqlDataAdapter(cmd); 
    da.Fill(dt); 
}

As you will see in the WHERE you can ask if the parameter is null and cancel that condition, there ask only for one parameter, but apply in both