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 credentials from asp.net app to RS

Hello All,

I am embedding reports in a Web Application using the ReportViewer control. The Web Application and the ReportServices reside on different machines on the same network. The settings on the ReportServices IIS are Windows Integrated Authentication and anonymous access is disabled.

When I access the reports from my Web Application, I get windows pop-up asking for credentials. I am using impersonation to pass the credentials to the reporting services. But somehow the credentials are not passed to the report server and the pop-up shows up always. I am trying to get rid of the pop-window. Can somebody help??

Does using any other forms of authentication help?

THanks
Imran

How are you displaying the report in your web app? Just a link, or something different? I'm thinking this might be the double hop problem, so you might want to impersonate a service account that's specific for that report.|||I am displaying the report using the reporviewer control.
I am using impersonation in my web app. The same account also exists in the Report server machine. You are right I think it is a double hop problem. But I dont know how to solve it. I think we should use the Soap method to display the reports instead of the URL Access method
Thanks
Imran|||

With the report viewer impersonation settings don't matter - the report viewer sends the client's browser directly to the report server. Is everyone on the domain? Is the client browser setup to login automatically for the zone the report server is in?

|||

What you need is called Pass Through Authentication. Try the links below for more info. Hope this helps.
http://www.iisanswers.com/articles/enablepassthrough.htm

http://www.codeproject.com/aspnet/PassThroughSecurity.asp

Passing control flags to stored procedure

Wanted to know which among these options is better and why? Or if their
could be scenarios where we could opt for one of these.

a) flags passed from code to control the execution of queries within a
stored procedure i.e. - where queries within a single stored procedure
are controlled by flags passed to them.
OR

b) Break individual queries into separate stored procedureSQLnewbie (gaurav124@.hotmail.com) writes:
> Wanted to know which among these options is better and why? Or if their
> could be scenarios where we could opt for one of these.
> a) flags passed from code to control the execution of queries within a
> stored procedure i.e. - where queries within a single stored procedure
> are controlled by flags passed to them.
> OR
> b) Break individual queries into separate stored procedure

Both can be useful depending on context, requirements and circumstance.
And not the least it would be a matter of taste.

See a stored procedure that encapulates something. Say that in our
system that would be a need to list the positions for an account on
a certain date. You would expect a procedure that as parameter takes
@.accno and @.date. Now, since we have one table for current positions,
and table with historic data, it will have to be two different queries.
But that's not really something the application should have to
bother about, and thus it should be one procedure (which possibly
forks off into subprocedures).

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||But aren't their situations where controlling the stored procedures
would cause query recompilation (Execution Plan recreation) which could
lead to performance issues.|||SQLnewbie (gaurav124@.hotmail.com) writes:
> But aren't their situations where controlling the stored procedures
> would cause query recompilation (Execution Plan recreation) which could
> lead to performance issues.

Not sure what you mean here, but generally passing variables to stored
procedures does not cause recompilation.

On the other hand sometimes you may wish that it would. We had a case
in our application. There was a stored procedure that I frequenly caught
doing a complex parallel plan requiring a couple of seconds to run,
when there was a much more effcient plan for the task.

The problem was that the client that called this procedure first called
it with a 0 for its only parameter which means "bring me all from the
last 20 days". On successive calls, the client only wanted the rows
with an id higher than the highest id in the previous call. The problem
was that the plan for getting the rows for 20 days was different from
getting the delta, but it was this plan that ended up in the cache. My
solution in this case was to write to sub-procedures, one each for the
two cases. But to the client, it still looked the same. (Except that
performance was better!)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you ever had a course in basic software engineering? Look up
coupling and cohesion.

Cohesion:
This is how well a module does one and only one thing; that it is
logically coherent. The modules should have strong cohesion. You
ought to name the module in the format "<verb><object>", where the
"<object>" is a specific logical unit in the data model. There are
several types of cohesion. We rank them going from the worst form of
cohesion to the best

1) Coincidental
2) Logical
3) Temporal
4) Procedural
5) Communicational
6) Informational
7) Functional

