I developed a Crystal Report viewer using developer studio .NET. It works for many reports but not this one. when I enter @startdate and click for next parameter, the message "please enter a datetime value" appeared. it did not say where it was generated.
any idea, anyone? thanks for helping and here is the stored proc behind the report I am running.
**********************************************************
**********************************************************
CREATE PROC dbo.com_rpt_clm0004_sp
@StartDate DATETIME = NULL
, @EndDate DATETIME = NULL
, @RangeType varchar(30) = NULL
,@StartRange int = NULL
,@EndRange int = NULL
AS
SET NOCOUNT ON
IF (@RangeType = 'scanned_date' )
BEGIN
EXEC('
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM hcfa_fifteenhundred_sv f
WHERE scanned_date BETWEEN '''+@StartDate+''' AND '''+@EndDate+'''
UNION ALL
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM ub_ninetytwo_sv u
WHERE scanned_date BETWEEN '''+@StartDate+ ''' AND '''+@EndDate+'''
')
END
IF (@RangeType = 'r_creation_date')
BEGIN
EXEC('
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM hcfa_fifteenhundred_sv f
WHERE r_creation_date BETWEEN '''+@StartDate+''' AND '''+@EndDate+'''
UNION ALL
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM ub_ninetytwo_sv u
WHERE r_creation_date BETWEEN '''+@StartDate+ ''' AND '''+@EndDate+'''
')
END
IF (@RangeType = 'teleform_batch_number')
BEGIN
EXEC('
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM hcfa_fifteenhundred_sv f
WHERE teleform_batch_number BETWEEN '''+@StartRange+''' AND '''+@EndRange+'''
UNION ALL
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM ub_ninetytwo_sv u
WHERE teleform_batch_number BETWEEN '''+@StartRange+ ''' AND '''+@EndRange+'''
')
END
GO
any idea, anyone? thanks for helping and here is the stored proc behind the report I am running.
**********************************************************
**********************************************************
CREATE PROC dbo.com_rpt_clm0004_sp
@StartDate DATETIME = NULL
, @EndDate DATETIME = NULL
, @RangeType varchar(30) = NULL
,@StartRange int = NULL
,@EndRange int = NULL
AS
SET NOCOUNT ON
IF (@RangeType = 'scanned_date' )
BEGIN
EXEC('
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM hcfa_fifteenhundred_sv f
WHERE scanned_date BETWEEN '''+@StartDate+''' AND '''+@EndDate+'''
UNION ALL
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM ub_ninetytwo_sv u
WHERE scanned_date BETWEEN '''+@StartDate+ ''' AND '''+@EndDate+'''
')
END
IF (@RangeType = 'r_creation_date')
BEGIN
EXEC('
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM hcfa_fifteenhundred_sv f
WHERE r_creation_date BETWEEN '''+@StartDate+''' AND '''+@EndDate+'''
UNION ALL
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM ub_ninetytwo_sv u
WHERE r_creation_date BETWEEN '''+@StartDate+ ''' AND '''+@EndDate+'''
')
END
IF (@RangeType = 'teleform_batch_number')
BEGIN
EXEC('
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM hcfa_fifteenhundred_sv f
WHERE teleform_batch_number BETWEEN '''+@StartRange+''' AND '''+@EndRange+'''
UNION ALL
SELECT line_of_business
,form_type
,teleform_batch_number
,document_control_number
,scanned_date
,receive_date
,r_creation_date
FROM ub_ninetytwo_sv u
WHERE teleform_batch_number BETWEEN '''+@StartRange+ ''' AND '''+@EndRange+'''
')
END
GO