ASP.NET网站RDLC报表无法显示用户日期筛选的SQL数据问题

我正在搭建一个从SQL数据库生成RDLC报表的ASP.NET网站，但使用两个TextBox的用户输入日期作为筛选条件时，报表始终无数据显示，请求排查原因。

有问题的代码实现 #

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;
}

可正常运行的存储过程调用代码 #

我已在SQL Server Management Studio中创建存储过程，手动设置参数调用时报表可正常显示，代码如下：

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;
}

排查要点及修复方案 #

1. 日期类型转换与格式问题 #

从存储过程代码可见，你使用了CONVERT(datetime, Date)，说明数据库中Date字段大概率是字符串类型（varchar/nvarchar）而非datetime类型。直接拼接.NET的DateTime对象为字符串传入SQL，会因格式不匹配导致筛选失效——比如.NET默认输出的日期格式可能与数据库存储格式不一致，造成字符串比较不成立。

2. 直接拼接SQL的风险与错误 #

手动拼接DateTime到SQL语句不仅存在SQL注入风险，还会因系统区域设置影响日期ToString()输出格式，导致生成的SQL语句不符合SQL Server要求。必须改用参数化查询，让ADO.NET自动处理日期类型转换：

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))
    {
        // 注意：User是SQL关键字，需用[User]转义；同时处理Date字段的类型转换
        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, 
                        @FromDate AS FI, @ToDate AS FF 
                        FROM Operations 
                        WHERE CONVERT(datetime, Date) BETWEEN @FromDate AND DATEADD(MILLISECOND, -3, DATEADD(DAY, 1, @ToDate))
                        GROUP BY Date, nUser, NameUser, Flagout 
                        ORDER BY cvUser";
        SqlCommand cmd = new SqlCommand(query, cn);
        // 添加参数，自动处理类型转换
        cmd.Parameters.Add("@FromDate", SqlDbType.DateTime).Value = from;
        cmd.Parameters.Add("@ToDate", SqlDbType.DateTime).Value = to;
        cn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        dt.Load(reader); // 无需判断HasRows，Load会自动处理空结果
    }
    return dt;
}

3. 时间范围边界问题 #

存储过程中用CONVERT(datetime, '2022/12/10 5:59:59.999')作为结束时间，是为了包含当天所有时段的记录。而有问题的代码直接使用to，若用户输入的是2022-12-10，DateTime.Parse后会是2022-12-10 00:00:00，导致当天所有记录被排除。修复方案是将结束时间调整为to的次日凌晨减去3毫秒，确保包含当天所有数据（对应SQL中的DATEADD(MILLISECOND, -3, DATEADD(DAY, 1, @ToDate))）。

4. SQL关键字冲突 #

查询语句中NameUser AS User的User是SQL Server保留关键字，必须用方括号[User]包裹，否则会引发语法错误，导致查询无结果。

5. 调试验证 #

• 在GetOrdersByDates方法中添加Debug.WriteLine(dt.Rows.Count)，查看返回的数据行数。
• 输出实际执行的SQL语句（参数化查询可通过SQL Server Profiler或日志工具查看），复制到SSMS中执行，确认是否有数据返回。
• 检查txtStartDate和txtEndDate的输入格式是否正确，可添加try-catch捕获DateTime.Parse异常。

内容的提问来源于stack exchange，提问作者NoobCode