CODEWITHSUNDEEP
c#asp.netsql-serverrdlcsql-server-2008r2-express
NoobCode
NoobCode

Reputation: 1

User Input Date And Time as SQL Query Parameters in RDLC Reports

I am trying to build an ASP NET website which generates RDLC reports from SQL DataBase data but while trying to use user input by two TextBoxes it just doesn't show anything in the report, can someone tell me why?

Here's the code:

   protected void ShowReport()
   {
        DataTable dtOrders = GetOrdersByDates(DateTime.Parse(txtStartDate.Text),
        DateTime.Parse(txtEndDate.Text));
        RV1.Reset();
        RV1.LocalReport.ReportPath = Server.MapPath("~/reports/Report1.rdlc");
        ReportDataSource ds = new ReportDataSource("DataSet1", dtOrders);
        RV1.LocalReport.DataSources.Add(ds);
    }

    private DataTable GetOrdersByDates(DateTime from, DateTime to)
    {
        DataTable dt = new DataTable();
        String ConnectionString = @"Data Source=(local)\sqlexpress;initial Catalog=Operations;integrated Security=true;";
        using (SqlConnection cn = new SqlConnection(ConnectionString))
        {
        string query = @"SELECT Date AS Datet, nUser AS cvUser, NameUser AS User, COUNT(nTicket) AS notravels, CASE WHEN flagout = 0 THEN 'NO' ELSE 'YES' END AS FlaggedOut, '"+ from +"' AS FI, '" + to + "' AS FF FROM Operations WHERE Date BETWEEN '"+ from + "' AND '"+to+"' GROUP BY Date, nUser, NameUser, Flagout ORDER BY cvUser";
            SqlCommand cmd = new SqlCommand(query, cn);
            cn.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows) {
                dt.Load(reader);
            }
        }
        return dt;
    }

I've already done the Stored Procedure on SQL Management Studio and tested setting the query as the stored procedure using this code manually and it works this way

 protected void Users_Click(object sender, EventArgs e)
 {
    String ConnectionString = @"Data Source=(local)\sqlexpress;initial Catalog=Operations;integrated Security=true;";
    SqlConnection conn = new SqlConnection(ConnectionString);
    SqlCommand cmd = new SqlCommand();
    cmd.Connection = conn;

    string query = @"SELECT CONVERT(datetime, MAX(Date)) AS Datet, nUser AS cvUser, NameUser AS User, COUNT(nTicket) AS notravels, CASE WHEN flagout = 0 THEN 'NO' ELSE 'YES' END AS FlaggedOut, CONVERT (datetime, '2022/12/09 6:0:00.000') AS FI, CONVERT(datetime, '2022/12/10 6:0:00.000') AS FF FROM Operations WHERE (CONVERT(datetime, Date) BETWEEN CONVERT(datetime, '2022/12/09 6:0:00.000') AND CONVERT (datetime, '2022/12/10 5:59:59.999')) GROUP BY Date, nUser, NameUser, Flagout ORDER BY cvUser";


    cmd.CommandText = "PR_CantUsers";
    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    conn.Open();
    SqlDataReader dr = cmd.ExecuteReader();
    DataTable dt = new DataTable();
    dt.Load(dr);
    conn.Close();

    RV1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1", dt));
    RV1.LocalReport.ReportPath = Server.MapPath("~/reports/Report1.rdlc");
    RV1.LocalReport.EnableHyperlinks = true;
}

Upvotes: 0

Views: 255

Answers (1)

NoobCode
NoobCode

Reputation: 1

So i figured out how to do it but on Crystal Reports using the same method, first of all i cleared all the code above and rewrite new lines of code, this is how i did it:

protected void Fleteros_Click(object sender, EventArgs e)
    {
        rptCantFleteros crystalReport10 = new rptCantFleteros();

        SqlConnection connection = new SqlConnection(ConnectionString);
        SqlDataAdapter sda = new SqlDataAdapter();
        CntFleteros dsCntFleteros = new CntFleteros();

        crystalReport10.Load(Server.MapPath("~/reportes/rptCantFleteros.rpt"));
        string sqlQuery = "SELECT nFletero AS cvecamion, Convert(datetime, MAX(Fecha)) as Expr1, COUNT(nTicket) AS noviajes, CASE WHEN flagsalida = 0 THEN 'NO' ELSE 'SI' END AS Destarado, NombreFlete, CONVERT(datetime, @fechaInicio) AS fi, CONVERT(datetime, @fechaFin) AS ff "
                          + "FROM BSCOperacion WHERE (Fecha BETWEEN CONVERT(datetime, @fechaInicio) AND CONVERT(datetime, @fechaFin))"
                          + "GROUP BY nFletero, NombreFlete, FlagSalida ORDER BY nFletero";

                SqlCommand cmd = new SqlCommand(sqlQuery, connection);
                cmd.Connection = connection;
                sda.SelectCommand = cmd;
                DateTime fechaI = DateTime.Parse(txtFechaInicio.Text);
                DateTime fechaF = DateTime.Parse(txtFechaFin.Text);
                cmd.Parameters.AddWithValue("@fechaInicio", fechaI);
                cmd.Parameters.AddWithValue("@fechaFin", fechaF);
                sda.Fill(dsCntFleteros, "cntfleteros");

        crystalReport10.SetDataSource(dsCntFleteros);
        CrystalReportViewer1.ReportSource = crystalReport10;

Im pretty sure this is the same method for RDLC as well.

Upvotes: 0

Related Questions