Coupling:
If modules have to be used in a certain order, then they are strongly
coupled. If they can be executed independently of each other and put
together like Lego blocks, then they are loosely or weakly coupled.
There are several kinds of coupling, which are ranked from worse to
best:

1) Content
2) Common
3) Control
4) Stamp
5) Data

This is covered briefly in a chapter on writing stored procedures in my
book on SQL Programming Style, which will be out later in 2005. In the
meantime, you can read DeMarco, Yourdon, Constantine, Myers or several
other of the pioneers.
This is FAR more basic than SQL programming.

Passing an array of values for a single parameter

I am using a reportviewer control on my web form and I have created a parameter in my report. I can pass a hardcoded parameter and it works for one values at a time, but I want to send an array of values for the same parameter. For example if I have 5 different fleets of aircaft I might want to see 1,2 or all of the fleet in this report. I am kind of new to SQL reporting any help would be great.

Thanks in advance

you can pass an array but you need to do some processing in the RS designer..under the Data tab. or you can do it all in the stored proc it self by setting up the parameter as varchar and doing the splitting inside..|||

Thanks I will look into RS Designer Data Tab. Is there some sample code available the performs this particular function.

|||check out my blog..there is an article about parsing an array..there is also a link to a better article..it involves using UDFs. It is prbly a better and more efficient approach from the stored proc itself.|||

Could I use a parameter collection to pass an Array of Parameters to a report, if so how would I code that?

Thanks

|||prios did you get this working? If so, could you post your solution? I would also be interested in seeing how you passed a single parameter using the report viewer.
Thanks.|||

I found a solution...

http://odetocode.com/Articles/128.aspx

Passing a selected row column value to the stored procedure

I have a simple Gridview control that has a delete command link on it.

If I use the delete SQL code in line it works fine. If I use a stored procedure to perform the SQL work, I can't determine how to pass the identity value to the SP. Snippets are below...

The grid
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2">
<Columns>
<asp:BoundField DataField="member_id" HeaderText="member_id" InsertVisible="False"
ReadOnly="True" SortExpression="member_id" />
<asp:BoundField DataField="member_username" HeaderText="member_username" SortExpression="member_username" />
<asp:BoundField DataField="member_firstname" HeaderText="member_firstname" SortExpression="member_firstname" />
<asp:BoundField DataField="member_lastname" HeaderText="member_lastname" SortExpression="member_lastname" />
<asp:BoundField DataField="member_state" HeaderText="State" SortExpression="member_state" />
<asp:CommandField ShowEditButton="True" />
<asp:CommandField ShowDeleteButton="True" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:rentalConnectionString1 %>"
SelectCommand="renMemberSelect" SelectCommandType="StoredProcedure"
DeleteCommand="renMemberDelete" DeleteCommandType="StoredProcedure"
OldValuesParameterFormatString="original_{0}"
>

<DeleteParameters>

<asp:Parameter Name="member_id" Type="Int32" />

</DeleteParameters>

</asp:SqlDataSource
the SP

CREATE PROCEDURE renMemberDelete
@.member_id as int
As UPDATE [renMembers]
SET member_status=1
WHERE [member_id] = @.member_id
GO

Try:GridView2.DataKeyNames="member_id"

or

<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataSourceID="SqlDataSource2" DataKeyNames="member_id">

Passing a dataset as a parameter?

