locked
Cannot create a data reader for dataset 'DatasetName' only when filtering by DateTime RRS feed

  • Question

  • User-508216992 posted

    Hello guys, 

    I have a report in an aspx webform which I update dynamically with an sql command with filters. When the user click the button the query is built according the Textbox that the user filled. Everything works great excepting for the DateTime filters. Curiously in my local machine works perfectly, the problem only occurs when I put the code on the server. I can't figure out if there is a problem with the SQL version or the Datetime format, specially because the rest of the filters are working properly and even the Date filters works well on my Visual Studio.

    private void GetReporteCustom(string command)
    {
    DataTable table = new DataTable();
    table = sql.getDataTable(command);
    
    ReportViewer1.LocalReport.DataSources.Clear();
    ReportViewer1.LocalReport.DataSources.Add(new ReportDataSource("dsReporteGeneral", table));
    ReportViewer1.DataBind();
    ReportViewer1.LocalReport.Refresh();
    }


    protected void ButtonFiltrar_Click(object sender, EventArgs e) { DateTime dateDesde = new DateTime(2017, 4, 1, 0, 0, 0); DateTime dateHasta = DateTime.Now; if (TextBoxFechaDesde.Text != "") { dateDesde = DateTime.Parse(TextBoxFechaDesde.Text); } if (TextBoxFechaHasta.Text != "") { dateHasta = DateTime.Parse(TextBoxFechaHasta.Text); } //GetReportByDate(date); string com = ""; com = "SELECT oper.id as ID, oper.codigo AS Codigo, rem.nombre AS Remitente, dest.nombre AS Destinatario, oper.hora_reg, oper.hora_ret, oper.bandeja_sal AS Bandeja_Sal, oper.hora_pal, oper.operador_pal, oper.hora_pal2, oper.operador_pal2, oper.hora_pal3, oper.operador_pal3, oper.hora_salida, oper.operador_sal, oper.hora_salida2, oper.operador_sal2, oper.hora_salida3, oper.operador_sal3, oper.hora_fin, sec.descripcion as Sector, pal.descripcion as Palomar, oper.envio, oper.operador, oper.paquete, est.estado as Estado, DATEDIFF(hh, oper.hora_reg, GetDate()) as Tiempo " + "from dbo.operaciones AS oper, dbo.remitente as rem, dbo.remitente as dest, dbo.sector as sec, dbo.palomar as pal, dbo.estado as est " + "where rem.id = oper.remitente and dest.id = oper.destinatario and sec.id = oper.sector and pal.id = oper.palomar and oper.estado = est.id"; if (TextBoxFechaDesde.Text != "") { com += " AND oper.hora_reg >= '" + dateDesde + "'"; } if (TextBoxFechaHasta.Text != "") { com += " AND oper.hora_reg <= '" + dateHasta + "'"; } if (TextBoxCodigo.Text != "") { com += " AND oper.codigo LIKE '" + TextBoxCodigo.Text + "%'"; } if (DropDownListTipo.SelectedValue != "Todos") { com += " AND oper.paquete LIKE '" + DropDownListTipo.SelectedValue + "%'"; } if (TextBoxOperador.Text != "") { com += " AND oper.operador LIKE '" + TextBoxOperador.Text + "%'"; } if (DropDownListEnvio.SelectedValue != "Todos") { com += " AND oper.envio LIKE '" + DropDownListEnvio.SelectedValue + "%'"; } if (TextBoxPrecinto.Text != "") { com += " AND oper.precinto LIKE '" + TextBoxPrecinto.Text + "%'"; } if (TextBoxDestinatario.Text != "") { com += " AND dest.nombre LIKE '" + TextBoxDestinatario.Text + "%'"; } if (TextBoxRemitente.Text != "") { com += " AND rem.nombre LIKE '" + TextBoxRemitente.Text + "%'"; } GetReporteCustom(com); } }



    Tuesday, May 30, 2017 5:17 PM

All replies

  • User1967761114 posted

    Hi Faqundo,

    The error message is only occurred when the datasource of the report viewer has no data.

    In your case, the datasource of the report viewer is a datatable, so your app will occurred the error when the datatable is null.

    I guess that the method of “sql.getDataTable(command)” will return a null value when the SQL statements has some errors, you could check it.

    According to your description, the error is occurred when filtering by datetime, and after I review your code, I think there has 2 points will occurred the error.

    (1) In SQL Server, the datetime valid value is between “1753-01-01“ and “9999-12-31“,if the value of datetime out of this range, that will occurred the error.

    (2) You’d better use SqlParameter to instead directly use string value in sql statements.

    For example, in your code:

    if (TextBoxCodigo.Text != "")
    {
         com += " AND oper.codigo LIKE '" + TextBoxCodigo.Text + "%'";
    }
    if (DropDownListTipo.SelectedValue != "Todos")
    {
        com += " AND oper.paquete LIKE '" + DropDownListTipo.SelectedValue + "%'";
    }
    

    You‘d better modify it like the following code.

    List<SqlParameter> sqlParameters = new List<SqlParameter>();
    if (TextBoxCodigo.Text != "")
    {
        com += " AND oper.codigo LIKE @codigo";
        sqlParameters.Add(new SqlParameter("@codigo", TextBoxCodigo.Text + "%"));
    }
    if (DropDownListTipo.SelectedValue != "Todos")
    {
        com += " AND oper.paquete LIKE '@paquete";
        sqlParameters.Add(new SqlParameter("@paquete", DropDownListTipo.SelectedValue + "%"));
    }
    
    DataTable table = sql.getDataTable(com, sqlParameters.ToArray());

    You also could modify the method of getDataTable such like the following code.

    public DataTable getDataTable(string command, params SqlParameter[] sqlParameters)
    {
        //other statements
        using (var sqlCommand = new SqlCommand())
        {
            sqlCommand.Connection = connection;
            sqlCommand.CommandText = command;
            sqlCommand.Parameters.AddRange(sqlParameters);
            //other statements
            //……
        }
    }
    

    For more details about why that’s better to use SqlParameter, you could refer to the following link.

    https://stackoverflow.com/questions/7505808/why-do-we-always-prefer-using-parameters-in-sql-statements

     

    If you have any other questions, please feel free to contact me any time.

    Best Regards

    Even




    Wednesday, May 31, 2017 4:38 AM