VB.NET RDLC报表传递日期范围失效显示全量记录问题排查求助

• SQL拼接与参数化逻辑冲突
  你当前的代码存在重复传值逻辑：WHERE条件中已经直接把DateTimePicker1.Text、DateTimePicker2.Text拼接进了SQL字符串，后续添加的DateTimePicker1、DateTimePicker2两个参数完全没有被SQL语句引用，属于无效代码。同时拼接时DateTimePicker1.Text末尾多写了一个多余空格，会导致字符串比对异常，过滤规则失效。
• 日期类型转换错误
  你将datetime类型的InvoiceDate转为varchar类型做字符串区间比对，极易受日期格式、系统时区、字段精度影响导致比对结果不符合预期，无需转字符串，直接用datetime类型比对即可。
• 参数命名不规范
  SQL Server的参数化查询要求参数名带@前缀，你添加参数时未加前缀，就算SQL语句中引用了参数也会无法识别。

Dim strSQL As String = "", strSQLID As String = "", strSQLOrderBy As String = ""
' 改用参数化查询，不在SQL里拼接日期值
Dim query As String = " select o.InvoiceDate,o.RegistrationNo,o.PatientName,o.TransactionStatus,o.gender,o.company,o.Visittype,o.Doctor,o.Specialisation,"
query &= " o.departmentName,o.Address,o.CityName,o.StateName,o.MobileNo,o.ServiceName"
query &= " from DRT_VW_OPVISIT o "
query &= " inner join employee e on o.DoctorId=e.ID "
query &= " where o.InvoiceDate between @StartDate and @EndDate "
query &= " and  o.TransactionStatus='Active' "

If Trim(strID) <> "" Then strSQLID = strSQLID & " And e.ID In (" + strID + ")"
strSQLOrderBy = " order by o.InvoiceDate desc "

conn = GetConnect()
conn.Open()

query = query & strSQLID & strSQLOrderBy
Dim cmd As SqlCommand = New SqlCommand(query, conn)
' 正确添加带前缀的参数，直接传DateTime类型的值，不需要转字符串
cmd.Parameters.AddWithValue("@StartDate", Me.DateTimePicker1.Value.Date)
cmd.Parameters.AddWithValue("@EndDate", Me.DateTimePicker2.Value.Date.AddDays(1).AddSeconds(-1)) ' 处理EndDate当天的时间精度问题，避免漏掉当天有时间部分的记录

Dim sda1 As SqlDataAdapter = New SqlDataAdapter(cmd)
Dim dt2 As DataTable = New DataTable()
sda1.Fill(dt2)

' 验证过滤结果：调试时可在这里看dt2的行数是否符合预期，确认SQL过滤没问题再绑定报表
With RepDoctorWiseVisit.ReportViewer1.LocalReport
    .ReportPath = "D:\VB Project 2021\RepDepartmentWisePrice\RepDepartmentWisePrice\Report3.rdlc"
    .DataSources.Clear()
    .DataSources.Add(New Microsoft.Reporting.WinForms.ReportDataSource("DataSet1", dt2))
End With
RepDoctorWiseVisit.Show()
RepDoctorWiseVisit.ReportViewer1.RefreshReport()

Return dt2

如果修复后还是有问题，可以先把拼接完成的SQL语句拿到SQL Server Management Studio中执行，确认返回的结果已经是过滤后的区间数据，再排查RDLC报表本身是否额外设置了全量数据集的默认规则。

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