Solved

crystal report SQL command "no value given for one or more required parameters"

Posted on 2010-09-21
11
1,928 Views
Last Modified: 2012-05-10
This is driving me insane and I know the answer will be simple.

In Crystal Reports 11 (XI) I have a query that currently has parameters for StartDate and EndDate. I need to add further parameters, for example a numeric parameter called ClientID. After adding the new parameter to the command window the it does not prompt for it (only for the dates) and gives me the error "no value given for one or more required parameters".

The SQL is fine. The following works:

WHERE ordr_strt_d BETWEEN {?StartDate} And {?EndDate}
AND (Client.clnt_i = 1 or 1 = 0)

but this does not:

WHERE ordr_strt_d BETWEEN {?StartDate} And {?EndDate}
AND (Client.clnt_i = {?ClientID} or {?ClientID}  = 0)

Any ideas?
0
Comment
Question by:dbasplus
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 33730296
This is a stupid question, but when you edited the Command, did you add the parameter object in the right-hand pane of the command editor? If so, did  you then add the parameter into the SQL by double-clicking it from the right-hand pane?

Other issues to consider - what type of database and connection do you have?  I had a client a few months ago that was using DB2 with an ODBC connection.  Whenever I tried to modify a Command to add a new parameter, Crystal Reports would crash.  I had to jump through hoops in order to get it to work. We were able to eventually resolve the issue by installing a service pack.

~Kurt
0
 

Author Comment

by:dbasplus
ID: 33730326
Hi Kurt, don't be worried about asking stupid questions, I'm sure this will need a stupid answer. The parameter was added on the right hand side and then double-clicked to add it into the command window.
The connection to the database seems to be ok. It's and ADO connection to my SQL 2010 database and I have all the latest updates I can find.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 33730707
Do you have access to an Enterprise Repository?  If so, you might try the following:

1) Create a blank report with the command as you want it
2)  Upload command to the repository
3)  Go to the original report and set location from the old command to the new command in the repository
4)  Disconnect the new command in the original report from the repository so it stands alone within the report

Those are the steps I had to take in order to get my command changes to work before I upgraded the service pack.

~Kurt
0
On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

 

Author Comment

by:dbasplus
ID: 33730815
Hi Kurt,
This is simple project and I only have simple reports, thus do not have a Repository.

When I create a new report the commands' parameters are added to reports' parameter fields. I would have thought that if I update the command with new parameters the same would happen.

If I delete the command, to create a new one, it removes all the reports fields and I will have to go and reformat (recreate) the report. This seems a little over the top.

Maybe this is just a bug in Crystal.
0
 
LVL 4

Expert Comment

by:msd1305
ID: 33732789
Most probable reason could be that your report and database connections are not getting  refreshed properly. Try the following:
1) Make sure that you dont have 'Save data with report' option selected in File menu.
2) Click on Database->Log on or off server and log off all the existing connection.
3) Make sure once again about the parameter in the command whether you have added it correctly.
4) run the report and select 'Prompt for new parameters'
5) if you have any subreports then make sure you are passing correct parameters to it.
6) If nothing from the above mentioned works then please attached the report if possible and we can investigate.

Hope this helps! :)

0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 33738747
I am guessing 0 is what you use to return all clients.

I have achieved this without issue in the past using the following logic, which looks very similar to yours! The only difference is I do the check for "return all" first.

Maybe try switching the order of your OR. Also you can try doing a string check like I am below (adding ' ' where applicable).

and ('{?pProgrammer}' = '* ALL Programmers'
        OR table.field = '{?pProgrammer}')
0
 
LVL 14

Expert Comment

by:LinInDenver
ID: 33738769
Oh - also on the part where if you remove the command it deletes all fields... A good tip is to actually display only formula fields in your report design.

A formula can simply contain just a database field (no logic required), and then dropped onto the landscape.

When a table or command is removed from a report designed this way, the formula fields remain in your design, but they just error out. This gives you a chance to add a new command with the same as the old command, or re-point/fix formulas.

I hope this makes sense. :-) It has saved me a lot of frustration.
0
 

Author Comment

by:dbasplus
ID: 33739870
Apologies for any delay in replying, I'm in Australia.

Update. I have:
reinstalled Crystal Reports XI and SP4.
Tried rearranging the order of the OR and making it do a string compare.
Checked the database status (logon / log off etc) and report options.
Played around with adding and deleting various parameters.

All to no avail.

I have noticed that if I add new parameters, other than 'ClientID' for example 'ZifoidID' they work. It just won't let me have 'ClientID' even if I rename 'ZifoidID' to 'ClientID'.

Attached is the report file.
Orders.rpt
0
 
LVL 14

Accepted Solution

by:
LinInDenver earned 500 total points
ID: 33740319
i wonder if ClientID is some kind of reserved word in your database? I've never heard of such a thing, but you never know.

I guess you can implement a new standard (which we use) - if it is a parameter, it starts with p_.

p_ClientID, p_StartDate, p_EndDate!
0
 

Author Comment

by:dbasplus
ID: 33749878
OK, for now I have taken the suggestion to just rename my input parameters. This makes it work but I still think there is a bug with this. I have other reports that use 'ClientID'  and are fine. Something has been stored by the report that has corrupted the file.
Thanks for all your help guys.
0
 

Author Closing Comment

by:dbasplus
ID: 33749894
This is a work-around rather than a solution.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question