Can you build a dataset in a web application and send it as a parameter
through the report viewer control and have reporting services use this
dataset as the binding dataset for your table in Reporting Services? IF so,
can you help me or direct me to some article that will show me how to do
this.RS (both 2000 and 2005) don't support binding to applcation datasets
natively. If you need to pass a dataset to a server-generated report, you
may find my custom extension useful
(http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
If you RS 2005 and the Report Viewers, you can bind the dataset to a local
report.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> Can you build a dataset in a web application and send it as a parameter
> through the report viewer control and have reporting services use this
> dataset as the binding dataset for your table in Reporting Services? IF
> so,
> can you help me or direct me to some article that will show me how to do
> this.|||The posts that I am seeing in your article looks like people are having
problems with doing this in Reporting Services 2005. At least the referencing
portion. Could you update with how this is done in 2005 or post where the
AWC.RS.Extensions.dll
is copied in 2005.
Thanks in advance
"Teo Lachev [MVP]" wrote:
> RS (both 2000 and 2005) don't support binding to applcation datasets
> natively. If you need to pass a dataset to a server-generated report, you
> may find my custom extension useful
> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
> If you RS 2005 and the Report Viewers, you can bind the dataset to a local
> report.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> > Can you build a dataset in a web application and send it as a parameter
> > through the report viewer control and have reporting services use this
> > dataset as the binding dataset for your table in Reporting Services? IF
> > so,
> > can you help me or direct me to some article that will show me how to do
> > this.
>
>|||I was also reading that someone was having trouble with using a dataset that
used SQL Query joins with multiple tables or is this not a problem?
"Teo Lachev [MVP]" wrote:
> RS (both 2000 and 2005) don't support binding to applcation datasets
> natively. If you need to pass a dataset to a server-generated report, you
> may find my custom extension useful
> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
> If you RS 2005 and the Report Viewers, you can bind the dataset to a local
> report.
> --
> HTH,
> ---
> Teo Lachev, MVP, MCSD, MCT
> "Microsoft Reporting Services in Action"
> "Applied Microsoft Analysis Services 2005"
> Home page and blog: http://www.prologika.com/
> ---
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
> > Can you build a dataset in a web application and send it as a parameter
> > through the report viewer control and have reporting services use this
> > dataset as the binding dataset for your table in Reporting Services? IF
> > so,
> > can you help me or direct me to some article that will show me how to do
> > this.
>
>|||One day I will probably get to it :-) It shouldn't be that terribly
difficult to upgrade the changed interfaces.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:74683164-56A9-44BD-806D-7C9DBBCAA339@.microsoft.com...
> The posts that I am seeing in your article looks like people are having
> problems with doing this in Reporting Services 2005. At least the
> referencing
> portion. Could you update with how this is done in 2005 or post where the
> AWC.RS.Extensions.dll
> is copied in 2005.
> Thanks in advance
> "Teo Lachev [MVP]" wrote:
>> RS (both 2000 and 2005) don't support binding to applcation datasets
>> natively. If you need to pass a dataset to a server-generated report, you
>> may find my custom extension useful
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> If you RS 2005 and the Report Viewers, you can bind the dataset to a
>> local
>> report.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
>> > Can you build a dataset in a web application and send it as a
>> > parameter
>> > through the report viewer control and have reporting services use this
>> > dataset as the binding dataset for your table in Reporting Services? IF
>> > so,
>> > can you help me or direct me to some article that will show me how to
>> > do
>> > this.
>>|||While the sky is the limit what your CDE can do, RS expects a
two-dimensional resultset.
--
HTH,
---
Teo Lachev, MVP, MCSD, MCT
"Microsoft Reporting Services in Action"
"Applied Microsoft Analysis Services 2005"
Home page and blog: http://www.prologika.com/
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:3CC6145C-449B-4C11-B5DF-A7A4F1275375@.microsoft.com...
>I was also reading that someone was having trouble with using a dataset
>that
> used SQL Query joins with multiple tables or is this not a problem?
> "Teo Lachev [MVP]" wrote:
>> RS (both 2000 and 2005) don't support binding to applcation datasets
>> natively. If you need to pass a dataset to a server-generated report, you
>> may find my custom extension useful
>> (http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5).
>> If you RS 2005 and the Report Viewers, you can bind the dataset to a
>> local
>> report.
>> --
>> HTH,
>> ---
>> Teo Lachev, MVP, MCSD, MCT
>> "Microsoft Reporting Services in Action"
>> "Applied Microsoft Analysis Services 2005"
>> Home page and blog: http://www.prologika.com/
>> ---
>> "dillig" <dillig@.discussions.microsoft.com> wrote in message
>> news:28CDC775-5FFA-4993-B9CF-D766B33A5DF3@.microsoft.com...
>> > Can you build a dataset in a web application and send it as a
>> > parameter
>> > through the report viewer control and have reporting services use this
>> > dataset as the binding dataset for your table in Reporting Services? IF
>> > so,
>> > can you help me or direct me to some article that will show me how to
>> > do
>> > this.
>>

