Prev: ReportViewer - size to contents
Next: How to Carry information from the web page into the report
From: dbuchanan on 20 Jun 2008 03:11
I have a stored procedure that takes seven parameters. I have tested it
throughly and it delivers expected results.

However when it is used as the source for the ReportViewer it fails. It
falis with no errors and an empty screen - but the hearder displays after a
brief generating progress ring.

My setup is like this:
I added a SqlDataSource to my web page and for the SQL statement supplied
the EXEC statement with parameters. In the next screen I wired up the
parameters to the appropriate controls.

I have tested to find what parameters fail. Here is what I found.

When I use all parameters it fails when I supply a certain five of the seven
I get the empty screen as described above.

I am certan that all there is data to display. What else could it be?

declaraation section of the stored procedrue
ALTER procedure [dbo].[usp_Report1]
( @EntityID int ,@RoleID nvarchar(1000) ,@CourseID nvarchar(1000)
,@HireDateRangeStart ,@HireDateRangeEnd smalldatetime ,@CourseDateRangeStart
smalldatetime ,@CourseDateRangeEnd smalldatetime )

At most the parameters I can supply without an empty result;
EXEC [dbo].[usp_Report1] @EntityID, N'1786,1904,1807',
'453055431,453055010,151,155',
@HireDateRangeStart, @HireDateRangeEnd, @CourseDateRangeStart,
@CourseDateRangeEnd

Test values I supply
EXEC [dbo].[usp_Report1] 26183, N'1786,1904,1807',
N'453055431,453055010,151,155', '1900-01-01' ,'2079-06-06' ,'1900-01-01'
,'2079-06-06'

Can the empty results mean anything other than an empty set? Are there any
known bugs?

Thanks,
Doug


From: "Charles Wang [MSFT]" on 20 Jun 2008 06:28
Hi Doug,
Before we perform further research, I would like to confirm the following
questions with you:
1. Could you execute the stored procedure successfully in SSMS?
Open SSMS, create a new query window and execute your query:
============================================================
EXEC [dbo].[usp_Report1] 26183, N'1786,1904,1807',
N'453055431,453055010,151,155', '1900-01-01' ,'2079-06-06' ,'1900-01-01'
,'2079-06-06'
=============================================================
Check if it can return the correct result. If this does not work as
expected, you may need to dig into your stored procedure for detail view.

2. Is the submitted T-SQL statement to your SQL Server instance valid from
SQL Profiler?
Open SQL Profiler to monitor your SQL Server instance to see what the
T-SQL statement submitted is. Copy it out to SSMS query window to see if it
is valid.

Also I notice that you were using some multivalve parameters and I
recommend that you refer to this article to see if you correctly use them:
Using Single-Valued and Multivalued Parameters
http://msdn.microsoft.com/en-us/library/aa337292.aspx

Try using JOIN function to set the stored procedure parameter in your
dataset query. For example, open your dataset property window, switch to
the Parameters tab and set the following:
@CourseID = JOIN(Parameters!CourseID.Value,",")

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.


Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg(a)microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

From: dbuchanan on 23 Jun 2008 17:05
Charles,

> 1. Could you execute the stored procedure successfully in SSMS?

Yes, I could directly execute it in SSMS and I get results returned.

(by the way I am using SSMS the underlying database is Sql 2000)

> 2. Is the submitted T-SQL statement to your SQL Server instance
> valid from SQL Profiler?

For both multi-vlaue parameters it is only picking up the first value

> Also I notice that you were using some multivalve parameters and I
> recommend that you refer to this article to see if you correctly use them:
> Using Single-Valued and Multivalued Parameters
> http://msdn.microsoft.com/en-us/library/aa337292.aspx

You did pretty good at anticipating where the problem is. This issue does
have to do with this multi-value problem.

I have reviewed the article above and it appears to me that it only applies
to using ReportingServices ReportViewere in Remote mode. I am using Local
mode so I could not see a way to implement the article's suggestions.

This "multi-vlaue" problem is affecting me in another places in my
applicaiton so I am very interested in understading the solution to this. (I
have a CheckBoxList that is supposed to receive multi-values from a previous
CheckBoxList and it only recieves the first value.)

