Solved

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

Posted on 2010-09-21
11
1,899 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 

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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

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 …
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

815 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now