passing or defaulting null

I wanted to know what are the advantages/divantages of passing null value
s
in the sp as oppose to setting them as default in the tables.
I am using SQL 2005, "set ANSI_NULLS ON".
ThanksMumbai_Chef wrote:
> I wanted to know what are the advantages/divantages of passing
> null values in the sp as oppose to setting them as default in the
> tables.
> I am using SQL 2005, "set ANSI_NULLS ON".
>
If you have, for example, a DateEntered column, which will always need to be
initialized to the current date and time when a row is inserted and never
subsequently updated, then by all means create a default constraint for the
column. This allows you to never even have to mention the column in any
UPDATE/INSERT DML queries, and as a result, you never have to declare a
parameter for this columns value in any of your procedures.
On the other hand, if you have a column for which you will sometimes be
providing a non-default value, then obviously you will need to include a
parmaeter for that value in your stored procedures. Whether or not you
declare the parameter with a default value (making it an optional parameter)
is totally up to you.
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Passing NULL-value into package variables

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

I don't think you can pass nulls through the commandline dtexec or dtexecui simply because a DBNull is an object. One option you can pursue is to pass it using a console app written in VB or C#.

This link shows one of the ways of doing that.|||

Dennis_v_E wrote:

We have a package with a package variable.
This variable is of data-type 'DateTime'.
However, when i try to pass the value 'NULL' the package fails... i use the following statement with 'dtexec.exe'

/SET \Package.Variables[MyDate].Value;"NULL"

What's the correct syntax for passing null-values? But maybe (because i cannot find anything on this) i should ask if this is even possible...

Its not possible.

One way around it may be to have a boolean variable called IsDatetimeNull which you set to TRUE or FALSE from the command-line.

Then, you put an expression on your datetime variable which sets it to NULL(DT_DBTIMESTAMP) if IsDatetimeNull==TRUE.

Something like that anyway. You get the idea.

-Jamie

|||

Thanx,

To bad it cannot be done simple. But i get the idea.
Maybe i put a feature request in Connect.

Dennis

sql

Passing null value parameter to a data-driven subscription

I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.

When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.

I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.

Has anyone else experienced this issue and do you have a resolution?

Thanks in advance for your assistance.

I am having the same issue, please let me know if you have found a resolution.

Thank you,

Dave

|||

I had the same problem. In another thread someone mentioned that there is a problem with null parameters and data-driven subscriptions. It seems like a major bug in reporting services. My workaround was to use something other than null and have the report query recognize this setting as equivalent to null e.g. if it's a varchar field then set to 'NOTHING' and have the report definition query treat 'NOTHING' as NULL. For a uniqueidentifer field I used a guid with all zeroes.

Hope that helps.

Regards,

Greg

Passing null value parameter to a data-driven subscription

I have a data-driven subscription (DDS) stored procedure that gets called when a subscription fires and returns parameters to a report. One of the parameter values I want to return is NULL, which also happens to be the default value of the corresponding report parameter.

When I set the report parameter to use the default value (NULL) when setting up the subscription in Report Manager, the subscription works fine. When I try to pass the NULL value from the DDS proc to the report, the subscription fails.

I do not know what the error logs say. I've rarely looked at them and what I have seen seems a bit cryptic. I've narrowed down through trial-and-error that passing the NULL value is the issue.

Has anyone else experienced this issue and do you have a resolution?

Thanks in advance for your assistance.

I am having the same issue, please let me know if you have found a resolution.

Thank you,

Dave

Passing NULL value not working

I currently have a stored procedure that looks something like this

SELECT * FROM tblQuestions WHERE Title LIKE ISNULL('%'+@.Name+'%', Title)

I have a form that supplies this value. This statement should make it so that if a NULL value is passed in, then it will return all the rows, if some text is specified, then it will not. On my SQLDataSource on the page where the parameter is supplied I have set ConvertEmptyStringsToNull to True, but when I say in my code,

SqlDataSource1.SelectParameters.Add("@.Name", TextBox1.Text);

It won't give me back any of the rows, I know that the stored procedure works fine because I can get it to work by a basic query and other testing on it, so somewhere in my form, the NULL value isn't being passed, I belive that it is passing an empty string and I don't know why. Thank you in advance

/jcarver

Try this:

WHERE ([Title] Like '%' + @.Name + '%') or ( @.Name is NULL)"

And add this to your SqlDatasource: CancelSelectOnNullParameter="False"

|||

The two where clauses are logically equivalent.

I'm a bit curious why you have a column called title and a parameter called @.name instead of @.title.

As for passing in a null, you could check the length of the textbox text, and if it's 0, send DBValue instead of the Text property.

|||

Try

IF @.Name IS NULL SET @.Name = ''

IF DATALENGTH(@.Name) > 0
SELECT * FROM tblQuestions WHERE Title LIKE '%'+@.Name+'%'
ELSE
SELECT * FROM tblQuestions

|||

SELECT * FROM tblQuestions WHERE Title LIKE '%' + @.Name + '%'

ConvertEmptyStringToNull must be set to False!

sql

Passing Null to Stored Procedure in Reporting Services

Hello,

I have a report. I need to make a drop-down parameter optional. I've setup the parameter to use a dataset as its source, and I need to allow the value to be null. I've selected allow nulls check box, and I've even tried setting the default value to System.DBNull.Value. But, when the entry is not selected (blank), no data ever comes back. If the data is blank or null, my procedure works (I've tested it). How do I send a null value to the database through reporting services report? I was hoping to avoid creating a special query just for that purpose.

Thanks.

Since you say that it is Blank, the report is passing a blank '', not a null. In your proc, you could have the statement:

if @.Parm = ''

Begin

Select @.Parm = null

End

In your parameter setup, uncheck the Allow Blank, and select the Allow Nulls...

Then, when running the report, when the NULL check box in the parameter panel is checked, a NULL will be passed to the proc.

hth

BobP

|||

Hello,

I have Allow Nulls checked. I do have that safeguard in my procedure. I don't have the option for the null checkbox in the parameter panel though. I am sure I have Allow Nulls checked in my application. How does that get added to the panel?

Thanks.

