locked
Report viewer not showing the exact date filtered by DateTime Picker RRS feed

  • Question

  • Good day,

    I am new in programming and just doing it for hobby putting at least 2 hours everyday to work on a project I have for my wife.

    What I am trying to accomplish is trying to put on report viewer the data gathered on an attendance captured Table i have from the access data base.

    I have a table named Attendance_Info and got some fields as follows primary key is AttID = autonumber, AttSerialNo = short text, Fullname = short text,  Timestamp =  short text, Remarks = short text and last is capturedImage = ole object.

    On Form1 there it captures the attendance on it for AttSerialNo = txtSeriaNo, Fullname =txtFullname, Timestamp = DateTimePicker1 = long(in format at settings), Remarks = txtremarks and the last is captureImage = Picturebox1.

    I was able to save data perfectly from the Form1 capturing attendance. here comes the Form2 which i need to put it on report viewer.

    On Form2 I have DateTimePicker1 and DateTimePicker2 = which I intend to filter the date of the captured attendance, I have also a button1 and the reportviewer1. On the solution explorer I right click then added an object dataset1 where I manually added the DataTable1 with columns AttSerialNo, Fullname, Timestamp and remarks. Then put a report1.rdlc and designed it with column AttSerialNo, Fullname, Timestamp and remarks.

    On the Button1 click event lies the code

     Dim RptDS As ReportDataSource

            ReportViewer1.RefreshReport()

            With ReportViewer1.LocalReport
                .ReportPath = Application.StartupPath & "\Report\Report1.rdlc"
                .DataSources.Clear()
            End With

            Dim ds As New DataSet1
            adapter = New OleDbDataAdapter

            connection = New OleDbConnection
            With connection
                If .State = ConnectionState.Closed Then
                    .ConnectionString = StrCon
                    .Open()
                End If
            End With

            sql = "SELECT * FROM Attendance_Info WHERE Timestamp Between '" & (DateTimePicker1.Value.ToString("MMMM dd, yyyy")) &
                "' AND '" & (DateTimePicker2.Value.ToString("MMMM dd, yyyy")) & "'"
            adapter.SelectCommand = New OleDbCommand(sql, connection)
            adapter.Fill(ds.Tables("DataTable1"))
            connection.Close()

            RptDS = New ReportDataSource("DataSet1", ds.Tables("DataTable1"))
            ReportViewer1.LocalReport.DataSources.Add(RptDS)
            ReportViewer1.SetDisplayMode(DisplayMode.PrintLayout)
            ReportViewer1.ZoomMode = ZoomMode.Percent
            ReportViewer1.ZoomPercent = 70

    Here comes the problem, it is not filtering the date between the DateTimePicker1 and DateTimePicker2. Instead it shows date and time which is not part of the filter date like for an example if you will choose month of May to June it will bring up February and March. It is not showing the accurate date and time from my specified filter dates.

    I am sorry for the long description of my problem however to make more sense and easy for those people to understand where I am coming from.

    Yours Truly,

    Jepoyman

    Thanks ,





    • Edited by jepoyman Wednesday, June 17, 2020 7:43 AM
    Wednesday, June 17, 2020 7:39 AM

All replies

  • Does it work if you change the condition to ‘…where CAST(Timestamp as date) between '" & DateTimePicker1.Value.ToString("yyyy-MM-dd") & "'and '" & DateTimePicker2.Value.ToString("yyyy-MM-dd 23:59:59") & "'"?

    Although, parameterized queries are more appropriate instead of string concatenation.



    • Edited by Viorel_MVP Wednesday, June 17, 2020 8:29 AM
    Wednesday, June 17, 2020 8:22 AM
  • I did your advise to CAST it however it gave me error message

    Additional information: IErrorInfo.GetDescription failed with E_FAIL(0x80004005)
    • Edited by jepoyman Wednesday, June 17, 2020 10:29 PM
    Wednesday, June 17, 2020 10:29 PM
  • I observed the problem of my code it pulls up the data whenever you filter it from single specified month, like for an example if the data was made in month of April the first datetimepicker1 should be set on April 1st then the second datetimepicker2 is set on April 30th. With this kind of filter it brings exactly all data from April however I would like to get it more dynamic which you can select for example month of January to March, I would like to see only those in the covered filter January and March.

    With my case it is not happening like that it brings up even the month not covered within the filter.

          sql = "SELECT * FROM Attendance_Info WHERE Timestamp BETWEEN '" & DateTimePicker1.Value.ToString("MMMM dd, yyy hh:mm:ss") & "'AND'" &
                DateTimePicker2.Value.ToString("MMMM dd, yyy hh:mm:ss") & "'"

    If you have better code than mine may you kindly please share it or make some revision on my codes.

    Thanks.


    • Edited by jepoyman Thursday, June 18, 2020 4:50 AM
    Thursday, June 18, 2020 4:48 AM
  • Hi jepoyman,

    Thanks for your feedback.

    I suggest you use the Parameter to avoid SQL injections , like this :

                string cmdtxt = "SELECT * FROM Attendance_Info WHERE Timestamp Between @startDate AND @endDate";
                OleDbCommand cmd = new OleDbCommand(cmdtxt, con);
                cmd.Parameters.AddWithValue("@startDate ", DbType.DateTime).Value = dateTimePicker1.Value.ToString("yyyy/MM/dd");
                cmd.Parameters.AddWithValue("@endDate ", DbType.DateTime).Value = dateTimePicker2.Value.ToString("yyyy/MM/dd");

    I make a simple test on my side.

    Data in my database:

    Result:

    Hope it could be helpful.

    Best Regards,

    Xingyu Zhao


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 19, 2020 2:06 AM