473,837 Members | 1,656 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Passing null value to Crystal Reports parameter

PK
Hi, I have an application that opens a Crystal report document and
passes in a value to a parameter in the report (pointing to an Oracle
DB). However, if I want to pass a "null" value to retrieve all records
that have no data for the specified field that the parameter is a
condition of (ie. SELECT * from <table> where <field> is null"), it
returns nothing. I think CR is building the SQL statement like "SELECT
* from <table> where <field> = null". If I put in a value, it retrieve
records properly. Other than creating a stored procedure to handle the
null value and build my own SQL to fetch the records (this would be
problematic for me because I want to be able to pass in multiple values
for a parameter), is there anything I can do?

Here is my code:

paramFieldDef = oRpt.DataDefini tion.ParameterF ields[0];
ParameterDiscre teValue discreteValue = new ParameterDiscre teValue();
ParameterValues curValues = paramFieldDef.C urrentValues;

discreteValue.V alue = null;

curValues.Add(d iscreteValue);

Nov 17 '05 #1
2 19361
Try using this in your code:

discreteValue.V alue = DBValue.Null;

instead and see if it will work.

"PK" <ko*****@hotmai l.com> wrote in message
news:11******** **************@ l41g2000cwc.goo glegroups.com.. .
Hi, I have an application that opens a Crystal report document and
passes in a value to a parameter in the report (pointing to an Oracle
DB). However, if I want to pass a "null" value to retrieve all records
that have no data for the specified field that the parameter is a
condition of (ie. SELECT * from <table> where <field> is null"), it
returns nothing. I think CR is building the SQL statement like "SELECT
* from <table> where <field> = null". If I put in a value, it retrieve
records properly. Other than creating a stored procedure to handle the
null value and build my own SQL to fetch the records (this would be
problematic for me because I want to be able to pass in multiple values
for a parameter), is there anything I can do?

Here is my code:

paramFieldDef = oRpt.DataDefini tion.ParameterF ields[0];
ParameterDiscre teValue discreteValue = new ParameterDiscre teValue();
ParameterValues curValues = paramFieldDef.C urrentValues;

discreteValue.V alue = null;

curValues.Add(d iscreteValue);

Nov 17 '05 #2
PK
I have tried that, and I get this error when I try to assign it:

"Value does not fall within the expected range"

Now, this might get into something more Crystal specific, but I edited
the record selection formula in the report to the following:

if IsNull({?PARAME TER}) Then
(
// returns all records where TABLE.COLUMN_A is null OR
// TABLE.COLUMN_A matches the other parameter values
// in the array (if parameter accepts multiple values)

IsNull({TABLE.C OLUMN_A}) OR
{TABLE.COLUMN_A } = {?PARAMETER}
)
else
(
// returns all records where TABLE.COLUMN_A matches
// the parameter values in the array (if parameter
// accepts multiple values)

{TABLE.COLUMN_A } = {?PARAMETER}
)
This works when I pass in only 1 null parameter, but if I pass in an
array of parameter values (ie. 1, 2, null, 4, 5), the report only
returns records that meet the parameter values that were set AFTER the
null value was inserted. In the example above where the parameter
values are "1, 2, null, 4, 5", the report only retrieves records that
match values 4 and 5, ignoring 1, 2, and null.

for (int k = 0; k < strReportValue. Length; k++)
{
// discrete value
ParameterDiscre teValue discreteValue = new
ParameterDiscre teValue();
if (strReportValue[k] == "")
discreteValue.V alue = null;
else
discreteValue.V alue = strReportValue[k];

curValues.Add(d iscreteValue);
}

paramFieldDef.A pplyCurrentValu es(curValues);

Nov 17 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1354
by: cem marifet | last post by:
I am trying to pass in a value of null to a webservice parameter. I 've tried xsi:nil="true" attribute on the parameter tag. But it is not recognizing the value as null. Is there any thing else I need to specify? Any help would be greatly apreciated. here is part of soap message for the parameter going in to webservice <test xsi:nil="true" /></test>
5
3147
by: Jet | last post by:
Hi all, I had a code like the follow: class A{ ... public void get(int i,params OleDbParameter p) { if((p!=null)||(p.Length>0)) { // Do some work }
0
1041
by: aky | last post by:
Hi Im performing Parameter Fields Runtime Customization for crystal reports through VS.NET 2003, using C# as code Behind the ParameterFieldName property (and the other properties such as CurrentValues,MinimumValue,etc.) of the ParameterField Variable I declared is not available for selection in the intellisense...it's replaced by set_ParameterFieldName/get_ParameterFieldName...(set_CurrentValues, etc.). How can I correct this?
3
1257
by: Brian Henry | last post by:
How do you pass a null into a crystal reports parameter? I have been trying dbnull.value in vb.net and that does not work, just gives an error... thanks
1
11484
by: David Shorthouse | last post by:
Hey folks, I am attempting to pass null as the input value from a series of textboxes if the user does not input a value prior to submit. To try and do this, I am using a vbscript function on this asp as follows: CommentsAdd = IIf(Request.Form("Comments")="",NULL,Request.Form("Comments")) I was hoping this would convert those emptystring textboxes to null.
6
7825
by: Smish | last post by:
hey all, I am passing parameters through code to a stored procedure in both windows application (using c# and sql server & crystal reports)and web application(using asp.net & sql server & crystal reports)... the windows application works perfectly.. bt web apllication gives exception..."Invalid Table Number"... Can nebdy help...Please......
0
1274
by: paul | last post by:
Hello, I have recently upgraded to VB .net 2005 and a Crystal report that before did not use to prompt for its single parameter now does so. I have been writing the parameter value to the report in code. Code below Dim crReport As New rptIssue_Filtered() 'ParameterFieldDefinitions and ParameterFieldDefinition are off
0
1195
by: minnug | last post by:
Newbie I would like to pass four variables from one asp page to another asp page showing crystal reports. The crystal report would be embedded in the second page. I can use the previous page method or perhaps session variables to pass the variables: value1() value2() etc. My intention is to graph the variable values against their category names as a bar chart. e.g Category 1 value1() Category 2 value2() etc. How...
3
2132
by: Dan Aldean | last post by:
Hi, To create a Crystal Reports I use a ReportViewer. How can I pass parameters to the report which is based on a stored procedure. Please provide some code. Thanks
0
9685
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10886
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10580
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10634
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10277
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9413
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5674
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5853
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4479
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.