Pass UserName to the SQL Select ? (VB)

VB ASP.NET 2.0

How do I get the Authenticated UserName passed to a Select Command of a data control ?

I'm not clear on how to get the logged in username fromUser.Identity.Name as a string or how to pass it to the Select Command of a control.

I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL.

I want to show the user data from a database, based on who is logged in .

Help, greatly appreciated.

Chris

User.Identity.Name will hold the value of the currently authenticated user. How you deal with parameters depends on how you approach it. with a straightforward Sql command, the following is a guide:

Dim query As String = "Select * From Users WHERE UserName = @.UserName"
Dim conn As New SqlConnection(myconnectionstring)
Dim cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@.UserName",User.Identity.Name)
conn.Open()
Dim rdr As New SqlDataReader = cmd.ExecuteReader()
While rdr.Read()...

If you want to use a SqlDataSource, this article should help:http://aspnet.4guysfromrolla.com/articles/030106-1.aspx

|||

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

|||

valenumr:

You can also use the SQL Function SUSER_SNAME() to get the login directly in the SQL query...

SELECT foo, bar, suser_sname() as user FROM some_table

But that'll get the SQL user, which probably won't be the same as the user logged on to the website..

Regards

Fredr!k

|||

Very true... it will work if your DB is using windows login's, and you don't have any impersonation going on in your web server... so I guess, I should have mentioned that.

|||

Thanks to all the rapid replies.

An error had lead me to believe that the User.Identity.Name did not return a string, but further research says it should.

I should be clearer about the approach I was attempting. Given that I'm struggling to learn .Net and ASP.Net I'm struggling to keep the issues clear.

I was trying to find a way to do the assignment in the Mark Up for the server controls.

I have found that you can also create a Parameter in the mark up for the SqlSourceControl section under the <SelectParameters> section that you can then use in the Select Command.

Still trying to tie that info together to do this.

BUT , your cmd.Parameters.AddWithValue method is eye opening. When I start working with code, I'm still trying to learn the models to understand whichevents to put such code.

(Anyone know a concise reference for this ? )

I want a dropdown box to be filled with a set of items from a database that are particular to the User that's logged on.

(ps. I find from my research that you can use My.User.Name , also to get the username)

Many Thanks for all the help !

Chris9876

|||

I'm not at my computer to verify, but try User.Identity.Name.tostring If that doesn't work, I will check when I get home as I do this exact thing, but with the userid and not the username.

|||

This is from memory, but you can create a parameter in your sqldatasource (Let's call it @.Username).

Then in the SqlDataSource1_Selecting event, just put:

e.Commad.Parameters("@.UserName").Value= My.User.Name

Then whenever the sqldatasource is about to do a select, you set the username parameter to whomever is currently logged it. Simple and easy.

If you are changing the output based on the persons roles, or if they are currently logged in, you might want to just use a loginview control.

|||

The following Markup and Event Code works great. (DropdownList is filled with data from MyField whereUserName=@.UserName)

Thank you all very much.

<asp:DropDownListID="DropDownList1"runat="server"DataSourceID="SqlDataSource1"DataTextField="ClassCode"DataValueField="ClassCode">

</asp:DropDownList>

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT [MyField] FROM [MyView] WHERE ([UserName] = @.UserName)">

<SelectParameters>

<asp:ParameterName="UserName"Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

ProtectedSub SqlDataSource1_Selecting(ByVal senderAsObject,ByVal eAs System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs)Handles SqlDataSource1.Selecting

e.Command.Parameters("@.UserName").Value =My.User.Name

EndSub