Crystal Reports and parameterized query

  • Thread starter Thread starter Tony K
  • Start date Start date
T

Tony K

I have created a form to show data in a datagridview based on a start date
and end date. My query I use is for an Access DB.
Here is the query from my dataset designer:

SELECT [Inventory Transactions].TransactionDate, [Inventory
Transactions].TransactionDescription, [Inventory Transactions].UnitsSold,
Products.ProductIDNumber,
Products.ProductDescription, Suppliers.SupplierName
FROM (([Inventory Transactions] INNER JOIN
Products ON [Inventory Transactions].ProductID =
Products.ProductID) INNER JOIN
Suppliers ON Products.SupplierID =
Suppliers.SupplierID)
WHERE ([Inventory Transactions].TransactionDate >= ?) AND ([Inventory
Transactions].TransactionDate <= ?) AND ([Inventory Transactions].UnitsSold
0)
Click to expand...
ORDER BY Suppliers.SupplierName


Here is my subroutine from the form that fills the datagridview:

Private Sub ButtonSearchByDate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles ButtonSearchByDate.Click
Try
Me.UsageByDateTableAdapter.FillByUsageByDate(Me.Inventory_management_databaseDataSet.UsageByDate,
New System.Nullable(Of Date)(CType(DateTimePickerStart.Value, Date)), New
System.Nullable(Of Date)(CType(DateTimePickerEnd.Value, Date)))
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub


How/Where do I send these same two parameters to a crystal report?

Thanks in advance,
Tony K
 
Hi Tony,
Assuming you have set up your CR with a parameter called 'DateRange' that
accepts just that, a date range, you do something along the following lines.

Private Sub ConfigureCrystalReports()
MyCrystalReportViewer.ReportSource = myReportDocument
Dim myParameterFields As ParameterFields =
MyCrystalReportViewer.ParameterFieldInfo
SetDateRange(myParameterFields,
StartDateTimePicker.Value.ToShortDateString,
StopDateTimePicker.Value.ToShortDateString)
End Sub

Private Sub SetDateRange(ByVal myParameterFields As ParameterFields,
ByVal startDate As String, ByVal endDate As String)
Dim myParameterRangeValue As ParameterRangeValue = New
ParameterRangeValue()
myParameterRangeValue.StartValue = startDate
myParameterRangeValue.EndValue = endDate
myParameterRangeValue.LowerBoundType = RangeBoundType.BoundInclusive
myParameterRangeValue.UpperBoundType = RangeBoundType.BoundInclusive
Dim myParameterField As ParameterField =
myParameterFields("DateRange")
myParameterField.CurrentValues.Clear()
myParameterField.CurrentValues.Add(myParameterRangeValue)
End Sub

--
Terry


Tony K said:
I have created a form to show data in a datagridview based on a start date
and end date. My query I use is for an Access DB.
Here is the query from my dataset designer:

SELECT [Inventory Transactions].TransactionDate, [Inventory
Transactions].TransactionDescription, [Inventory Transactions].UnitsSold,
Products.ProductIDNumber,
Products.ProductDescription, Suppliers.SupplierName
FROM (([Inventory Transactions] INNER JOIN
Products ON [Inventory Transactions].ProductID =
Products.ProductID) INNER JOIN
Suppliers ON Products.SupplierID =
Suppliers.SupplierID)
WHERE ([Inventory Transactions].TransactionDate >= ?) AND ([Inventory
Transactions].TransactionDate <= ?) AND ([Inventory Transactions].UnitsSold
0)
Click to expand...
ORDER BY Suppliers.SupplierName


Here is my subroutine from the form that fills the datagridview:

Private Sub ButtonSearchByDate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles ButtonSearchByDate.Click
Try
Me.UsageByDateTableAdapter.FillByUsageByDate(Me.Inventory_management_databaseDataSet.UsageByDate,
New System.Nullable(Of Date)(CType(DateTimePickerStart.Value, Date)), New
System.Nullable(Of Date)(CType(DateTimePickerEnd.Value, Date)))
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub


How/Where do I send these same two parameters to a crystal report?

Thanks in advance,
Tony K
Click to expand...
 
Thank you Terry. I'll try it now.


Terry said:
Hi Tony,
Assuming you have set up your CR with a parameter called 'DateRange'
that
accepts just that, a date range, you do something along the following
lines.

Private Sub ConfigureCrystalReports()
MyCrystalReportViewer.ReportSource = myReportDocument
Dim myParameterFields As ParameterFields =
MyCrystalReportViewer.ParameterFieldInfo
SetDateRange(myParameterFields,
StartDateTimePicker.Value.ToShortDateString,
StopDateTimePicker.Value.ToShortDateString)
End Sub

Private Sub SetDateRange(ByVal myParameterFields As ParameterFields,
ByVal startDate As String, ByVal endDate As String)
Dim myParameterRangeValue As ParameterRangeValue = New
ParameterRangeValue()
myParameterRangeValue.StartValue = startDate
myParameterRangeValue.EndValue = endDate
myParameterRangeValue.LowerBoundType =
RangeBoundType.BoundInclusive
myParameterRangeValue.UpperBoundType =
RangeBoundType.BoundInclusive
Dim myParameterField As ParameterField =
myParameterFields("DateRange")
myParameterField.CurrentValues.Clear()
myParameterField.CurrentValues.Add(myParameterRangeValue)
End Sub

--
Terry


Tony K said:
I have created a form to show data in a datagridview based on a start
date
and end date. My query I use is for an Access DB.
Here is the query from my dataset designer:

SELECT [Inventory Transactions].TransactionDate, [Inventory
Transactions].TransactionDescription, [Inventory Transactions].UnitsSold,
Products.ProductIDNumber,
Products.ProductDescription,
Suppliers.SupplierName
FROM (([Inventory Transactions] INNER JOIN
Products ON [Inventory Transactions].ProductID =
Products.ProductID) INNER JOIN
Suppliers ON Products.SupplierID =
Suppliers.SupplierID)
WHERE ([Inventory Transactions].TransactionDate >= ?) AND
([Inventory
Transactions].TransactionDate <= ?) AND ([Inventory
Transactions].UnitsSold
0)
Click to expand...
ORDER BY Suppliers.SupplierName


Here is my subroutine from the form that fills the datagridview:

Private Sub ButtonSearchByDate_Click(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles ButtonSearchByDate.Click
Try

Me.UsageByDateTableAdapter.FillByUsageByDate(Me.Inventory_management_databaseDataSet.UsageByDate,
New System.Nullable(Of Date)(CType(DateTimePickerStart.Value, Date)), New
System.Nullable(Of Date)(CType(DateTimePickerEnd.Value, Date)))
Catch ex As System.Exception
System.Windows.Forms.MessageBox.Show(ex.Message)
End Try
End Sub


How/Where do I send these same two parameters to a crystal report?

Thanks in advance,
Tony K
Click to expand...
Click to expand...
 
Back
Top