?
Solved

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

Posted on 2010-09-21
11
Medium Priority
?
2,026 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

 

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 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month8 days, 3 hours left to enroll

765 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