> Try using JOIN function to set the stored procedure parameter in your
> dataset query. For example, open your dataset property window, switch to
> the Parameters tab and set the following:
> @CourseID = JOIN(Parameters!CourseID.Value,",")

I do not know where to apply this Join function. Below I explain the wire-up
top assist in the discussion.

There are three parts to the implementation; ReportViewer, SqlDataSource and
StioredProcedure.

The ReportViewer control references the RDLC and a SqlDataSource.
The SqlDataSource references the data connection and a SQL statement shown
below

EXEC [dbo].[usp_Report1]
@CourseID,
@CourseDateRangeStart,
@CourseDateRangeEnd,
@HireDateRangeStart,
@HireDateRangeEnd,
@RoleID

Each of the parameters is wired to a different control on the form.
@CourseID and @RoleID are wired to different CheckBoxLists. These two
conttrols are expected to deliver multiple vlaues to the paramters. In the
advanced area of the paramameter properties I have the following settings
for the CourseID parameter:

ControlID: CourseTitles
ConvertEmptyStringToNull: True
DefaultValue: 148,453054...
Direction: Input
Name: CourseID
PropertyName: SelectedValue
Size: 0
Type: String

When I run the *test query* all multi-values are received and data is
returned.

(On the other hand when teh application is run only the first values of
multi-values are received and no data is returned)

This is the beginnig of the s/p

ALTER procedure [dbo].[usp_Report1]
(
@CourseID nvarchar(1000)
,@CourseDateRangeStart smalldatetime
,@CourseDateRangeEnd smalldatetime
,@HireDateRangeStart smalldatetime
,@HireDateRangeEnd smalldatetime
,@RoleID nvarchar(1000)
)

AS
Set NoCount ON

declare
@SQLQuery nvarchar(4000)
,@ParamDef nvarchar(300)

-- @RoleID & @CourseID are not parameterized because they are multi-valued

set @ParamDef =
N'
@CourseDateRangeStart smalldatetime
,@CourseDateRangeEnd smalldatetime
,@HireDateRangeStart smalldatetime
,@HireDateRangeEnd smalldatetime
'

set @SQLQuery =
N'select ...

I am supplying the parameters to the s/p in the followoing manners:

....
Where a.EntityID <> 0
and css.course_id in (' + @CourseID + N') and a.RoleID in (' + @RoleID + N')
and a.DateCreated between @HireDateRangeStart and @HireDateRangeEnd
and css.classdate between @CourseDateRangeStart and @CourseDateRangeEnd'

And I am executing the s/p internally like this;

EXECUTE sp_executesql
@SQLQuery,
@ParamDef,
@CourseDateRangeStart,
@CourseDateRangeEnd,
@HireDateRangeStart,
@HireDateRangeEnd

(Optionally you can view the other multi-values in my app after my sign-off
below)

How do I implement the join function for this? or how do I resolve the
probelm of only receivign single values?

in another post on this topic:
"How - parameter of a string of integers" you replied to me as follows:

> Erland's articles are very helpful for this issue, in which there are
> many methods.

I reviewed Erland's articles but saw no clear-cut way to implient them in
my situation.