|||

You might want to consider modifying the stored procedure to default the parameter in question. You can set up the last (or last few) arguments of a stored procedure to be optional by providing a default value for the argument. For example, if you have a stored procedure defined as:

Code Snippet

create dbo.myProc

( @.anArgument varchar(20) = null

)

as

...

Since you have provided a default value to your parameter (as designated in red), you are no longer required to pass this argument to your stored procedure. Sometimes this can help.

Kent

|||

The null check box only gets added when you check the Allow Nulls box.

Also make sure you uncheck the Allow Blanks box.

BobP

|||

Hello,

I have a default value set to null for my stored procedure. I have allow nulls checked. I have allow blanks unchecked. I have verified my stored procedure and it works with nulls and blanks (SP converts blanks to null), and I have run the stored procedure in the RS designer and it works. I am sure of that... But, no matter what, I do not get a nulls checkbox in the parameter area...

Any ideas?

|||

BobP,

I do have Allow Nulls checked, and still it does not show.

Any ideas?

|||What version of SSRS are you using? 2000 or 2005, and which SP?|||

For some reason, SSRS doesn't seem to support "allow NULL" option for drop-down query-based parameters. You are always required to pick one of the parameter values to proceed with the report.

I created a small test report based on an SP that accepts one optional parameter of type INT. I was able to configure optional drop-down parameter for this report by creating a following dataset for parameter query:

Code Snippet

SELECT NULL party_id, '<all customers>' party_name

UNION ALL
SELECT party_id, party_name FROM party ORDER BY party_name

This will add "<all customers>" option to the list of parameters values and return NULL to the SP if this option is selected.

Hope this helps.

Denis

|||Reporting Services 2000.|||

Hey,

I was hoping not to have to do that, but your right I believe that is the only option.

Thanks.

Passing NULL to DataSet parmameter

Hey All,

I have a number Stored Procs that have been around for a while that pull the entire list, or if I pass an ID, will just the record for that ID like below.

I want to be able to use these querries to poplate Multi-Select parameter dropdowns. going to the Data tab and creating a new dataset, I put in the SP name, and close the window. I then go to the Red ! point to preview the data it prompts me for my ID parmaeter on my SP. In the dropdown list it has '<Null>' (no ' though). When I run it, it works fine and returns all of my records.

When I run the report, it errors saying I didn't pass a parm for ID. I go back to the data tab, and edit my DataSet hitting the elipse. I go to the 3 tab called parameters, and type the following I get the following errors:

@.ID = <Null> - ERROR - [BC30201] Expression expected

@.ID= - ERROR - Return statement in function must return a value

@.ID = Null --ERROR - Null constant not supported use System.DBNull instead

@.ID = System.DBNull -ERROR - [BCS30691] DBNull is a type in System and cannot be used in an expression

@.ID=System.DBNull.Value NO ERROR, but it does not return anything either. I also did a SQL Trace, and I can see that it doesn't even send the querry to the database.

Does anyone know another magic value I can pass to get this to work?

I am being a little stuborn, I know that I could just create new procs, and wrap up the null, but the more stuff you create the more you have to maintain, so I would prefer to reuse these.

Thanks in advance.

Eric Wild

PS: My company is moving from crystal reports to Reporting service, and Reporting services is Rocks! It is very intuitve, simple and straign forward. The formatting is easy with the table and the matrix control blows away the crosstab control in crystal. Also, I'm finding that because crystal was so un extendable, that I would spend hours shaping sql to get over it's blemishes, and hours shaping it in the report, only to sometimes reliaze that the proposed onetime hack wouldn't work, and have to start all over! So far with RSS any tips and tricks I have learned can very easily be applied to any report I work on! Aslo, I do mostly interanet web apps, and it is nice to dump my reports on the Report Server, and not worry about haing to create a web page, create a datasource and all the ansilary stuff to go along with it. The only thing I don't like is the name 'Roporting Services': It does not stick out too far in Google Searches like 'AJAX.NET' or 'ASP.NET'. Anyway kudoes to the Reporting Services team!

ALTER PROC [dbo].[spGetLaborRole]
@.ID INT = NULL
AS
BEGIN
SELECT ID, Descr
FROM dbo.LaborRole
WHERE ( (ID = @.ID) OR (@.ID IS NULL) )

Hello Eric,

Can you verify that in your report parameter definition (Report menu --> Report Parameters), the 'Allow null values' checkbox is selected for your ID parameter?

Jarret

|||

Jarret,

That worked!

I guess I didn't see them as being related. I think of report parameters as things that communicate with the ouside world, and not related to my internl querrires. I wouldn't want a prompt to the end user showing ID: NULL to run the report. I can see though there is a hidden check so it not for end users. Cool thanks!

Here is steps on how to fix this.

1) go to the data tab and select the elipse. select the parameters tab and delete the @.ID=... stuff I put in and close Window.

2) Go to the Layout tab, and from the menu select Report/Report Parameters...

3) a new Parameter is in the list to the left callled ID.

-Check Allow Nulls

- Check Hidden

-Verify Default Value NULL is bubbled in below.

Thanks again

Eic Wild

passing null reportparameter values

