Solved

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

Posted on 2010-09-21
11
1,840 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
 

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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…
This video discusses moving either the default database or any database to a new volume.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 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

22 Experts available now in Live!

Get 1:1 Help Now