Parameter for Command Object disappearing 2

[MrBillSC]

Sql Server, Crystal vers 9.0

I am new to the Add Command function of Crystal reports. I can add a command ok and it seem to return correct results. However, when I try to add a parameter to the command object, the parameter disappears as soon as I click "OK" out of the "Command Parameter and "Add Command To Report" dialogue boxes. When I go back in to edit the command object, the parameter is gone. The steps for adding the parameter are so simple I can't imagine what I am doing wrong. Is this a bug with a fix?

Any help with this would be appreciated.

MrBill

 

[lbass]

Can you explain a little more about how you added the parameter? Once created, did you then add it into the query on the left?

Also, when you say okay to leave the command query screen, the parameter screen pops up. You must respond to this (don't cancel) in order to save the changes to the query.

-LB

 

[MrBillSC]

When the "Add Command To Report" window opened I created the sql query in the "Enter SQL query..." box.

Then I clicked the "Create" button in the "Parameter List" window. The "Command Parameter" window opened and I filled in all the fields:

Parameter Name
Prompting Text
Value Type
Default Value

Then I clicked the "OK" button in the "Command Parameter" window. This returned me to the "Add Command To Report" window. My new parameter was visable in the "Parameter List" box.

Then I clicked the "OK" button in the "Add Command To Report" window,which then closed the window. At this point the parameter seems to have disappeared. If I run the report the prompt does not appear. If I reopen the command, the parameter does not appear in the "Parameter List" box.

MrBill

 

[Turkbear]

Hi,
Unless you actually use the Command Object's parameter in the Sql code of the command, it will not be prompted for or retained .



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

 

[lbass]

After creating the parameter, you have to add it into the where clause of the query. Did you do that? If it is a string, you should enclose it in sinqle quotes.

-LB

 

[MrBillSC]

Thank you Turkbear and lbass. I did not add to the query. I will give this a try. I'm sure that is the problem.

MrBill

 

[MrBillSC]

I can't seem to get the parameter into the query proprely.
Here is the query (ParmStatus is the parameter field):

select ESYAPPTR.POS_ID, ESYAPPTR.APPOINT_ID, ESYAPPTR.STATUS

from ESYAPPTR

/* Select only records where status = parameter value. */

where ESYAPPTR.STATUS = ParmStatus

/* Group By position and appointment. */

group by pos_id, appoint_id, status

When I click "OK" in the "Add Command To Report" window I get the following error message:

Failed to open a rowset.
Details: 42522[microsoft][ODBC sql server driver][sql server]Invalid column name 'ParmStatus'.

Then my parameter called 'ParmStatus' disappears from the "Parameter List" box.
I know the query is sensitive to single vs. double quotes and our setup accepts single quotes for values and errs out with same error message if double quotes are used. Is it possible the string parameter is trying to insert double quotes?

Any light anyone can shed would be appreciated.

MrBill

 

[MrBillSC]

Never mind. I discovered I needed brackets and '?' etc for the parameter to be recognized.

Thanks all for your help.

MrBill

 

[lbass]

After creating the parameter in the command screen, you would put the cursor in the position where you want to add the parameter, and then double-click on the newly created parameter.

-LB