Prompt users for SQL parameters in command object?

[ericb123]

Hello, I inherited a SQL query (SQL 2005, CR11) where the SQL is in a Crystal command object. There are 2 declared parameters in the SQL: @date1 & @date2 for the date parameter in the where clause.

When I add the SQL to the command window, I can enter parameters, but it doesn't seem to store them or let me use them in my report. I'm trying to be able to prompt the user to enter these date values.

I added 2 parameters to my SQL: @date1 & @date2. It prompts me in the beginning, but it's not returning any records.

Any help is greatly apreciated, thanks!

 

[Madawc]

It ought to work, I've done similar things. Maybe the SQL command is wrong. Please post what you have.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

Are you copying the SQL query from somewhere else and pasting it into the command area? I would try deleting the current parameters and creating the parameters within the command and then plugging the new parameters {?date1} and {?Date2} into the query.

-LB

 

[ericb123]

When I run the SQL in SQL Query Analyzer, it runs fine:

(it's too long to put here, multiple temp tables and unions, etc).

Declare @Date1 datetime
Declare @Date2 datetime

Set @Date1 = '03/01/2008'
Set @Date2 = '04/30/2008'

then in the SQL, there's:

where at.closeddate between @Date1 and @Date2

So all I did was replace @Date1 and @Date2 with parameters I created in the query window: {?StartDate} and {?EndDate}

But when I do this, Crystal times out and closes. Running it in the SQL Query window takes about 20-30 seconds.

 

[Turkbear]

Hi,
You can probably remove this part:

Declare @Date1 datetime
Declare @Date2 datetime

Set @Date1 = '03/01/2008'
Set @Date2 = '04/30/2008'

Just create the 2 parameters
{?StartDate}
and
{?EndDate}

In the Command window and use them in the where clause..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ericb123]

Ok, thanks. I added just the 2 parameters in the command window. Then in my where clause I added them.

This works, however, when Crystal prompts me to enter the parameters, for each parameter, there's 2 entries. One is just a drop-down box with 2 options: The first is "...", then 2nd is the default date value in the parameter I set.

Then, directly under that is another box with a date picker for entering a date.

So there's 4 inputs for 2 date parameters. Am I doing something wrong here? When I put the paramters into my SQL, I'm putting them in as: {?StartDate} with the brackets and question mark.

 

[Turkbear]

Hi,
Be sure that you have not created parameters in the report in addition to the ones created as the command object's parameters...







To Paraphrase:"The Help you get is proportional to the Help you give.."