Hi,
Iâ'm using the report viewer object to view a report which works fine until I
need to pass null values. When I use the following code I get â'The
'reportId' parameter is missing a valueâ':
ReportViewer1.ShowParameterPrompts = false;
ReportParameter[] parameters = new ReportParameter[2];
parameters[0] = new ReportParameter("reportId");
parameters[1] = new ReportParameter("userid", "123");
ReportViewer1.ServerReport.SetParameters(parameters);
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Remote;
ReportViewer1.ServerReport.Refresh();
The reportId is setup for null values, however it still gives me an error:
<ReportParameter Name="reportId">
<DataType>Integer</DataType>
<Nullable>true</Nullable>
<Prompt>reportId</Prompt>
</ReportParameter>
I know other people have had this problem but using â'new
ReportParameter("reportId")â' doesnâ't work for me.
Thanks in advance!I'm guessing here, but do you need to explicitly set the value of the
null parameter. DBNull isn't the same as .Net C# null.
Regards, Rhys
On Sep 21, 11:50 pm, Nathan <nathan.et...@.online.nospam> wrote:
> Hi,
> I'm using the report viewer object to view a report which works fine until I
> need to pass null values. When I use the following code I get "The
> 'reportId' parameter is missing a value":
> ReportViewer1.ShowParameterPrompts = false;
> ReportParameter[] parameters = new ReportParameter[2];
> parameters[0] = new ReportParameter("reportId");
> parameters[1] = new ReportParameter("userid", "123");
> ReportViewer1.ServerReport.SetParameters(parameters);
> ReportViewer1.ProcessingMode => Microsoft.Reporting.WebForms.ProcessingMode.Remote;
> ReportViewer1.ServerReport.Refresh();
> The reportId is setup for null values, however it still gives me an error:
> <ReportParameter Name="reportId">
> <DataType>Integer</DataType>
> <Nullable>true</Nullable>
> <Prompt>reportId</Prompt>
> </ReportParameter>
> I know other people have had this problem but using "new
> ReportParameter("reportId")" doesn't work for me.
> Thanks in advance!

passing null report parameter via a url

Hi,
I have a SP in sql with parameters that can get null values such as:
@.paramguf tinyint=null,
AS
select 1 as kodKibuz,name
from learningstuds
where
(@.paramguf is null or hasamaguf=@.paramguf)
I am sending the report parameters via vb.net
Dim URL As String
URL = "http://localhost/reportserver/reports/tutorials?%2fTutorials%2fKibuzKlita1"
URL += "&rs:Command=render&rc:Parameters=false&rc:Toolbar=false"
URL += "¶mdate=" + Date32.saveDate().ToShortDateString
URL += "¶mguf=" + ddlGuf.SelectedValue
'Pass the URL as a Web request
'Dim request As HttpWebRequest = WebRequest.Create(URL)
Response.Redirect(URL)
How can I send a null value to the report for paramguf?
Thanksquoting KB article http://support.microsoft.com/kb/842853
You can also pass a null value for a parameter to a report by adding
'param:isnull=true' to the URL. For example, if the SalesOrderNumber is a
Null-valued report parameter that must be passed to the report, add the
'SalesOrderNumber:isnull=true' string to the URL."
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"collie" <collie@.discussions.microsoft.com> wrote in message
news:72BB20CA-9B8F-48F6-A3C8-6CCA1049EBE6@.microsoft.com...
> Hi,
> I have a SP in sql with parameters that can get null values such as:
> @.paramguf tinyint=null,
> AS
> select 1 as kodKibuz,name
> from learningstuds
> where
> (@.paramguf is null or hasamaguf=@.paramguf)
> I am sending the report parameters via vb.net
> Dim URL As String
> URL => "http://localhost/reportserver/reports/tutorials?%2fTutorials%2fKibuzKlita1"
> URL += "&rs:Command=render&rc:Parameters=false&rc:Toolbar=false"
> URL += "¶mdate=" + Date32.saveDate().ToShortDateString
> URL += "¶mguf=" + ddlGuf.SelectedValue
> 'Pass the URL as a Web request
> 'Dim request As HttpWebRequest = WebRequest.Create(URL)
> Response.Redirect(URL)
>
> How can I send a null value to the report for paramguf?
> Thanks
>

Passing null parameters via Query-String

I'm trying to call a report, passing my parameters via query-string...
I have one parameter that can receive null values...
Following Books Online, the syntax for null values is :isnull, giving the following sample of URL:
http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
In my report, I shadowed this syntax, it looks like the following:
http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
If I pass a common value to func_cd_matricula, like 1 or 2, it works fine, but with this isnull syntax, it gives me the following error:
"The path of the item '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The full path must be less than 260 characters long, must start with slash; other restrictions apply. Check the documentation for complete set of restrictions."
Does anybody know why this?
Thanks,
Rafa®BOL is incorrect.
Right syntax is
sales:isNull=true
Lev
http://blogs.msdn.com/levs
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rafa®" <Rafa®@.discussions.microsoft.com> wrote in message
news:4308595B-5A3C-4F4D-ADAD-D9288B71B926@.microsoft.com...
> I'm trying to call a report, passing my parameters via query-string...
> I have one parameter that can receive null values...
> Following Books Online, the syntax for null values is :isnull, giving the
> following sample of URL:
> http://exampleWebServerName/reportserver?/foldercontainingreports/orders&division=mailorder®ion=west&sales:isnull
> In my report, I shadowed this syntax, it looks like the following:
> http://localhost/ReportServer?/ctr_es_reports/entrada_saida&dh_ini=2004-06-01&dh_fim=2004-07-20&func_cd_matricula:isnull
> If I pass a common value to func_cd_matricula, like 1 or 2, it works fine,
> but with this isnull syntax, it gives me the following error:
> "The path of the item
> '/ctr_es_reports/entrada_saida,func_cd_matricula:isnull' is not valid. The
> full path must be less than 260 characters long, must start with slash;
> other restrictions apply. Check the documentation for complete set of
> restrictions."
> Does anybody know why this?
> Thanks,
> Rafa®sql

Passing null parameters to subreports

