Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Command w/ parameter does not return correct data Post Reply Post New Topic
Author Message
dkeyzer
Newbie
Newbie


Joined: 12 Aug 2009
Location: United States
Online Status: Offline
Posts: 2
Quote dkeyzer Replybullet Topic: Command w/ parameter does not return correct data
    Posted: 12 Aug 2009 at 10:43am
I just ran into a rather weird problem with CR 2008.  If I add a Command with sql syntax using date parameters the command does not return the same data as when I add the command using the same values but entered statically. 

Essentially I have written a simple SQL query using the Server Management Studio and verified that it is returning the correct data.  If I transfer this to CR by selecting "Add a Command" in database expert it returns the correct data.  But then, if I replace the two static date values in the query with parameters and give these parameters the exact same value as they had in the static query the report returns incorrect data.  I can even then go to "Show SQL" copy the text back to Studio, replace the parameter names with the values I entered in CR and it returns correct data.  I don't get what CR is changing when it actually runs the SQL string.  I am not linking the Command with any table or any other command and am not doing any additional filtering or sorting, I am just printing the output (a single column) to the report.  Has anyone experienced anything like this before?
IP IP Logged
dkeyzer
Newbie
Newbie


Joined: 12 Aug 2009
Location: United States
Online Status: Offline
Posts: 2
Quote dkeyzer Replybullet Posted: 12 Aug 2009 at 10:45am
By the way, the SQL string I am using is:

SELECT DISTINCT PatientFullName
FROM PatientOrders
WHERE ServiceMasterName IN ('...','...','etc')
AND StartDate_Local<'7/31/2009 11:59:11'
AND (StopDate_Local>'7/01/2009 11:59:11' OR StopDate_Local Is Null) ORDER BY PatientFullName

And with parameters (returns incorrect data):

SELECT DISTINCT PatientFullName
FROM PatientOrders
WHERE ServiceMasterName IN ('...','...','etc')
AND StartDate_Local<{?Date Maximum}
AND (StopDate_Local>{?Date Minimum} OR StopDate_Local Is Null) ORDER BY PatientFullName
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 12 Aug 2009 at 11:29am
Don't use paremeters in Command statememts.
If you want to use the params at the DB level, uses a stored procedure, define the params there and they will be created in crystal automaticlly (although you will have to use a DATETIME field ratehr than a Date field).
Otherwise make your COMMAND exclude the date filter and add that in as part of your select stamtement in crystal while using crystal built parameters in that select statement.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.016 seconds.