You continued:
> Anyway I would like to simply show a method of
> using dynamic T-SQL here for your reference, for example:
> declare @strGroupID varchar(255)
> set @strGroupID = '1,2'
> exec ( N'select * from ProductSales
> where OrderID in (' + @strGroupID + N')')

This is what I tried to implement. I believe I am following it exactly, but
I am not receiving multi-values in the resulting query.

Do you have any suggestion?

Thank you,
Doug

=============================================================
There are some differences with my wire-up for passing multi-values between
ChecBoxLists.

Similarly there are three parts to this implementation; CheckBoxList,
SqlDataSource and StioredProcedure.

The CheckBoxList control references a SqlDataSource.

The SqlDataSource references the data connection and a SQL statement shown
below

EXEC [dbo].[usp_PopulateCourseTitles] @ClassTypes, @GroupID

The @GroupID is wired to a CheckBoxList which is expected to deliver
multiple vlaues. Here are the advanced properties

ControlID: CourseGroups
ConvertEmptyStringToNull: True
DefaultValue: 2,3,4,5....
Direction: Input
Name: GroupID
PropertyName: SelectedValue
Size: 0
Type: String

When I run the *test query* all multi-values are received and data is
returned.

(On the other hand when only the first values of multi-values are received
no data is returned)

The s/p is implemented internally almost exactly as the previous one. Here
is the parameter definition so you can see the data type I am using:

ALTER Procedure [doug].[usp_rptPopulateCourseTitlesSelectionM4]
( @ClassTypes nvarchar(10)
,@GroupID nvarchar(3000) )

and here is the execution within the s/p:

EXECUTE sp_executesql @SQLQuery

============================


From: "Charles Wang [MSFT]" on 24 Jun 2008 06:46
Hi Doug,
My applogies, I did not think that you were using a local report (.rdlc).
My suggestions of multi-value parameter does not apply to local report.
This situation is very different from server report. In this case, you
could not expect to use Report Parameter, instead you need to use ADO.NET
parameters. The following code is my test sample for querying a table named
T1 from the stored procedure usp_getT1.
===============================================
GTDataSet gTDataSet = new GTDataSet();
SqlConnection cn = new
SqlConnection("server=SHA-CHANGLIWANG;database=GT;integrated security =
SSPI;");
SqlDataAdapter adp = new SqlDataAdapter("", cn);
adp.SelectCommand = new SqlCommand("usp_getT1");
adp.SelectCommand.CommandType = CommandType.StoredProcedure;
adp.SelectCommand.Parameters.Add(new SqlParameter("@ID",
"2,3,4"));
adp.SelectCommand.Connection = cn;
adp.Fill(gTDataSet,"usp_getT1");

ReportDataSource rds = new ReportDataSource();
rds.Name = "GTDataSet_usp_getT1";
rds.Value = gTDataSet.Tables[0];
reportViewer1.LocalReport.DataSources.Clear();
this.reportViewer1.LocalReport.DataSources.Add(rds);

this.reportViewer1.RefreshReport();
========================================================
The logic of the above code is as following:
====================================
1. Create the dataset object which type is the dataset that is used in your
report;
2. Use SqlDataAdapter for filling data into your dataset;
3. Create a SqlCommand for executing your stored procedure, add the
parameters with their values to the SqlCommand object, and set it to the
SqlDataAdapter object;
4. Fill the dataset object by executing SqlDataAdapter.Fill;
5. Create a ReportDataSource and add it to your local report's data source
collection.
6. Refresh your report.
=====================================

My test stored procedure is as following:
====================================
create procedure usp_getT1
(
@id varchar(100)
)
as
declare @strSQL nvarchar(1000)
set @strSQL = N'SELECT * FROM T1 WHERE ID IN ('+@id+')';
exec sp_executeSQL @strSQL
======================================

My test steps are as following:
1. Create a Dataset(.xsd) item in your project, open the file, right click
the dataset design pane, click Add->TableAdapter, select the connection
string, click Next, check "Use existing stored procedures", click Next,
select your stored procedure to the "Select" field, click Next, click Next
and click Finish;
2. Right click the TableAdapter shap and click Add->Column to add the
columns returned from your stored procedure;
3. Design your report from this dataset;
4. Set the report path and run the above code to display the report in
reportviewer.

Hope this helps. If you have any other questions or concerns, please feel
free to let me know.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg(a)microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================




From: dbuchanan on 24 Jun 2008 17:54
Charles,

Please have left me with teh following questions reaised by your post.

> SqlDataAdapter adp = new SqlDataAdapter("", cn);
Should this really be an empty string?

> adp.SelectCommand.Parameters.Add(new SqlParameter("@ID", "2,3,4"));
How to I get the multiple parameters here?

> ReportDataSource rds = new ReportDataSource();
ReportDataSource is not recognized by intellisense. I cannot find it in
object browser. The only references I can find for it are in the context of
WinForms.

What can I do?
.....
==================================================
By the way..
While working on this I discovered another way to pass multiple values.

I gather the various values from the control delimit them by comas. Then
I remove the last comma and store the value in a hidden label (or it could
be stored in a session variable.)
Then I referred to that label in the SqlDataSource


Is there anything I lose using this?
==================================================

I would still like know how to process usnig code, but I need these question
answered.

Thanks
Doug


 |  Next  |  Last
Pages: 1 2
Prev: ReportViewer - size to contents
Next: How to Carry information from the web page into the report