This is similar to Carlos' question on 7/7/05 that nobody responded to.
Please respond.
(http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
a Parent report, I setup a hyperlink to jump to another report that accepts a
number of parameters (some of the parameters are defined as nullable in the
parent and the calling report). However when I try to preview the report and
click on the hyperlink cell passing parameters that are null in addition to
parameters that are populated, the subreport seems to hang and wait for
input. I tried so many variations of this and it just does't work. It only
works if all the parameters are populated, but that is not realistic.
Can someone please suggest a solution ASAP? This looks like a bug to me.
This is not rocket science here.
Thanks.
--
-RB
:)I had this problem too....
The solution is to manually change the .rdl file with the <Omit> element
under the
<Parameter> element...
Please check
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
for more details...
"capricorn" wrote:
> This is similar to Carlos' question on 7/7/05 that nobody responded to.
> Please respond.
> (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> a Parent report, I setup a hyperlink to jump to another report that accepts a
> number of parameters (some of the parameters are defined as nullable in the
> parent and the calling report). However when I try to preview the report and
> click on the hyperlink cell passing parameters that are null in addition to
> parameters that are populated, the subreport seems to hang and wait for
> input. I tried so many variations of this and it just does't work. It only
> works if all the parameters are populated, but that is not realistic.
> Can someone please suggest a solution ASAP? This looks like a bug to me.
> This is not rocket science here.
> Thanks.
> --
> -RB
> :)|||Hi Trisha.
Thank you for responding. It worked like a charm. What a relief.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||I also noticed that after I changed the .RDL file and go back into report
designer and save the report again for some other change, all my omit
statements are wiped out. Is there any way to stop this from happening?
Thanks.
--
-RB
:)
"Trisha" wrote:
> I had this problem too....
> The solution is to manually change the .rdl file with the <Omit> element
> under the
> <Parameter> element...
> Please check
> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> for more details...
>
> "capricorn" wrote:
> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > Please respond.
> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >
> > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > number of parameters (some of the parameters are defined as nullable in the
> > parent and the calling report). However when I try to preview the report and
> > click on the hyperlink cell passing parameters that are null in addition to
> > parameters that are populated, the subreport seems to hang and wait for
> > input. I tried so many variations of this and it just does't work. It only
> > works if all the parameters are populated, but that is not realistic.
> >
> > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > This is not rocket science here.
> >
> > Thanks.
> > --
> > -RB
> > :)|||No, there is not. RS designer rewrites the RDL.
Something to try which I would like to hear if you are successfull is to map
the subreport parameter to an expression and put this in for the expression:
= Nothing
I think that might work for you but I am not sure since I have never needed
to do this myself. Let me know if it works. It would be easier than messing
with the RDL.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"capricorn" <capricorn@.discussions.microsoft.com> wrote in message
news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable in
>> > the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)|||=Nothing will work for subreport and drillthrough report parameters.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:O9bbI%23HtFHA.3236@.TK2MSFTNGP09.phx.gbl...
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to
> map the subreport parameter to an expression and put this in for the
> expression:
> = Nothing
> I think that might work for you but I am not sure since I have never
> needed to do this myself. Let me know if it works. It would be easier than
> messing with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
>>I also noticed that after I changed the .RDL file and go back into report
>> designer and save the report again for some other change, all my omit
>> statements are wiped out. Is there any way to stop this from happening?
>> Thanks.
>> --
>> -RB
>> :)
>>
>> "Trisha" wrote:
>> I had this problem too....
>> The solution is to manually change the .rdl file with the <Omit> element
>> under the
>> <Parameter> element...
>> Please check
>> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
>> for more details...
>>
>> "capricorn" wrote:
>> > This is similar to Carlos' question on 7/7/05 that nobody responded
>> > to.
>> > Please respond.
>> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
>> >
>> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
>> > cell of
>> > a Parent report, I setup a hyperlink to jump to another report that
>> > accepts a
>> > number of parameters (some of the parameters are defined as nullable
>> > in the
>> > parent and the calling report). However when I try to preview the
>> > report and
>> > click on the hyperlink cell passing parameters that are null in
>> > addition to
>> > parameters that are populated, the subreport seems to hang and wait
>> > for
>> > input. I tried so many variations of this and it just does't work. It
>> > only
>> > works if all the parameters are populated, but that is not realistic.
>> >
>> > Can someone please suggest a solution ASAP? This looks like a bug to
>> > me.
>> > This is not rocket science here.
>> >
>> > Thanks.
>> > --
>> > -RB
>> > :)
>|||Save the rdl file and then go to the designer...I've had no problems after
doing it this way....
"capricorn" wrote:
> I also noticed that after I changed the .RDL file and go back into report
> designer and save the report again for some other change, all my omit
> statements are wiped out. Is there any way to stop this from happening?
> Thanks.
> --
> -RB
> :)
>
> "Trisha" wrote:
> > I had this problem too....
> > The solution is to manually change the .rdl file with the <Omit> element
> > under the
> > <Parameter> element...
> >
> > Please check
> > http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> > for more details...
> >
> >
> >
> > "capricorn" wrote:
> >
> > > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> > > Please respond.
> > > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> > >
> > > I am also using SQL Server 2000 Reporting Services. Within a Matrix cell of
> > > a Parent report, I setup a hyperlink to jump to another report that accepts a
> > > number of parameters (some of the parameters are defined as nullable in the
> > > parent and the calling report). However when I try to preview the report and
> > > click on the hyperlink cell passing parameters that are null in addition to
> > > parameters that are populated, the subreport seems to hang and wait for
> > > input. I tried so many variations of this and it just does't work. It only
> > > works if all the parameters are populated, but that is not realistic.
> > >
> > > Can someone please suggest a solution ASAP? This looks like a bug to me.
> > > This is not rocket science here.
> > >
> > > Thanks.
> > > --
> > > -RB
> > > :)|||I tried mapping the drillthrough parameters to an expression that contains:
=Nothing
It did not work. Since one of the parameters is an integer datatype, I got
the following error message:
"The value provided for the report parameter ... is not valid for its type."
--
-RB
:)
"Bruce L-C [MVP]" wrote:
> No, there is not. RS designer rewrites the RDL.
> Something to try which I would like to hear if you are successfull is to map
> the subreport parameter to an expression and put this in for the expression:
> = Nothing
> I think that might work for you but I am not sure since I have never needed
> to do this myself. Let me know if it works. It would be easier than messing
> with the RDL.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "capricorn" <capricorn@.discussions.microsoft.com> wrote in message
> news:BC4A9C83-47BB-4A90-B6CE-DC0D6DF9C90B@.microsoft.com...
> >I also noticed that after I changed the .RDL file and go back into report
> > designer and save the report again for some other change, all my omit
> > statements are wiped out. Is there any way to stop this from happening?
> > Thanks.
> > --
> > -RB
> > :)
> >
> >
> > "Trisha" wrote:
> >
> >> I had this problem too....
> >> The solution is to manually change the .rdl file with the <Omit> element
> >> under the
> >> <Parameter> element...
> >>
> >> Please check
> >> http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server-reporting/5825/The-value-provided-for-report-parameter-StartDate
> >> for more details...
> >>
> >>
> >>
> >> "capricorn" wrote:
> >>
> >> > This is similar to Carlos' question on 7/7/05 that nobody responded to.
> >> > Please respond.
> >> > (http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=114e1634-7800-466c-a544-05536b58fa6f&sloc=en-us)
> >> >
> >> > I am also using SQL Server 2000 Reporting Services. Within a Matrix
> >> > cell of
> >> > a Parent report, I setup a hyperlink to jump to another report that
> >> > accepts a
> >> > number of parameters (some of the parameters are defined as nullable in
> >> > the
> >> > parent and the calling report). However when I try to preview the
> >> > report and
> >> > click on the hyperlink cell passing parameters that are null in
> >> > addition to
> >> > parameters that are populated, the subreport seems to hang and wait for
> >> > input. I tried so many variations of this and it just does't work. It
> >> > only
> >> > works if all the parameters are populated, but that is not realistic.
> >> >
> >> > Can someone please suggest a solution ASAP? This looks like a bug to
> >> > me.
> >> > This is not rocket science here.
> >> >
> >> > Thanks.
> >> > --
> >> > -RB
> >> > :)
>
>

