Reporting

Question

Good night, should be simple thing, but I’m breaking my head and it’s not going.

I made a report of the college project "Input Parts in Stock" and is working perfectly. When I did the tests the DateTime with the hour was zeroed, then I set the time and is working perfectly my Form.

However, only dates that are at zero time (00:00:00) appear in the report, but when you have a specified time (18:35:06) does not show.

They follow the images and the code, I’m doing the project in layers, but I have no idea how to do the report by taking the layer DAO, so I did it in the layer View. It’s wrong to do so?

code

 private void btnGerar_Click(object sender, EventArgs e)
    {
        SqlCommand cmd = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet dsum = new DataSet();
        DataTable oTable = new DataTable();

        String strReportPath = "";
        try
        {
            strReportPath = @"Report1.rdlc";
            reportViewer1.LocalReport.ReportPath = strReportPath;
            SqlConnection conn = new SqlConnection(@"Data Source=DENILSON-PC;Initial Catalog=dbSistEstoqueEmp;Integrated Security=True");
            cmd.Connection = conn;
            cmd.CommandText = "SELECT * FROM tbEntradaEstoque WHERE data_ent = @dataEnt";
            cmd.CommandType = CommandType.Text;
            conn.Open();
            cmd.Parameters.Add("dataEnt", SqlDbType.DateTime).Value = Convert.ToDateTime(maskDataInicial.Text);

            SqlDataReader oDataReader = cmd.ExecuteReader();
            oTable.Load(oDataReader);
            ReportDataSource myReportDataSource = new ReportDataSource("DataSet1", oTable);
            reportViewer1.Clear();
            reportViewer1.LocalReport.DataSources[0] = myReportDataSource;
            reportViewer1.RefreshReport();


        }
        catch (Exception ex)
        {

            MessageBox.Show(ex.Message);
        }
    }

thanks Bruno worked . date in my parameter.

how I am doing in 4 layers is wrong to do the reports in the View layer ?

thank you

Answer 1

The problem lies here instruction:

cmd.CommandText = "SELECT * FROM tbEntradaEstoque WHERE CONVERT(DATE, data_ent) = @dataEnt";
(...)
cmd.Parameters.Add("dataEnt", SqlDbType.DateTime).Value = Convert.ToDateTime(maskDataInicial.Text);

Hence the format of the date in the database is datetime(datetime2). Your instruction converts a date into the format dd/mm/yyyy for the format yyyy/mm/dd hh:mm:ss[.fração de segundos], with the particularity that the hh:mm:ss[fração de segundos] is always 00:00:00 000

Your SQL statement to get the results in the database is as follows:

cmd.CommandText = "SELECT * FROM tbEntradaEstoque WHERE data_ent = @dataEnt";

So you will only return the records you have in the database 00:00:00,000 in the component hh:mm:ss[.fração de segundos],

One solution is to change your instruction to

cmd.CommandText = "SELECT * FROM tbEntradaEstoque WHERE CONVERT(DATE, data_ent) = @dataEnt";

and

cmd.Parameters.Add("dataEnt", SqlDbType.DateTime).Value = Convert.ToDateTime(maskDataInicial.Text).Date;

(Or simply convert the String maskDataInitial.Text directly to Date format (without hh:mm:ss)