We help IT Professionals succeed at work.

DateTimePicker in ReportViewer

Jpcoder
Jpcoder used Ask the Experts™
on
I am trying to add the DateTimePicker to my report, the SQL query will run in the configuration wizard but not on the report.

Please help if you can.

Here is what I have:

I am using VB 2005 ReportViewer  
I have a dataset  tableadapter  that queries a SQL db
------------------------------------------
On my report page I have two DateTimePicker

DateTime1_Start
DateTime2_End
-------------------------------------------
My TableAdapter SQL Statement:

SELECT        AVG(Unit) AS AUnit, AVG(StandardUnits) AS SUnits, AVG(Case) AS ACase , Shift, LineNumber,

FROM            Line2Data

WHERE        (DateAndTime BETWEEN @DateAndTime1_Start AND @DateAndTime2_End)

---------------------------------------------
My Report VB code:

Private Sub DateAndTime1_Start_ValueChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles DateAndTime1_Start.ValueChanged
       
Me. Line2DataTableAdapter.Fill(Me.L2ReportDataset.Line2Data, DateAndTime1_Start.value, DateAndTime2_End.Value)

        Me.ReportViewer1.RefreshReport()
    End Sub

-----------------------------------------------
 
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
What error are you getting? Have ytou tried running the query directly to test? It seems like you need a GROUP BY clause for it since you are doing Avg()'s
leonstryker

Author

Commented:
leonstryker, Thanks for your response,

I have the Group By Shift, I just didn't include it in my post.
I think that my problem is within my vb sub.  

 I can make it work if I modify the code (SQL & VB) and use only one DateTimePicker on the report.  When I add the code for the second * DateTimePicker I get nothing. No data and  No errors.
 
 (*dates chosen with the dateTimePicker are valid in the db.)

Ok, if you think the problem is in the VB code then lets use some simple stuff to check what you are sending to the database. Instead of:
Me. Line2DataTableAdapter.Fill(Me.L2ReportDataset.Line2Data, DateAndTime1_Start.value, DateAndTime2_End.Value)
Assign values to variables and look at them before passing:
sLine2Data = Me.L2ReportDataset.Line2Data
sDateAndTime1_Start = DateAndTime1_Start.value
sDateAndTime2_End = DateAndTime2_End.Value
Debug.Print sLine2Data & "|" &  sDateAndTime1_Start & "|" &  sDateAndTime2_End
Me. Line2DataTableAdapter.Fill(sLine2Data , sDateAndTime1_Start, sDateAndTime2_End)
This should eliminate the obvious in anycase.
leonstryker
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What does the FILL code do with the parameters?

mlmcc

Author

Commented:
Sorry for the delay, multiple projects on my plate..

 I will Assign values to variables before passing and give it a test.  I will let you know asap.
thanks,
Jp

Author

Commented:
leonstryker,

Ok, That worked great.  I also moved the code to a button click event instead of using the DateTimePicker ValueChanged to refresh the report.
Thanks for your help!

Jp