Passing NULL parameter from aspx page to Report Server

Hi,

I was wondering if anyone here could help me out. I have an aspx page that has the ReportViewer control on it. I have a report that has 3 parameters used for a stored procedure. One of the parameter is a datetime parameter and it can be null. For the purposes of this iteration of the report, this parameter needs to be null.

I can not for the life of me figure out how to send over a null parameter from the aspx page. Everything I have tried gives me this error: "The value provided for the report parameter 'StartDate' is not valid for its type. (rsReportParameterTypeMismatch) "

I tried to send the parameter as "&StartDate:isnull", but that did not work. Neither did: "&StartDate=", "&StartDate=NULL", "&StartDate=<NULL>"

I'm reaching my wits end. I can't seem to find any information anywhere about sending a null parameter to ReportServer via the ReportViewer.

Any help would be greatly appreciated.

I can't find out how to pass NULL as the parameter for the report, either. The report server is based in VB, so it uses Nothing instead of Null, but I can't get it to take that, either. I'm not sure how you have the report set up, or what else you are using it for, but it may be easier if you could set NULL as the default value, and then just not pass in the date parameter.

|||

I tried a sample snippet and its working,

ReportParameter rp1 =

newReportParameter("param2",newstring[] {null },false);

Add it to reportviewer control

List<ReportParameter> paramList =newList<ReportParameter>();

paramList.Add(rp2);

ReportViewer1.LocalReport.SetParameters(paramList); //replace local with server report if you are hostin your reports on reporting server

there you go on report side you can check

=IIF( IsNothing(Parameters!param2.Value),"Null value","not nul")

|||

Unfortunately, I can't get the NULL to set for a default parameter in the report either. I tried, setting it equal to:

=NULL
=null
=System.DBNull
=DBNull
=isnull
=:isnull
:isnull
<NULL>
=""
=
just plain leaving it blank

I have noticed that the when going through preview, the checkbox for NULL is checked. But if I run the report, it is not defaulted to a checked state.

It seems like there would be some information about this somewhere. I have a hard time believing that no one has really found this to be an issue before.

Thanks in advance for your help

|||

It looks like sundher_ganesh has come up with a working solution for sending a null value to the report. If you want a default value of null for the report, you can do one of the following:

In the report designer (At least in VS 2005) you can select Null for the default value (It's one of the three radio buttons).

Otherwise, "=Nothing" (without the quotes, of course), works for the default expression.

|||

Benners_J:

It looks like sundher_ganesh has come up with a working solution for sending a null value to the report. If you want a default value of null for the report, you can do one of the following:

In the report designer (At least in VS 2005) you can select Null for the default value (It's one of the three radio buttons).

Otherwise, "=Nothing" (without the quotes, of course), works for the default expression.

Yes, he did. I guess we posted around the same time. Thanks so much sundher_ganesh and Benners_J for your help.

passing null date value to database

how can i pass null value to database? date is not required field in my database. i can pass default date but i think default date is not good in my case as it is DOB of a customer.Hi there,

Check out the DBNull class. :)|||DBNull.Value should do the trick ...
But don't forget when reading back from the table to check for the NULL value|||well, i tried that it says can't convert System.DBNull to datetime. i also used Convert.DBNull but it shows error at run time if the value isn't provided. well, it seems like i have to pass DBNull.Value directly but in my case i have to assign it to variable coz i am using stored procedure and using SqlHelper class.

what about integers though? is it the same thing?|||I think you should be able to use System.Data.SqlTypes.SqlDateTime.Null.

Terri|||hi terri,
it still says "value of type System.Data.SqlTypes.SqlDateTime.Null cannot be converted to 'Date'"|||Can you please post the relevant code?

Terri|||

 Dim commDate As DateTime
 If tbCommDate.Text = "" Then
 'commDate = New DateTime(1900, 1, 1)
 'can't use this statement
 commDate = System.Data.SqlTypes.SqlDateTime.Null
 Else
 commDate = CType(tbCommDate.Text, DateTime)
 End If

I pass this "commDate" to the stored procedure. i am using SQLHelper class.|||We need to also see the code where you are telling the SQLHelper class the data type of the commDate parameter.

Terri|||

Public Shared Function ProspectiveInsert(ByVal agentId As Integer, ByVal tripId As Integer, ByVal institution As String, ByVal nationality As String, ByVal title As String, ByVal dob As DateTime, ByVal sex As String, ByVal elicos As String, ByVal course1 As String, ByVal course2 As String, ByVal fname As String, ByVal mname As String, ByVal lname As String, ByVal osStud As Boolean, ByVal osAdd As String, ByVal osPhone As String, ByVal osMobile As String, ByVal osFax As String, ByVal osSuburb As String, ByVal osPostcode As String, ByVal osCity As String, ByVal osCountry As String, ByVal add As String, ByVal phone As String, ByVal mobile As String, ByVal fax As String, ByVal suburb As String, ByVal postcode As String, ByVal city As String, ByVal country As String, ByVal email As String, ByVal ugQual As String, ByVal ugIns As String, ByVal ugYear As String, ByVal seconQual As String, ByVal seconYear As Integer, ByVal seconIns As String, ByVal pgQual As String, ByVal pgIns As String, ByVal pgYear As Integer, ByVal appStatus As String, ByVal appNotes As String, ByVal appDate As String, ByVal ielts As Double, ByVal engRemarks As String, ByVal coeDate As DateTime, ByVal coeRemarks As String, ByVal offerDate As DateTime, ByVal offerRemarks As String, ByVal recAddDate As DateTime, ByVal prefCommDate As DateTime, ByVal notes As String, ByVal modBy As String) As Integer
 Try
 SqlHelper.ExecuteNonQuery(ConfigurationSettings.AppSettings("connectionString"), "MCS_ProspectiveStudentInsert", agentId, tripId, institution, nationality, title, dob, sex, elicos, course1, course2, fname, mname, lname, osStud, osAdd, osPhone, osMobile, osFax, osSuburb, osPostcode, osCity, osCountry, add, phone, mobile, fax, suburb, postcode, city, country, email, ugQual, ugIns, ugYear, seconQual, seconIns, seconYear, pgQual, pgIns, pgYear, appStatus, appNotes, appDate, ielts, engRemarks, coeDate, coeRemarks, offerDate, offerRemarks, recAddDate, prefCommDate, notes, modBy)
 Catch ex As SqlException
 Return ex.Number
 End Try
 Return 0
 End Function

This is the function where I have passed the dates but i can't assign the System.Data.SqlTypes.Null to date variable.|||OK, I believe I have misled you. You should be using System.DBNull. Let us know if this works better for you.

Terri|||it says "DBNull is a type in system and can't be used as an expression."

at the moment i am using default value but i think that's not a good idea is it?|||thanx a lot for your time Terri.
i appreciate your willingness to help me.|||THe problem has NOTHING to do with SQL. See:

[code]
Dim commDate As DateTime

If tbCommDate.Text = "" Then

'commDate = New DateTime(1900, 1, 1)

'can't use this statement

commDate = System.Data.SqlTypes.SqlDateTime.Null
[/code]

NATURALLY blows. You can not assign SqlDateTime.Null to a DateTime.

You have to make the assignment "lower" - i.e. you pass the SqlDateTime directly into the relevant SQL parameter. You can not store it in another non-compatible time in the meantime.

For example, the EntityBroker - my O/R-Mapper - makes the switch statement directly when putting the value into the PARAMETER (or encoding the value for the SQL string). THEN it gets accepted (DBNull.Value, btw.). The way you do it earlier just results in an invalid cast.|||but i am using SqlHelper class. so i think i can't use it then. right?

Passing back NULL

Is there any way in a query to pass back a NULL if no data is found?
I have this query that is looking at our Customer table and some customers
may not exist but in that case I want to pass back a NULL...
My SQL looks like so...
SELECT *
FROM _CUSTOMER_
WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
The first name, Susanna Alway does NOT exist...so in that case I'd like to
pass pack a NULL.
These groups are the best!
Please help me out!
Thanks!A null.....? A single null column? An empty resultset?
Thomas
"RTP" <RTP@.discussions.microsoft.com> wrote in message
news:8847FFB7-02DA-4357-A62C-A95BE3B18576@.microsoft.com...
> Is there any way in a query to pass back a NULL if no data is found?
> I have this query that is looking at our Customer table and some customers
> may not exist but in that case I want to pass back a NULL...
> My SQL looks like so...
> SELECT *
> FROM _CUSTOMER_
> WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
> OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
> OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
> OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
> OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
> OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
> The first name, Susanna Alway does NOT exist...so in that case I'd like to
> pass pack a NULL.
> These groups are the best!
> Please help me out!
> Thanks!|||Yes use If Exists
If Exists (Select * From ...
Where ... ) -- make sure From & Where clause are same as main
query
Select <Stuff> From ...
Where ...
Order By ...
Else
Select Null As Col1Name, Null as Col2Name,
Null as Col3Name, etc...
After your query
"RTP" wrote:

> Is there any way in a query to pass back a NULL if no data is found?
> I have this query that is looking at our Customer table and some customers
> may not exist but in that case I want to pass back a NULL...
> My SQL looks like so...
> SELECT *
> FROM _CUSTOMER_
> WHERE (NAME LIKE 'Alway%' AND FNAME ='Susanna')
> OR (NAME LIKE 'Abaquin%' AND FNAME ='Paul')
> OR (NAME LIKE 'Abbott%' AND FNAME ='Cindy')
> OR (NAME LIKE 'Abney%' AND FNAME ='Linda')
> OR (NAME LIKE 'Abraham%' AND FNAME ='Jo')
> OR (NAME LIKE 'Abrams %' AND FNAME ='Jeff')
> The first name, Susanna Alway does NOT exist...so in that case I'd like to
> pass pack a NULL.
> These groups are the best!
> Please help me out!
> Thanks!|||That won't work because the EXISTS will return a TRUE condition for the OR i
n
the query. I have to build this for 7,000 names so I can't query each name
individually. I'm thinking of using a JOIN which will pass back NULLS if the
row doesn't exist. My problem here is that a user messed up a whole
spreadsheet where there are e-mail addresses and I'm cutting and pasting
these 7,000 names out of an e-mail spreadsheet and then using Query Analyzer
to go up against the database to see if we have their e-mail address. The
7,000 names include those people which may NOT be in out _CUSTOMER_ table an
d
there is no way of determining whether they're in the database or not until
I
query.
Any help or insight would be GREATLY appreciated!!!
"CBretana" wrote:
> Yes use If Exists
> If Exists (Select * From ...
> Where ... ) -- make sure From & Where clause are same as main
> query
> Select <Stuff> From ...
> Where ...
> Order By ...
> Else
> Select Null As Col1Name, Null as Col2Name,
> Null as Col3Name, etc...
>
> After your query
> "RTP" wrote:
>|||Since this sounds like a one-time thing, then write a script that cycles thr
ough
each address from the spreadshet and does whatever sophisticated checking an
d
data cleasing is necesassry.
If you do that in T-SQL, then you'll use cursors or you could do it in some
other language like VBA from Excel.
Thomas
"RTP" <RTP@.discussions.microsoft.com> wrote in message
news:098909F0-4473-4145-94EC-056005EB917A@.microsoft.com...
> That won't work because the EXISTS will return a TRUE condition for the OR
in
> the query. I have to build this for 7,000 names so I can't query each name
> individually. I'm thinking of using a JOIN which will pass back NULLS if t
he
> row doesn't exist. My problem here is that a user messed up a whole
> spreadsheet where there are e-mail addresses and I'm cutting and pasting
> these 7,000 names out of an e-mail spreadsheet and then using Query Analyz
er
> to go up against the database to see if we have their e-mail address. The
> 7,000 names include those people which may NOT be in out _CUSTOMER_ table
and
> there is no way of determining whether they're in the database or not unti
l I
> query.
> Any help or insight would be GREATLY appreciated!!!
> "CBretana" wrote:
>

Passing a null parameter from a report to a subreport

Hi Group,
I have a report with a subreport and I'm trying to make them work in the
report designer in VS2003.
The report has some parameters and one of them is DepartmentIndex, which can
be NULL.
This parameter is being passed down to the subreport.
I set the DepartmentIndex parameter to accept both blank and null values and
gave it a default value of " " (one space),
in both the report and the subreport.
If I run the SUBREPORT alone and check the null checkbox for this parameter,
the subreport works fine and displays the records with a null value in the
DepartmentIndex field.
But if I try to run the REPORT and check the null checkbox, it displays an
error :
An error occurred while executing the subreport '<subreport_name>': One or
more parameters required to run the report have not been specified.
Though, the report data is displayed correctly (showing the rows with null
DepartmentIndex).
Can anybody help, please ?
Thanks,
Andrei.I got the same error and I would someone can help?
One or more parameters required to run the report have not been specified
Linh
"andrei" wrote:
> Hi Group,
> I have a report with a subreport and I'm trying to make them work in the
> report designer in VS2003.
> The report has some parameters and one of them is DepartmentIndex, which can
> be NULL.
> This parameter is being passed down to the subreport.
> I set the DepartmentIndex parameter to accept both blank and null values and
> gave it a default value of " " (one space),
> in both the report and the subreport.
> If I run the SUBREPORT alone and check the null checkbox for this parameter,
> the subreport works fine and displays the records with a null value in the
> DepartmentIndex field.
> But if I try to run the REPORT and check the null checkbox, it displays an
> error :
> An error occurred while executing the subreport '<subreport_name>': One or
> more parameters required to run the report have not been specified.
> Though, the report data is displayed correctly (showing the rows with null
> DepartmentIndex).
> Can anybody help, please ?
> Thanks,
> Andrei.
>
>|||How do you pass parameter into subreport?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Linh Phan" <LinhPhan@.discussions.microsoft.com> wrote in message
news:FA2946C8-ECD9-47E1-AC80-B7AA2EF041B2@.microsoft.com...
>I got the same error and I would someone can help?
> One or more parameters required to run the report have not been specified
> Linh
> "andrei" wrote:
>> Hi Group,
>> I have a report with a subreport and I'm trying to make them work in the
>> report designer in VS2003.
>> The report has some parameters and one of them is DepartmentIndex, which
>> can
>> be NULL.
>> This parameter is being passed down to the subreport.
>> I set the DepartmentIndex parameter to accept both blank and null values
>> and
>> gave it a default value of " " (one space),
>> in both the report and the subreport.
>> If I run the SUBREPORT alone and check the null checkbox for this
>> parameter,
>> the subreport works fine and displays the records with a null value in
>> the
>> DepartmentIndex field.
>> But if I try to run the REPORT and check the null checkbox, it displays
>> an
>> error :
>> An error occurred while executing the subreport '<subreport_name>': One
>> or
>> more parameters required to run the report have not been specified.
>> Though, the report data is displayed correctly (showing the rows with
>> null
>> DepartmentIndex).
>> Can anybody help, please ?
>> Thanks,
>> Andrei.
>>|||Hi Lev,
I just figured it out by right click on each subreport and set the missing
parameters for each. It worked well.
Thanks!
Linh
"Lev Semenets [MSFT]" wrote:
> How do you pass parameter into subreport?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Linh Phan" <LinhPhan@.discussions.microsoft.com> wrote in message
> news:FA2946C8-ECD9-47E1-AC80-B7AA2EF041B2@.microsoft.com...
> >I got the same error and I would someone can help?
> >
> > One or more parameters required to run the report have not been specified
> >
> > Linh
> >
> > "andrei" wrote:
> >
> >> Hi Group,
> >>
> >> I have a report with a subreport and I'm trying to make them work in the
> >> report designer in VS2003.
> >> The report has some parameters and one of them is DepartmentIndex, which
> >> can
> >> be NULL.
> >> This parameter is being passed down to the subreport.
> >> I set the DepartmentIndex parameter to accept both blank and null values
> >> and
> >> gave it a default value of " " (one space),
> >> in both the report and the subreport.
> >> If I run the SUBREPORT alone and check the null checkbox for this
> >> parameter,
> >> the subreport works fine and displays the records with a null value in
> >> the
> >> DepartmentIndex field.
> >> But if I try to run the REPORT and check the null checkbox, it displays
> >> an
> >> error :
> >> An error occurred while executing the subreport '<subreport_name>': One
> >> or
> >> more parameters required to run the report have not been specified.
> >>
> >> Though, the report data is displayed correctly (showing the rows with
> >> null
> >> DepartmentIndex).
> >>
> >> Can anybody help, please ?
> >>
> >> Thanks,
> >>
> >> Andrei.
> >>
> >>
> >>
>
>

Passing "null" in the URL for an Integer parameter.

Hello,
I've got a stored procedure that my report uses as a datasource. One of the parameters for the stored procedure is an Integer called "OfficeTypes". The stored procedure checks if OfficeTypes is null, and if it is it returns every record. I want to hide the "OfficeTypes" input on the report and pass a null value on the URL, however I can't seem to do this. My url is as follows:
"http://...&OfficeType:isnull=true"
I get the error message: "An attempt was made to set a report parameter 'OfficeTypeID-NULL' that is not defined in this report".
If I change my URL to the following:
"http://...&OfficeType=null"
I get the error message:"The value provided for the report parameter 'OfficeTypeID' is not valid for its type."
So the question is, how can you set an Integer parameter for a report to null using the URL? All help is welcome. Thanks.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.Hey Guys,
Could you please tell me how to use a stored procedure as the data source.
The problem that I am facing is, the fields returned by the stored procedure
are not getting listed in the fields window. I tried creating the fields
explicitly but it gave me an error that the required fields are not found in
the dataset.
Any pointers would be of lot of help.
thanks,
Nilesh
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:%23E1a0t2YEHA.2432@.tk2msftngp13.phx.gbl...
> Hello,
> I've got a stored procedure that my report uses as a datasource. One of
the parameters for the stored procedure is an Integer called "OfficeTypes".
The stored procedure checks if OfficeTypes is null, and if it is it returns
every record. I want to hide the "OfficeTypes" input on the report and pass
a null value on the URL, however I can't seem to do this. My url is as
follows:
> "http://...&OfficeType:isnull=true"
> I get the error message: "An attempt was made to set a report parameter
'OfficeTypeID-NULL' that is not defined in this report".
> If I change my URL to the following:
> "http://...&OfficeType=null"
> I get the error message:"The value provided for the report parameter
'OfficeTypeID' is not valid for its type."
> So the question is, how can you set an Integer parameter for a report to
null using the URL? All help is welcome. Thanks.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||I'm not very experienced so I'm not sure I'll be much help, but first off in
the "Data" tab of your report, are you setting the command type to "Stored
Procedure" or are you setting it "Text" and calling the EXEC storedProcedure
command? If the command type is stored procedure, I'm not sure what to tell
you. Whenever I add a stored procedure this way the fields for the sp
automatically are added to the parameters list for my report, and thus show
up on the reports window. You could always add some parameters to the
reports parameter list and then edit the data set "Parameters" properties to
point to the properties you added. If you still have questions, explain more
about how and what you've done.
"Nilesh Oswal" <nilesh_oswal@.persistent.co.in> wrote in message
news:eaZ44D3YEHA.3536@.TK2MSFTNGP11.phx.gbl...
> Hey Guys,
> Could you please tell me how to use a stored procedure as the data
source.
> The problem that I am facing is, the fields returned by the stored
procedure
> are not getting listed in the fields window. I tried creating the fields
> explicitly but it gave me an error that the required fields are not found
in
> the dataset.
> Any pointers would be of lot of help.
> thanks,
> Nilesh
>
> "SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
> news:%23E1a0t2YEHA.2432@.tk2msftngp13.phx.gbl...
> > Hello,
> >
> > I've got a stored procedure that my report uses as a datasource. One of
> the parameters for the stored procedure is an Integer called
"OfficeTypes".
> The stored procedure checks if OfficeTypes is null, and if it is it
returns
> every record. I want to hide the "OfficeTypes" input on the report and
pass
> a null value on the URL, however I can't seem to do this. My url is as
> follows:
> > "http://...&OfficeType:isnull=true"
> > I get the error message: "An attempt was made to set a report parameter
> 'OfficeTypeID-NULL' that is not defined in this report".
> > If I change my URL to the following:
> > "http://...&OfficeType=null"
> > I get the error message:"The value provided for the report parameter
> 'OfficeTypeID' is not valid for its type."
> > So the question is, how can you set an Integer parameter for a report to
> null using the URL? All help is welcome. Thanks.
> >
> > --
> > Posted using Wimdows.net NntpNews Component -
> >
> > Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
> supports Post Alerts, Ratings, and Searching.
>

pass in null/blank value in the date field or declare the field as string and co

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@.option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@.mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please helpThe stored procedure will accept null if you define the parameter that way:

create procedure TESTPROCEDURE (@.TestDate datetime = NULL)
as
select @.TestDate
go

exec TESTPROCEDURE '1/1/2003'
go

exec TESTPROCEDURE
go

blindman|||I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan|||Originally posted by danielacroft
I think vbNull also works when passing in a parameter. The code you have below is passing in an empty string which I'm sure I don't have to tell you is not null.

Try the following:
.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I think the stored proc idea is better though, it's safer and better for your data integrity.

Dan

Hello Dan,

What I need is an empty string in the date field to pass in in the stored procedure. What is the vb code for that?

Thanks!|||The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan|||Originally posted by danielacroft
The code to pass null (empty string won't work and null will only work if you have allowed nulls on this column in your db design) for a parameter is this:

.Parameters.Append .CreateParameter("@.mfg_start_date", adChar, adParamInput, 10, vbNull)

I modified your existing code. I'm not 100% sure that this will work but it should.

Dan

Thanks for replying so quickly.
I edited my code as you have it above.
I'm stilll having trouble getting the date displaying correctly. I need the year to display in four digits. It displays something '12/31/03'
This is code that I have

Function get_date(mfg_start_date as string,..)
mfg_start_date = Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)
mfg_start_date = CStr(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Year(mfg_start_date))

mfg_start_date is the textbox I need the date field to display but it will only eight digits of the year and place two empty strings after. I can't understand why. In the db design the mfg_start_date field is a char with length 10 as in the stored procedure.

Also there must be a better way to write the code that I have above.

Thank you again.|||The date format is normally determined by the locale settings ont he server when you're using VB. Can I ask why you're not using a date field in your database?

Dan

pass in null value for boolean data type in VB

What is the syntax to pass in null value for boolean data type in VB in the stored procedure?
This is what I tried and it doesn't work.
.Parameters.Append .CreateParameter("@.disposition", adBoolean, adParamInput, 1, vbNull)
Thank you.I'm no vb programmer (at least that's what I tell people)

How about:

.Parameters.Append .CreateParameter("@.disposition", adBoolean, adParamInput, 1, "Null")

??