passing parameter FROM REPORT TO WINDOWS APP

Hi all. Is it possible to pass parameter FROM Report to my windows application(C#) ? Report is made in Business Intelligence Project. I just want to retrieve the total row number to my windows appication. Is it possible? If yes, Can you provide codes for this? Thanks. Your help would be highly appreciated.

-Ron-

Yes you can pass parameter values from your report to your windows application.

Have a default value for your parameter which is the count of rows of the table and then use Values property of ReportParametersInfo class ro retrieve the value.

Shyam

|||Excluding creative approaches such as exporting the report to HTML and parsing the HTML payload, in general the user has to initiate an action explicitly in the report. Then, assuming you use the Windows Report Viewer, you can sink the event. See the hypelink example in this article.|||

Shyam - I have no idea of what you are saying. Can you provide me sample codes for this? thanks.

Teo Lachev - yes i am using windows report viewer. can't find the hyperlink example in link you gave.

Please Help me....

-Ron-

|||In the link I gave you, there is another link that will bring you to a DevX article. From there you can download a code sample that shows how to sink a hyperlink event.

passing parameter from asp.net application to reporting services report

Hi,

There is a .net application which has a screen with four options(text boxes)

Each of these should take to reports generated by SQL REp Services

This is the requirment

The School selected in the App should be passed on to the report

How should I pass the parameter in my report

so that when user selects a link or text box report for that particular school number is displayed

Thanks

sowree

Are you displaying the report in a report viewer control, or opening up a new browser window with the report URL?

BobP

|||

hi

the report is being viewed using report manager, so the report I have deployed to report server must be displayed when the user selects the menu selecting a school number in the asp app.

I need to know how i pass the parameter in my report rdl 's stored procedure to select the school number selected in the ASP application

Thanks

|||

You would create a parameter in the report rdl to accept the school number, and then use that parameter in the call to the stored proc.

For example: You create a parameter called @.SchoolNumber

exec spgSchoolInfo @.SchooNumber

To pass the parameter to the report in report manager, you would put the school number in the URL: (http://msdn2.microsoft.com/en-US/library/ms153586.aspx)

Example: http://server/reportserver?/Sales/Northwest/Employee Sales Report&rs:Command=Render&SchoolNumber=1234

HTH

BobP

Passing in variable number of parameters to a stored procedure

I am fairly new to MSSQL. Looking for a answer to a simple question.

I have a application which passes in lot of stuff from the UI into a stored procedure that has to be inserted into a MSSQL 2005 database. All the information that is passed will be spilt into 4 inserts hitting 4 seperate tables. All 4 inserts will be part of a stored procedure that have to be in one TRANSACTION. All but one insert are straight forward.

The structure of this table is something like

PKID
customerID
email address
....

customerID is not unique and can have n email addresses passed in. Each entry into this table when inserted into, will be passed n addresses (The number of email addresses passed is controlled by the user. It can be from 1..n). Constructing dynamic SQL is not an option. The SP to insert all the data is already in place. Typically I would just create the SP with IN parameters that I will use to insert into tables. In this case I can't do that since the number of email addresses passed is dynamic. My question is what's the best way to design this SP, where n email addresses are passed and each of them will have to be passed into a seperate insert statement? I can think of two ways to this...

Is there a way to create a variable length array as a IN parameter to capture the n email addresses coming in and use them to construct multiple insert statements?

Is it possible to get all the n email addresses as a comma seperated string? I know this is possible, but I am not sure how to parse this string and capture the n email addresses into variables before I construct them into insert statements.

Any other ways to do this? ThanksFrom a relational perspective, the best answer is to write a single stored procedure that takes one email address and processes it, and then call that procedure N times from your UI. This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.

If you decide to pursue your original idea and use a delimited (probably comma separated) list, you can use fSplit (http://www.dbforums.com/t997070.html) which I posted here ages ago. This is cleaner from the UI perspective, but it will eventually byte you because of the poor fit with relational databases.

-PatP|||This is because a relational database is based on relational algebra, and while that processes sets well as output, it doesn't process them nearly as easily as input.Really? I never knew that.

What's the basic reasoning around that Pat?|||Ok - I had a few mins. Played around.

CREATE TABLE InsertTable
(
MyPKFld VarChar(5) PRIMARY KEY
)

DECLARE @.i AS SmallInt

SET NOCOUNT ON

DECLARE @.Insert AS VarChar(2000)

--SELECT @.Insert = '10001,10022,20099,15073,28948,18737,90273,27910,3 7891'
SELECT @.Insert = '10001,10022,15073,18737,20099,27910,28948,37891,9 0273'

SELECT @.i = 1

DECLARE @.LoopUpper AS TinyInt

SELECT @.LoopUpper = (SELECT COUNT(*) FROM dbo.Split(@.Insert, ','))

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

DECLARE @.Start AS DateTime

SELECT @.Start = GETDATE()

WHILE @.i <= @.LoopUpper BEGIN

INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
WHERE ID = @.i
SELECT @.i = @.i + 1

END

PRINT 'LOOP takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'

DELETE
FROM InsertTable
WHERE MyPKFld > 10000

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

SELECT @.Start = GETDATE()

INSERT INTO InsertTable
SELECT Data
FROM dbo.Split(@.Insert, ',')
PRINT 'SET takes ' + CAST(DATEDIFF(ms, @.Start, GETDATE()) AS VarChar(4)) + 'ms'

DROP TABLE InsertTable
Basically tests looping and inserting one record on each pass and inserting a set. I typically get the set at between 1/3 and 2/3 the time the loop takes. What have I missed?|||What's the basic reasoning around that Pat?In a set based environment (where sets are fully supported by both the language itself and the implementation), there's no issue. SQL as a language doesn't support passing sets in (at least it doesn't yet, the new draft standard has basic support for them).

What you are doing is passing a complex argument (more than one atomic element in a single argument). That is outside of relational algebra altogether since it violates first normal form. The reason it runs faster is that you're trading multiple calls in a relational solution for processing complexity in a code oriented solution. It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.

-PatP|||Thanks Pat
I don't think I totally get you. The csv string is not normalised. However the function (or whatever code one might run) normalises the input. As far as SQL Server is concerned, it might always have been a set.

It certainly works, and at least for now it is faster, but eventually it will get to the point that it causes problems.By this do you mean it will be a bugger to maintain or do you mean there will be some sort of technical problem over time? If the latter - what would that be?

Grateful for the education as ever :)|||When you create a non-normalized interface like this, you've broken one of the fundamental building block "contracts" between a client and server. That relationship is either relational, or it isn't relational, and any non-relational interface makes the relationship between client and server non-relational.

This kind of change can make sense when you're implementing a different paradyme such as OOP. When you do that, you leave the relational world behind, so the rigorous "proofs" of behavior no longer have any meaning, but that happens any time you switch from one paradyme to another.

There are lots of really fundamental characteristics involved in relational processing. These make it predictable, which in turn makes it dependable. While relational technology certainly isn't the best possible way to do things, it is the best that I've found so far that is widely commercially supported and clearly understood by many professionals.

There are thousands of ways this can go wrong (and I've personally tried several hundred of those ;)). One example would be that you could have an application start out as a single server implementation, grow to use a cluster, expand further to use a cloud of replicating servers... When something goes worng :o in the process of getting data from a web server client into the data cloud, you have to start using network monitors to find the problem since you can't rely on which app/web server will initiate the conversation and which database server will process it. If you have bundled multiple calls into one and then rely on the server to parse them, you can no longer predict what the data "payload" will be exactly, so you need to start doing moderately sophisticated pattern matching. The process gets ugly, really fast.

Not everyone will face this specific problem. Given sufficient time though, I'll guarantee that you'll hit some problem related to the bundling effect. If you are making a paradyme shift, and that shift buys you something substantial in terms of coding time, support, ability to use new features, etc. then it is certainly worth considering. If all it buys you is a slight performance gain in exchange for the predictability of the pure relational model, I'd be hard pressed to "green light" this change.

-PatP|||Thanks Pat - appreciated :D

As it happens - I asked this question some time ago and got one yay and one nay from two of your esteemed peers hence why I jumped on your answer.
EDIT - plus my initial reading of your answer went against everything I thought I knew about SQL.sql

Passing in own Variable to print

I want to display some of the information not in the datasource selection but
through application passing in information. For example: personName,
reportName (dynamical defined depending on which button user click) and etc.
These variable only use in header or footer.
How can I pass these information to the report viwer? Any help is greatly
appreciated.I've done similar things using report parameters.
"Locus" <Locus@.discussions.microsoft.com> wrote in message
news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
>I want to display some of the information not in the datasource selection
>but
> through application passing in information. For example: personName,
> reportName (dynamical defined depending on which button user click) and
> etc.
> These variable only use in header or footer.
> How can I pass these information to the report viwer? Any help is greatly
> appreciated.|||Hi Bill, how you do it?
I tried adding parameters, it seems to automatically pop up to ask for input
value, not allowing me to passing in value programmatically.
-- Locus
"Bill Miller" wrote:
> I've done similar things using report parameters.
> "Locus" <Locus@.discussions.microsoft.com> wrote in message
> news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> >I want to display some of the information not in the datasource selection
> >but
> > through application passing in information. For example: personName,
> > reportName (dynamical defined depending on which button user click) and
> > etc.
> > These variable only use in header or footer.
> >
> > How can I pass these information to the report viwer? Any help is greatly
> > appreciated.
>
>|||Try setting the Parameters Hidden parameter to checked.
"Locus" wrote:
> Hi Bill, how you do it?
> I tried adding parameters, it seems to automatically pop up to ask for input
> value, not allowing me to passing in value programmatically.
> -- Locus
> "Bill Miller" wrote:
> > I've done similar things using report parameters.
> >
> > "Locus" <Locus@.discussions.microsoft.com> wrote in message
> > news:1FF52F89-D3F6-44C3-B42D-45BBABBDE2F0@.microsoft.com...
> > >I want to display some of the information not in the datasource selection
> > >but
> > > through application passing in information. For example: personName,
> > > reportName (dynamical defined depending on which button user click) and
> > > etc.
> > > These variable only use in header or footer.
> > >
> > > How can I pass these information to the report viwer? Any help is greatly
> > > appreciated.
> >
> >
> >

Passing in a Parameter by URL but Not displaying in tool bar

Currently we're using an application and the Report Viewer to "view"
our reports. One report that we need to generate is solely based on
the user's SalesRepCode. So what we want is that each user select a
report and then pass there SalesRepCode which we get from the
application, through the query string to Reporting Services to
generate that user's specific report. We don't want to give the user
the ability to use another user's SalesRepCode so we don't specify a
prompt for this parameter in the report parameters dialog boxes. This
is how I'm passing the SalesRepCode value to reporting services (I
know that the query string can be modified so the security on this is
weak. If you have an alternative solution I would love to hear it).
Is there anyway to pass in a parameter and not have it displayed on
the toolbar?
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
This is the error I'm receiving
The report parameter 'SalesRepCode' is read-only and cannot be
modified. (rsReadOnlyReportParameter) Get Online HelpIn order to pass a parameter via the query string but hide it in the toolbar
you will need to be running Reporting Services SP1. This is a new feature
in SP1.
If you have SP1 then edit the parameter properties of the report via the
Report Manager and check "Prompt User" and clear out the value of "Prompt
String" for your SalesRepCode parameter.
--
erik perez
www.solien.com
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> Currently we're using an application and the Report Viewer to "view"
> our reports. One report that we need to generate is solely based on
> the user's SalesRepCode. So what we want is that each user select a
> report and then pass there SalesRepCode which we get from the
> application, through the query string to Reporting Services to
> generate that user's specific report. We don't want to give the user
> the ability to use another user's SalesRepCode so we don't specify a
> prompt for this parameter in the report parameters dialog boxes. This
> is how I'm passing the SalesRepCode value to reporting services (I
> know that the query string can be modified so the security on this is
> weak. If you have an alternative solution I would love to hear it).
> Is there anyway to pass in a parameter and not have it displayed on
> the toolbar?
>
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> This is the error I'm receiving
> The report parameter 'SalesRepCode' is read-only and cannot be
> modified. (rsReadOnlyReportParameter) Get Online Help|||Hey Erik
I installed the SP1 and tried what you suggested. It still doesn't
work. When I go into the reports manager and select my report and
then select parameters from the left side the list of parameters
appears. For my parameter has default is checked but there is no
value in the default text box. Prompt user is unchecked and prompt
string is SalesRepCode:
I still get the same error stating that the parameter is readonly.
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> In order to pass a parameter via the query string but hide it in the toolbar
> you will need to be running Reporting Services SP1. This is a new feature
> in SP1.
> If you have SP1 then edit the parameter properties of the report via the
> Report Manager and check "Prompt User" and clear out the value of "Prompt
> String" for your SalesRepCode parameter.
> --
> erik perez
> www.solien.com
> "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > Currently we're using an application and the Report Viewer to "view"
> > our reports. One report that we need to generate is solely based on
> > the user's SalesRepCode. So what we want is that each user select a
> > report and then pass there SalesRepCode which we get from the
> > application, through the query string to Reporting Services to
> > generate that user's specific report. We don't want to give the user
> > the ability to use another user's SalesRepCode so we don't specify a
> > prompt for this parameter in the report parameters dialog boxes. This
> > is how I'm passing the SalesRepCode value to reporting services (I
> > know that the query string can be modified so the security on this is
> > weak. If you have an alternative solution I would love to hear it).
> > Is there anyway to pass in a parameter and not have it displayed on
> > the toolbar?
> >
> >
> http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> >
> > This is the error I'm receiving
> >
> > The report parameter 'SalesRepCode' is read-only and cannot be
> > modified. (rsReadOnlyReportParameter) Get Online Help|||Ok. Now:
1) Check the "Prompt User" checkbox so it is active
2) Remove the "SalesRepCode" value inside the "Prompt String" text field so
that the field is empty
3) Apply and try feeding the code via the query string.
--
erik perez
www.solien.com
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409020907.14bd20de@.posting.google.com...
> Hey Erik
> I installed the SP1 and tried what you suggested. It still doesn't
> work. When I go into the reports manager and select my report and
> then select parameters from the left side the list of parameters
> appears. For my parameter has default is checked but there is no
> value in the default text box. Prompt user is unchecked and prompt
> string is SalesRepCode:
> I still get the same error stating that the parameter is readonly.
> "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > In order to pass a parameter via the query string but hide it in the
toolbar
> > you will need to be running Reporting Services SP1. This is a new
feature
> > in SP1.
> > If you have SP1 then edit the parameter properties of the report via the
> > Report Manager and check "Prompt User" and clear out the value of
"Prompt
> > String" for your SalesRepCode parameter.
> >
> > --
> > erik perez
> > www.solien.com
> >
> > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > Currently we're using an application and the Report Viewer to "view"
> > > our reports. One report that we need to generate is solely based on
> > > the user's SalesRepCode. So what we want is that each user select a
> > > report and then pass there SalesRepCode which we get from the
> > > application, through the query string to Reporting Services to
> > > generate that user's specific report. We don't want to give the user
> > > the ability to use another user's SalesRepCode so we don't specify a
> > > prompt for this parameter in the report parameters dialog boxes. This
> > > is how I'm passing the SalesRepCode value to reporting services (I
> > > know that the query string can be modified so the security on this is
> > > weak. If you have an alternative solution I would love to hear it).
> > > Is there anyway to pass in a parameter and not have it displayed on
> > > the toolbar?
> > >
> > >
> >
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > >
> > > This is the error I'm receiving
> > >
> > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > modified. (rsReadOnlyReportParameter) Get Online Help|||I'm such an idiot, I should pay more attention to what I write. I
didn't finish my paragraph.
Erik
I did what you said the first time. I removed the text in the "Prompt
String" text field, but when I pressed the "Apply" button I received a
warning for every other text box saying "A value is required." Because
all of my default text boxes were blank. Now I have
calculations/expressions for those default values in my reports so I
didnt' know exactly what to do so just for a test I placed default
values there and tried to submit the value via query string.
Unfortunatly I still receive a read-only error when I submit it. I
have installed the SP, I changed the parameters in the report manager.
Could it be another setting or maybe its machine specific?
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> Ok. Now:
> 1) Check the "Prompt User" checkbox so it is active
> 2) Remove the "SalesRepCode" value inside the "Prompt String" text field so
> that the field is empty
> 3) Apply and try feeding the code via the query string.
> --
> erik perez
> www.solien.com
> "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > Hey Erik
> >
> > I installed the SP1 and tried what you suggested. It still doesn't
> > work. When I go into the reports manager and select my report and
> > then select parameters from the left side the list of parameters
> > appears. For my parameter has default is checked but there is no
> > value in the default text box. Prompt user is unchecked and prompt
> > string is SalesRepCode:
> >
> > I still get the same error stating that the parameter is readonly.
> >
> > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > In order to pass a parameter via the query string but hide it in the
> toolbar
> > > you will need to be running Reporting Services SP1. This is a new
> feature
> > > in SP1.
> > > If you have SP1 then edit the parameter properties of the report via the
> > > Report Manager and check "Prompt User" and clear out the value of
> "Prompt
> > > String" for your SalesRepCode parameter.
> > >
> > > --
> > > erik perez
> > > www.solien.com
> > >
> > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > Currently we're using an application and the Report Viewer to "view"
> > > > our reports. One report that we need to generate is solely based on
> > > > the user's SalesRepCode. So what we want is that each user select a
> > > > report and then pass there SalesRepCode which we get from the
> > > > application, through the query string to Reporting Services to
> > > > generate that user's specific report. We don't want to give the user
> > > > the ability to use another user's SalesRepCode so we don't specify a
> > > > prompt for this parameter in the report parameters dialog boxes. This
> > > > is how I'm passing the SalesRepCode value to reporting services (I
> > > > know that the query string can be modified so the security on this is
> > > > weak. If you have an alternative solution I would love to hear it).
> > > > Is there anyway to pass in a parameter and not have it displayed on
> > > > the toolbar?
> > > >
> > > >
> > >
> http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > >
> > > > This is the error I'm receiving
> > > >
> > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > modified. (rsReadOnlyReportParameter) Get Online Help|||Did you try unchecking the "Has Default" checkboxes for those fields you are
feeding defaults to in the report?
--
erik perez
www.solien.com
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409030527.271c9ada@.posting.google.com...
> I'm such an idiot, I should pay more attention to what I write. I
> didn't finish my paragraph.
> Erik
> I did what you said the first time. I removed the text in the "Prompt
> String" text field, but when I pressed the "Apply" button I received a
> warning for every other text box saying "A value is required." Because
> all of my default text boxes were blank. Now I have
> calculations/expressions for those default values in my reports so I
> didnt' know exactly what to do so just for a test I placed default
> values there and tried to submit the value via query string.
> Unfortunatly I still receive a read-only error when I submit it. I
> have installed the SP, I changed the parameters in the report manager.
> Could it be another setting or maybe its machine specific?
>
> "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> > Ok. Now:
> > 1) Check the "Prompt User" checkbox so it is active
> > 2) Remove the "SalesRepCode" value inside the "Prompt String" text field
so
> > that the field is empty
> > 3) Apply and try feeding the code via the query string.
> >
> > --
> > erik perez
> > www.solien.com
> >
> > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > > Hey Erik
> > >
> > > I installed the SP1 and tried what you suggested. It still doesn't
> > > work. When I go into the reports manager and select my report and
> > > then select parameters from the left side the list of parameters
> > > appears. For my parameter has default is checked but there is no
> > > value in the default text box. Prompt user is unchecked and prompt
> > > string is SalesRepCode:
> > >
> > > I still get the same error stating that the parameter is readonly.
> > >
> > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> > news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > > In order to pass a parameter via the query string but hide it in the
> > toolbar
> > > > you will need to be running Reporting Services SP1. This is a new
> > feature
> > > > in SP1.
> > > > If you have SP1 then edit the parameter properties of the report via
the
> > > > Report Manager and check "Prompt User" and clear out the value of
> > "Prompt
> > > > String" for your SalesRepCode parameter.
> > > >
> > > > --
> > > > erik perez
> > > > www.solien.com
> > > >
> > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > > Currently we're using an application and the Report Viewer to
"view"
> > > > > our reports. One report that we need to generate is solely based
on
> > > > > the user's SalesRepCode. So what we want is that each user select
a
> > > > > report and then pass there SalesRepCode which we get from the
> > > > > application, through the query string to Reporting Services to
> > > > > generate that user's specific report. We don't want to give the
user
> > > > > the ability to use another user's SalesRepCode so we don't specify
a
> > > > > prompt for this parameter in the report parameters dialog boxes.
This
> > > > > is how I'm passing the SalesRepCode value to reporting services (I
> > > > > know that the query string can be modified so the security on this
is
> > > > > weak. If you have an alternative solution I would love to hear
it).
> > > > > Is there anyway to pass in a parameter and not have it displayed
on
> > > > > the toolbar?
> > > > >
> > > > >
> > > >
> >
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > > >
> > > > > This is the error I'm receiving
> > > > >
> > > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > > modified. (rsReadOnlyReportParameter) Get Online Help|||GREAT THAT WORKED!!!! THANKS
BUT now is there any way to pass a parameter without using the query
string. The SalesRepCode I'm trying to pass is sensitive information
and I don't want it in the query string. If you've got any
suggestions I'd love to hear them.
Phoenix
"erik perez" <erik.nojunkmail.at.solien.com> wrote in message news:<##LUp9bkEHA.3872@.TK2MSFTNGP11.phx.gbl>...
> Did you try unchecking the "Has Default" checkboxes for those fields you are
> feeding defaults to in the report?
> --
> erik perez
> www.solien.com
> "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> news:1538bf33.0409030527.271c9ada@.posting.google.com...
> > I'm such an idiot, I should pay more attention to what I write. I
> > didn't finish my paragraph.
> >
> > Erik
> >
> > I did what you said the first time. I removed the text in the "Prompt
> > String" text field, but when I pressed the "Apply" button I received a
> > warning for every other text box saying "A value is required." Because
> > all of my default text boxes were blank. Now I have
> > calculations/expressions for those default values in my reports so I
> > didnt' know exactly what to do so just for a test I placed default
> > values there and tried to submit the value via query string.
> > Unfortunatly I still receive a read-only error when I submit it. I
> > have installed the SP, I changed the parameters in the report manager.
> > Could it be another setting or maybe its machine specific?
> >
> >
> > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> > > Ok. Now:
> > > 1) Check the "Prompt User" checkbox so it is active
> > > 2) Remove the "SalesRepCode" value inside the "Prompt String" text field
> so
> > > that the field is empty
> > > 3) Apply and try feeding the code via the query string.
> > >
> > > --
> > > erik perez
> > > www.solien.com
> > >
> > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > > > Hey Erik
> > > >
> > > > I installed the SP1 and tried what you suggested. It still doesn't
> > > > work. When I go into the reports manager and select my report and
> > > > then select parameters from the left side the list of parameters
> > > > appears. For my parameter has default is checked but there is no
> > > > value in the default text box. Prompt user is unchecked and prompt
> > > > string is SalesRepCode:
> > > >
> > > > I still get the same error stating that the parameter is readonly.
> > > >
> > > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > > > In order to pass a parameter via the query string but hide it in the
> toolbar
> > > > > you will need to be running Reporting Services SP1. This is a new
> feature
> > > > > in SP1.
> > > > > If you have SP1 then edit the parameter properties of the report via
> the
> > > > > Report Manager and check "Prompt User" and clear out the value of
> "Prompt
> > > > > String" for your SalesRepCode parameter.
> > > > >
> > > > > --
> > > > > erik perez
> > > > > www.solien.com
> > > > >
> > > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > > > Currently we're using an application and the Report Viewer to
> "view"
> > > > > > our reports. One report that we need to generate is solely based
> on
> > > > > > the user's SalesRepCode. So what we want is that each user select
> a
> > > > > > report and then pass there SalesRepCode which we get from the
> > > > > > application, through the query string to Reporting Services to
> > > > > > generate that user's specific report. We don't want to give the
> user
> > > > > > the ability to use another user's SalesRepCode so we don't specify
> a
> > > > > > prompt for this parameter in the report parameters dialog boxes.
> This
> > > > > > is how I'm passing the SalesRepCode value to reporting services (I
> > > > > > know that the query string can be modified so the security on this
> is
> > > > > > weak. If you have an alternative solution I would love to hear
> it).
> > > > > > Is there anyway to pass in a parameter and not have it displayed
> on
> > > > > > the toolbar?
> > > > > >
> > > > > >
> > > > >
> > >
> http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > > > >
> > > > > > This is the error I'm receiving
> > > > > >
> > > > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > > > modified. (rsReadOnlyReportParameter) Get Online Help|||If the salesrepcode can be tied to the logged in user you can use the global
variable User!userid and create a query to get the salesrepcode from a
table.
Bruce L-C
"Phoenix" <phoenixsilver@.hotmail.com> wrote in message
news:1538bf33.0409031308.1185e4ca@.posting.google.com...
> GREAT THAT WORKED!!!! THANKS
> BUT now is there any way to pass a parameter without using the query
> string. The SalesRepCode I'm trying to pass is sensitive information
> and I don't want it in the query string. If you've got any
> suggestions I'd love to hear them.
> Phoenix
>
> "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
news:<##LUp9bkEHA.3872@.TK2MSFTNGP11.phx.gbl>...
> > Did you try unchecking the "Has Default" checkboxes for those fields you
are
> > feeding defaults to in the report?
> >
> > --
> > erik perez
> > www.solien.com
> >
> > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > news:1538bf33.0409030527.271c9ada@.posting.google.com...
> > > I'm such an idiot, I should pay more attention to what I write. I
> > > didn't finish my paragraph.
> > >
> > > Erik
> > >
> > > I did what you said the first time. I removed the text in the "Prompt
> > > String" text field, but when I pressed the "Apply" button I received a
> > > warning for every other text box saying "A value is required." Because
> > > all of my default text boxes were blank. Now I have
> > > calculations/expressions for those default values in my reports so I
> > > didnt' know exactly what to do so just for a test I placed default
> > > values there and tried to submit the value via query string.
> > > Unfortunatly I still receive a read-only error when I submit it. I
> > > have installed the SP, I changed the parameters in the report manager.
> > > Could it be another setting or maybe its machine specific?
> > >
> > >
> > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> > news:<e44q8NSkEHA.2340@.TK2MSFTNGP11.phx.gbl>...
> > > > Ok. Now:
> > > > 1) Check the "Prompt User" checkbox so it is active
> > > > 2) Remove the "SalesRepCode" value inside the "Prompt String" text
field
> > so
> > > > that the field is empty
> > > > 3) Apply and try feeding the code via the query string.
> > > >
> > > > --
> > > > erik perez
> > > > www.solien.com
> > > >
> > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > news:1538bf33.0409020907.14bd20de@.posting.google.com...
> > > > > Hey Erik
> > > > >
> > > > > I installed the SP1 and tried what you suggested. It still
doesn't
> > > > > work. When I go into the reports manager and select my report and
> > > > > then select parameters from the left side the list of parameters
> > > > > appears. For my parameter has default is checked but there is no
> > > > > value in the default text box. Prompt user is unchecked and
prompt
> > > > > string is SalesRepCode:
> > > > >
> > > > > I still get the same error stating that the parameter is readonly.
> > > > >
> > > > > "erik perez" <erik.nojunkmail.at.solien.com> wrote in message
> > news:<#vJ2mPFkEHA.1136@.tk2msftngp13.phx.gbl>...
> > > > > > In order to pass a parameter via the query string but hide it in
the
> > toolbar
> > > > > > you will need to be running Reporting Services SP1. This is a
new
> > feature
> > > > > > in SP1.
> > > > > > If you have SP1 then edit the parameter properties of the report
via
> > the
> > > > > > Report Manager and check "Prompt User" and clear out the value
of
> > "Prompt
> > > > > > String" for your SalesRepCode parameter.
> > > > > >
> > > > > > --
> > > > > > erik perez
> > > > > > www.solien.com
> > > > > >
> > > > > > "Phoenix" <phoenixsilver@.hotmail.com> wrote in message
> > > > > > news:1538bf33.0409010922.4b053b0e@.posting.google.com...
> > > > > > > Currently we're using an application and the Report Viewer to
> > "view"
> > > > > > > our reports. One report that we need to generate is solely
based
> > on
> > > > > > > the user's SalesRepCode. So what we want is that each user se
lect
> > a
> > > > > > > report and then pass there SalesRepCode which we get from the
> > > > > > > application, through the query string to Reporting Services to
> > > > > > > generate that user's specific report. We don't want to give
the
> > user
> > > > > > > the ability to use another user's SalesRepCode so we don't
specify
> > a
> > > > > > > prompt for this parameter in the report parameters dialog
boxes.
> > This
> > > > > > > is how I'm passing the SalesRepCode value to reporting
services (I
> > > > > > > know that the query string can be modified so the security on
this
> > is
> > > > > > > weak. If you have an alternative solution I would love to
hear
> > it).
> > > > > > > Is there anyway to pass in a parameter and not have it
displayed
> > on
> > > > > > > the toolbar?
> > > > > > >
> > > > > > >
> > > > > >
> > > >
> >
http://localhost/ReportServer?/IRBReports/InstallationStatus&SalesRepCode=123ShowMe
> > > > > > >
> > > > > > > This is the error I'm receiving
> > > > > > >
> > > > > > > The report parameter 'SalesRepCode' is read-only and cannot be
> > > > > > > modified. (rsReadOnlyReportParameter) Get Online Help

Passing DML to From .NET SQLClient?

Hi All,
I am trying to figure out why the following fails and how to fix it:
I have a .NET Client application that passes parameters to stored procedures
and uses dynamic SQL that is passed to the Server. Everything is great. My
problem is if I want to send over to the server several DML in one string.
e.g.
----
USE Master
GO
IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
DROP PROC sp_XXXXX'
GO
Create Procedure sp_XXXXX
..
..
..
etc...
GO
----
Passing this type of string to SQL fails. Get error regarding the "GO" key
words, "Create Procedure Must be the first statement, etc...
Is it not possible to pass Multiple DML statements to SQL Server in one
string? If I send each DML statement alone, without the "GO" key word it
does work; but not all together.
Thanks for any insight,
John.GO is not a Transact-SQL statement. It's a batch delimiter used by tools
like OSQL and Query Analyzer. One method to execute scripts containing GOs
in your client app is to parse the script and execute batches individually
when a GO is encountered. See the link below for an example. Another
method is to use SQL-DMO to execute scripts.
[url]http://groups.google.com/group/comp.databases.ms-sqlserver/msg/3e7809e7eeb4cc95[/u
rl]
Hope this helps.
Dan Guzman
SQL Server MVP
"John" <jrugo@.patmedia.net> wrote in message
news:ujWlCQZzFHA.3124@.TK2MSFTNGP12.phx.gbl...
> Hi All,
> I am trying to figure out why the following fails and how to fix it:
> I have a .NET Client application that passes parameters to stored
> procedures and uses dynamic SQL that is passed to the Server. Everything
> is great. My problem is if I want to send over to the server several DML
> in one string.
> e.g.
> ----
> USE Master
> GO
> IF EXISTS(SELECT 1 FROM sysobjects WHERE name = 'sp_XXXXX' AND type = 'P')
> DROP PROC sp_XXXXX'
> GO
> Create Procedure sp_XXXXX
> ..
> ..
> ..
> etc...
> GO
> ----
> Passing this type of string to SQL fails. Get error regarding the "GO"
> key words, "Create Procedure Must be the first statement, etc...
> Is it not possible to pass Multiple DML statements to SQL Server in one
> string? If I send each DML statement alone, without the "GO" key word it
> does work; but not all together.
> Thanks for any insight,
> John.
>sql

Passing DB as parameter to stored procedure

I'm working with an application that has multiple databases for different
clients. I have a stored procedure that I would like to use for all of
these databases that is stored in a separate database. In the past, I have
passed the database name to the stored procedure and used dynamic sql to
build the select statements. In this case, I need to select to a temporary
table and then have a second query that uses the data in the temporary
table. With dynamic sql, the temporary table is removed after the first
query finishes, so this approach won't work.
Is there a way to switch databases in a stored procedure? In the foxpro
days, I think there was a Set Database procedure that would allow you to do
this.
TIA,
JohnSee: http://www.sommarskog.se/dynamic_sql.html
There is a section which explains getting data from another database.
Anithsql

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 an int parameter

Hello,

This is the way I call report viewer in my asp.net application.

this.ReportViewer1.ServerUrl=serverUrl;

this.ReportViewer1.ReportPath=repName;

this.ReportViewer1.Parameters=Microsoft.Samples.ReportingServices.ReportViewer.multiState.False;

this.ReportViewer1.SetQueryParameter("MyName",myname);

this.ReportViewer1.SetQueryParameter("MyID",myID);

MyName is a string and MyID is an int. Without MyID parameter it is working fine, however, when I add MyID it does not work, so how can I pass an int parameter to reporting services.

Thanks,

Cast its type as an integer

Passing ADO.Net Dataset to Reporting Service

Hi...
Is the Reporting Service support ADO.NET dataset that pass from application
(like Crystal Report)?
Thanks
NickyIn RS 2000 you would have to write a data processing extension. In RS 2005
you can get a winform and webform control via Visual Studio 2005. You can
use this control either in local or remote mode. Server mode uses reports on
the server and you get the whole Report Manager user interface. If you use
it in local mode you provide the recordset, you also handle all the user
interface for the parameters. I am using this some and it works well but it
is definitely more work than use Reporting Services either directly using
the portal (Report Manager) that ships with the product or using the new
controls in server mode.
The best way I believe to develop with reporting services is to consider RS
to be a service you are using. The service encapsulates and handles
everything you need. You provide the queries. If it is more difficult then
instead of queries use stored procedures. Let Reporting Services handle
things. RS does things in a different way that Crystal but once you get used
to it you should find that RS can be quite fast and powerful for you.
So, the answer is yes you can with the new controls but you should first
evaluate whether you should.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicky" <Nicky@.discussions.microsoft.com> wrote in message
news:D72F6395-C241-4014-A330-DAA933FFF3EB@.microsoft.com...
> Hi...
> Is the Reporting Service support ADO.NET dataset that pass from
> application
> (like Crystal Report)?
> Thanks
> Nicky|||Hi Bruce,
Thanks for reply.
My case is like this, I need to pass in a dataset from my application to my
report. and view the report over my application.
The problem is,
1. I need to write my own Xml to built that report by coding? Or, is that
other way I can built up the report like normal, and just pass the dataset to
the to my report?
2. How to open a report from the application without go through the Report
Manager?
3. Do you have any example about this 2 question?
Thanks
Regards,
Nicky|||You do not need to create the report on the fly. Much harder and
unnecessary. Two ways to do this. One, is you create a data processing
extension. This is non-trivial but definitely doable and works for 2000 and
2005. In 2005 the easiest thing you could do is to use the new controls from
Visual Studio in local mode. You hand the control a report and a dataset
(really a table from the dataset) and away it goes. There is more work
involved. For instance, dealing with subreports, any jump to reports etc.
As far as opening the report without going through report manager, you can
use URL integration, you can roll your own using web services or you can use
the new controls from VS 2005.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicky" <Nicky@.discussions.microsoft.com> wrote in message
news:3DCCBA4C-DF03-40B1-AF56-045DF85DF88B@.microsoft.com...
> Hi Bruce,
> Thanks for reply.
> My case is like this, I need to pass in a dataset from my application to
> my
> report. and view the report over my application.
> The problem is,
> 1. I need to write my own Xml to built that report by coding? Or, is that
> other way I can built up the report like normal, and just pass the dataset
> to
> the to my report?
> 2. How to open a report from the application without go through the Report
> Manager?
> 3. Do you have any example about this 2 question?
> Thanks
> Regards,
> Nicky
>|||Also, is there a reason that you have to hand the recordset to the report.
Unless you are doing some major business logic to obtain the recordset I
recomment having a query using parameters and then you pass the parameters
to the report (you can do this via the controls (winform and webform) or
using URL integration. RS retrieves and renders the data. Many times people
want to pass the recordset just because that is how they are used to doing
things. It is better to solve the problem using RS the way it is designed if
at all possible. No reason to make matters more complicated. RS is designed
to be tightly integrated with your app, that is not a problem.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Nicky" <Nicky@.discussions.microsoft.com> wrote in message
news:3DCCBA4C-DF03-40B1-AF56-045DF85DF88B@.microsoft.com...
> Hi Bruce,
> Thanks for reply.
> My case is like this, I need to pass in a dataset from my application to
> my
> report. and view the report over my application.
> The problem is,
> 1. I need to write my own Xml to built that report by coding? Or, is that
> other way I can built up the report like normal, and just pass the dataset
> to
> the to my report?
> 2. How to open a report from the application without go through the Report
> Manager?
> 3. Do you have any example about this 2 question?
> Thanks
> Regards,
> Nicky
>|||Hi Bruce
thanks for your recommendation. But, I need to pass in my own dataset
through my application because it include too many business logic.
Anywhere, i have able to find the example:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5
thanks.
Nicky

Passing a WHERE clause

I want to build a SQL WHERE (and possibly ORDER BY) clause
in a c# application and then pass them as parameters to a report that would
be used by a SQL Server stored procedure.
can anyone tell me how to do that?
Syntax for building it?
maybe a link to an example?
thanks
--
Lucas DargisYou have a very good sample available in samples. search for forms
authentication and open the custom security c# file and see the code you will
get good idea about how to write.
Amarnath
"akbikerboy" wrote:
> I want to build a SQL WHERE (and possibly ORDER BY) clause
> in a c# application and then pass them as parameters to a report that would
> be used by a SQL Server stored procedure.
> can anyone tell me how to do that?
> Syntax for building it?
> maybe a link to an example?
> thanks
> --
> Lucas Dargis|||could you be more specific?
Are you talking about MSDN or in VS?
I can't find what you are talking about
thanks
--
Lucas Dargis
"Amarnath" wrote:
> You have a very good sample available in samples. search for forms
> authentication and open the custom security c# file and see the code you will
> get good idea about how to write.
> Amarnath
> "akbikerboy" wrote:
> > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > in a c# application and then pass them as parameters to a report that would
> > be used by a SQL Server stored procedure.
> >
> > can anyone tell me how to do that?
> > Syntax for building it?
> > maybe a link to an example?
> >
> > thanks
> > --
> > Lucas Dargis|||sql server online help samples
Amarnath
"akbikerboy" wrote:
> could you be more specific?
> Are you talking about MSDN or in VS?
> I can't find what you are talking about
>
> thanks
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > You have a very good sample available in samples. search for forms
> > authentication and open the custom security c# file and see the code you will
> > get good idea about how to write.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > in a c# application and then pass them as parameters to a report that would
> > > be used by a SQL Server stored procedure.
> > >
> > > can anyone tell me how to do that?
> > > Syntax for building it?
> > > maybe a link to an example?
> > >
> > > thanks
> > > --
> > > Lucas Dargis|||I looked all over and i can't find what your are talking about. do you have a
link to it?
--
Lucas Dargis
"Amarnath" wrote:
> sql server online help samples
> Amarnath
> "akbikerboy" wrote:
> > could you be more specific?
> > Are you talking about MSDN or in VS?
> > I can't find what you are talking about
> >
> >
> > thanks
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > You have a very good sample available in samples. search for forms
> > > authentication and open the custom security c# file and see the code you will
> > > get good idea about how to write.
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > in a c# application and then pass them as parameters to a report that would
> > > > be used by a SQL Server stored procedure.
> > > >
> > > > can anyone tell me how to do that?
> > > > Syntax for building it?
> > > > maybe a link to an example?
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis|||Just go to search in online help and search for forms authentication and if
you have installed the samples, go to the samples folder and see the forms
authentication example code.
Amarnath
"akbikerboy" wrote:
> I looked all over and i can't find what your are talking about. do you have a
> link to it?
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > sql server online help samples
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > could you be more specific?
> > > Are you talking about MSDN or in VS?
> > > I can't find what you are talking about
> > >
> > >
> > > thanks
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > You have a very good sample available in samples. search for forms
> > > > authentication and open the custom security c# file and see the code you will
> > > > get good idea about how to write.
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > in a c# application and then pass them as parameters to a report that would
> > > > > be used by a SQL Server stored procedure.
> > > > >
> > > > > can anyone tell me how to do that?
> > > > > Syntax for building it?
> > > > > maybe a link to an example?
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis|||there are a million articles about forms authentication. and i don't see what
that has to do with passing a where clause from an application to RS...
thanks for trying though.
--
Lucas Dargis
"Amarnath" wrote:
> Just go to search in online help and search for forms authentication and if
> you have installed the samples, go to the samples folder and see the forms
> authentication example code.
> Amarnath
> "akbikerboy" wrote:
> > I looked all over and i can't find what your are talking about. do you have a
> > link to it?
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > sql server online help samples
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > could you be more specific?
> > > > Are you talking about MSDN or in VS?
> > > > I can't find what you are talking about
> > > >
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > You have a very good sample available in samples. search for forms
> > > > > authentication and open the custom security c# file and see the code you will
> > > > > get good idea about how to write.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > be used by a SQL Server stored procedure.
> > > > > >
> > > > > > can anyone tell me how to do that?
> > > > > > Syntax for building it?
> > > > > > maybe a link to an example?
> > > > > >
> > > > > > thanks
> > > > > > --
> > > > > > Lucas Dargis|||dude, I didn't ask you to type it on google to get million option. By the way
I was mentioning that if you have sample installed, you can have a look at
the sample code, I repeat the code, so that you will get some idea for
writing code in c# or VB.net.
Amarnath
"akbikerboy" wrote:
> there are a million articles about forms authentication. and i don't see what
> that has to do with passing a where clause from an application to RS...
> thanks for trying though.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Just go to search in online help and search for forms authentication and if
> > you have installed the samples, go to the samples folder and see the forms
> > authentication example code.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I looked all over and i can't find what your are talking about. do you have a
> > > link to it?
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > sql server online help samples
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > could you be more specific?
> > > > > Are you talking about MSDN or in VS?
> > > > > I can't find what you are talking about
> > > > >
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > You have a very good sample available in samples. search for forms
> > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > get good idea about how to write.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > be used by a SQL Server stored procedure.
> > > > > > >
> > > > > > > can anyone tell me how to do that?
> > > > > > > Syntax for building it?
> > > > > > > maybe a link to an example?
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis|||Ok, to guide you precisly,
If you have samples installed in your m/c, go to
C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
accordingly)
you can see a custom security solution file, click on that and open. search
for
"AuthenticationExtension.cs" file and click on view code and search for
"VerifyUser"
In that code you can see how it is defined for stored procedure using
commandtype
and parameter.add is used. instead of "@.username" build your where clause by
defining a variable and start adding all the conditions and pass it on using
parameter.add.
I think this should give some idea about how to write it.
Please let me know if you have any problems
Amarnath
"akbikerboy" wrote:
> there are a million articles about forms authentication. and i don't see what
> that has to do with passing a where clause from an application to RS...
> thanks for trying though.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Just go to search in online help and search for forms authentication and if
> > you have installed the samples, go to the samples folder and see the forms
> > authentication example code.
> >
> > Amarnath
> >
> > "akbikerboy" wrote:
> >
> > > I looked all over and i can't find what your are talking about. do you have a
> > > link to it?
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > sql server online help samples
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > could you be more specific?
> > > > > Are you talking about MSDN or in VS?
> > > > > I can't find what you are talking about
> > > > >
> > > > >
> > > > > thanks
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > You have a very good sample available in samples. search for forms
> > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > get good idea about how to write.
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > be used by a SQL Server stored procedure.
> > > > > > >
> > > > > > > can anyone tell me how to do that?
> > > > > > > Syntax for building it?
> > > > > > > maybe a link to an example?
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis|||Well thanks for pointing that out precisely.
I guess I should have been more specific. I know how to create and pass a
parameter. Thatâ's not what I was asking. My question was how to write and
pass a 'where' clause specifically. What syntax to use.
For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
= 99874"
In that string, I have slashes, and quotes. When I try to pass this string
as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
that I know and nothing works.
Thanks again.
--
Lucas Dargis
"Amarnath" wrote:
> Ok, to guide you precisly,
> If you have samples installed in your m/c, go to
> C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> accordingly)
> you can see a custom security solution file, click on that and open. search
> for
> "AuthenticationExtension.cs" file and click on view code and search for
> "VerifyUser"
> In that code you can see how it is defined for stored procedure using
> commandtype
> and parameter.add is used. instead of "@.username" build your where clause by
> defining a variable and start adding all the conditions and pass it on using
> parameter.add.
> I think this should give some idea about how to write it.
> Please let me know if you have any problems
> Amarnath
>
> "akbikerboy" wrote:
> > there are a million articles about forms authentication. and i don't see what
> > that has to do with passing a where clause from an application to RS...
> >
> > thanks for trying though.
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > Just go to search in online help and search for forms authentication and if
> > > you have installed the samples, go to the samples folder and see the forms
> > > authentication example code.
> > >
> > > Amarnath
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I looked all over and i can't find what your are talking about. do you have a
> > > > link to it?
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > sql server online help samples
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > could you be more specific?
> > > > > > Are you talking about MSDN or in VS?
> > > > > > I can't find what you are talking about
> > > > > >
> > > > > >
> > > > > > thanks
> > > > > > --
> > > > > > Lucas Dargis
> > > > > >
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > get good idea about how to write.
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > > "akbikerboy" wrote:
> > > > > > >
> > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > >
> > > > > > > > can anyone tell me how to do that?
> > > > > > > > Syntax for building it?
> > > > > > > > maybe a link to an example?
> > > > > > > >
> > > > > > > > thanks
> > > > > > > > --
> > > > > > > > Lucas Dargis|||Oh Ok, so you meant this. the same way, how you have used in the example,
with double quotes and single quotes. if doesnt work try concatenating some
thing like this. e.g "Where aa = " + " ' " + "01/01/2007" + " ' "
Let me know whether it is working ?
Amarnath
"akbikerboy" wrote:
> Well thanks for pointing that out precisely.
> I guess I should have been more specific. I know how to create and pass a
> parameter. Thatâ's not what I was asking. My question was how to write and
> pass a 'where' clause specifically. What syntax to use.
> For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
> = 99874"
> In that string, I have slashes, and quotes. When I try to pass this string
> as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
> that I know and nothing works.
> Thanks again.
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > Ok, to guide you precisly,
> >
> > If you have samples installed in your m/c, go to
> >
> > C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> > Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> > accordingly)
> >
> > you can see a custom security solution file, click on that and open. search
> > for
> > "AuthenticationExtension.cs" file and click on view code and search for
> > "VerifyUser"
> >
> > In that code you can see how it is defined for stored procedure using
> > commandtype
> > and parameter.add is used. instead of "@.username" build your where clause by
> > defining a variable and start adding all the conditions and pass it on using
> > parameter.add.
> >
> > I think this should give some idea about how to write it.
> > Please let me know if you have any problems
> >
> > Amarnath
> >
> >
> > "akbikerboy" wrote:
> >
> > > there are a million articles about forms authentication. and i don't see what
> > > that has to do with passing a where clause from an application to RS...
> > >
> > > thanks for trying though.
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Just go to search in online help and search for forms authentication and if
> > > > you have installed the samples, go to the samples folder and see the forms
> > > > authentication example code.
> > > >
> > > > Amarnath
> > > >
> > > > "akbikerboy" wrote:
> > > >
> > > > > I looked all over and i can't find what your are talking about. do you have a
> > > > > link to it?
> > > > > --
> > > > > Lucas Dargis
> > > > >
> > > > >
> > > > > "Amarnath" wrote:
> > > > >
> > > > > > sql server online help samples
> > > > > >
> > > > > > Amarnath
> > > > > >
> > > > > > "akbikerboy" wrote:
> > > > > >
> > > > > > > could you be more specific?
> > > > > > > Are you talking about MSDN or in VS?
> > > > > > > I can't find what you are talking about
> > > > > > >
> > > > > > >
> > > > > > > thanks
> > > > > > > --
> > > > > > > Lucas Dargis
> > > > > > >
> > > > > > >
> > > > > > > "Amarnath" wrote:
> > > > > > >
> > > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > > get good idea about how to write.
> > > > > > > >
> > > > > > > > Amarnath
> > > > > > > >
> > > > > > > > "akbikerboy" wrote:
> > > > > > > >
> > > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > > >
> > > > > > > > > can anyone tell me how to do that?
> > > > > > > > > Syntax for building it?
> > > > > > > > > maybe a link to an example?
> > > > > > > > >
> > > > > > > > > thanks
> > > > > > > > > --
> > > > > > > > > Lucas Dargis|||so for the query i tried
select....
where @.where
parameter: LocationCode + "'" + LA + "'"
I am getting the following error
An error occurred whild executing the query
An expression of non-boolean type specified in a context where a condition
is expected, near @.where
error:4145
I am running the query in the report designed in the Data tab
thanks
--
Lucas Dargis
"akbikerboy" wrote:
> I want to build a SQL WHERE (and possibly ORDER BY) clause
> in a c# application and then pass them as parameters to a report that would
> be used by a SQL Server stored procedure.
> can anyone tell me how to do that?
> Syntax for building it?
> maybe a link to an example?
> thanks
> --
> Lucas Dargis|||For the query i use
SELECT...
WHERE @.where
parameter: declared as a string
LocationCode = + "'" + LA + "'"
and i got the following error:
An error occurred whild executing the query.
An expression of non-boolean type specified in a contex where a condition is
expected, near '@.where'.
Error: 4145
I am running the query in the Data tab in the Report Builder
thanks
--
Lucas Dargis
"Amarnath" wrote:
> Oh Ok, so you meant this. the same way, how you have used in the example,
> with double quotes and single quotes. if doesnt work try concatenating some
> thing like this. e.g "Where aa = " + " ' " + "01/01/2007" + " ' "
> Let me know whether it is working ?
> Amarnath
> "akbikerboy" wrote:
> > Well thanks for pointing that out precisely.
> >
> > I guess I should have been more specific. I know how to create and pass a
> > parameter. Thatâ's not what I was asking. My question was how to write and
> > pass a 'where' clause specifically. What syntax to use.
> >
> > For example: "WHERE EventDate = â'01/01/2007â' AND LocationCode = 'LA' AND zip
> > = 99874"
> >
> > In that string, I have slashes, and quotes. When I try to pass this string
> > as a parameter, I get errors. Iâ've tried every type of â'escapeâ' technique
> > that I know and nothing works.
> >
> > Thanks again.
> >
> > --
> > Lucas Dargis
> >
> >
> > "Amarnath" wrote:
> >
> > > Ok, to guide you precisly,
> > >
> > > If you have samples installed in your m/c, go to
> > >
> > > C:\Program Files\Microsoft SQL Server\90\Samples\Reporting
> > > Services\Extension Samples\FormsAuthentication Sample\cs (change folder name
> > > accordingly)
> > >
> > > you can see a custom security solution file, click on that and open. search
> > > for
> > > "AuthenticationExtension.cs" file and click on view code and search for
> > > "VerifyUser"
> > >
> > > In that code you can see how it is defined for stored procedure using
> > > commandtype
> > > and parameter.add is used. instead of "@.username" build your where clause by
> > > defining a variable and start adding all the conditions and pass it on using
> > > parameter.add.
> > >
> > > I think this should give some idea about how to write it.
> > > Please let me know if you have any problems
> > >
> > > Amarnath
> > >
> > >
> > > "akbikerboy" wrote:
> > >
> > > > there are a million articles about forms authentication. and i don't see what
> > > > that has to do with passing a where clause from an application to RS...
> > > >
> > > > thanks for trying though.
> > > > --
> > > > Lucas Dargis
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Just go to search in online help and search for forms authentication and if
> > > > > you have installed the samples, go to the samples folder and see the forms
> > > > > authentication example code.
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "akbikerboy" wrote:
> > > > >
> > > > > > I looked all over and i can't find what your are talking about. do you have a
> > > > > > link to it?
> > > > > > --
> > > > > > Lucas Dargis
> > > > > >
> > > > > >
> > > > > > "Amarnath" wrote:
> > > > > >
> > > > > > > sql server online help samples
> > > > > > >
> > > > > > > Amarnath
> > > > > > >
> > > > > > > "akbikerboy" wrote:
> > > > > > >
> > > > > > > > could you be more specific?
> > > > > > > > Are you talking about MSDN or in VS?
> > > > > > > > I can't find what you are talking about
> > > > > > > >
> > > > > > > >
> > > > > > > > thanks
> > > > > > > > --
> > > > > > > > Lucas Dargis
> > > > > > > >
> > > > > > > >
> > > > > > > > "Amarnath" wrote:
> > > > > > > >
> > > > > > > > > You have a very good sample available in samples. search for forms
> > > > > > > > > authentication and open the custom security c# file and see the code you will
> > > > > > > > > get good idea about how to write.
> > > > > > > > >
> > > > > > > > > Amarnath
> > > > > > > > >
> > > > > > > > > "akbikerboy" wrote:
> > > > > > > > >
> > > > > > > > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > > > > > > > in a c# application and then pass them as parameters to a report that would
> > > > > > > > > > be used by a SQL Server stored procedure.
> > > > > > > > > >
> > > > > > > > > > can anyone tell me how to do that?
> > > > > > > > > > Syntax for building it?
> > > > > > > > > > maybe a link to an example?
> > > > > > > > > >
> > > > > > > > > > thanks
> > > > > > > > > > --
> > > > > > > > > > Lucas Dargis|||By two way you can solve this.
1. use stored proc to concatenate all the "select", includng the values
which comes from parameter ie where clause
2. By directly giving in the data tab..
e.g I have taken some samples from adventure... it goes like this..
paste this code in the data tab it executes successfully..
declare @.s as nvarchar(1000)
set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
Person.Contact WHERE ' + @.w
exec sp_executesql @.s
the @.w is what I entered " as (Title = 'Mr.').
PS. "where", is the reserved word, so name @.where to some other name.
So basically what i meant here is to built using stored proc or use string
and concatenate with your parameter.
Amarnath
"akbikerboy" wrote:
> so for the query i tried
> select....
> where @.where
> parameter: LocationCode + "'" + LA + "'"
> I am getting the following error
> An error occurred whild executing the query
> An expression of non-boolean type specified in a context where a condition
> is expected, near @.where
> error:4145
> I am running the query in the report designed in the Data tab
> thanks
> --
> Lucas Dargis
>
> "akbikerboy" wrote:
> > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > in a c# application and then pass them as parameters to a report that would
> > be used by a SQL Server stored procedure.
> >
> > can anyone tell me how to do that?
> > Syntax for building it?
> > maybe a link to an example?
> >
> > thanks
> > --
> > Lucas Dargis|||AWESOME! very clever.
well your second solution is just what i was looking for, however, it brings
up another problem.
Since the query is now created in a variable, RS doesn't recognize the
fields in the layout tab. how do i reference the fields so i can create the
report?
thanks again
--
Lucas Dargis
"Amarnath" wrote:
> By two way you can solve this.
> 1. use stored proc to concatenate all the "select", includng the values
> which comes from parameter ie where clause
> 2. By directly giving in the data tab..
> e.g I have taken some samples from adventure... it goes like this..
> paste this code in the data tab it executes successfully..
> declare @.s as nvarchar(1000)
> set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
> Person.Contact WHERE ' + @.w
> exec sp_executesql @.s
> the @.w is what I entered " as (Title = 'Mr.').
> PS. "where", is the reserved word, so name @.where to some other name.
> So basically what i meant here is to built using stored proc or use string
> and concatenate with your parameter.
> Amarnath
>
> "akbikerboy" wrote:
> > so for the query i tried
> > select....
> > where @.where
> >
> > parameter: LocationCode + "'" + LA + "'"
> >
> > I am getting the following error
> >
> > An error occurred whild executing the query
> > An expression of non-boolean type specified in a context where a condition
> > is expected, near @.where
> >
> > error:4145
> >
> > I am running the query in the report designed in the Data tab
> >
> > thanks
> > --
> > Lucas Dargis
> >
> >
> > "akbikerboy" wrote:
> >
> > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > in a c# application and then pass them as parameters to a report that would
> > > be used by a SQL Server stored procedure.
> > >
> > > can anyone tell me how to do that?
> > > Syntax for building it?
> > > maybe a link to an example?
> > >
> > > thanks
> > > --
> > > Lucas Dargis|||So you can forget my last question. I reread your last post and figured it out.
i put the sql code you provided me into a stored proc and called that proc
while sending the Where Clause as a parameter and it worked PERFECTLY!!!
i can't thank you enough. i've been stuck on this for a few weeks.
Lucas Dargis
"akbikerboy" wrote:
> AWESOME! very clever.
> well your second solution is just what i was looking for, however, it brings
> up another problem.
> Since the query is now created in a variable, RS doesn't recognize the
> fields in the layout tab. how do i reference the fields so i can create the
> report?
> thanks again
> --
> Lucas Dargis
>
> "Amarnath" wrote:
> > By two way you can solve this.
> > 1. use stored proc to concatenate all the "select", includng the values
> > which comes from parameter ie where clause
> > 2. By directly giving in the data tab..
> > e.g I have taken some samples from adventure... it goes like this..
> >
> > paste this code in the data tab it executes successfully..
> >
> > declare @.s as nvarchar(1000)
> > set @.s = 'SELECT ContactID, NameStyle, Title, FirstName, FROM
> > Person.Contact WHERE ' + @.w
> > exec sp_executesql @.s
> >
> > the @.w is what I entered " as (Title = 'Mr.').
> >
> > PS. "where", is the reserved word, so name @.where to some other name.
> > So basically what i meant here is to built using stored proc or use string
> > and concatenate with your parameter.
> >
> > Amarnath
> >
> >
> > "akbikerboy" wrote:
> >
> > > so for the query i tried
> > > select....
> > > where @.where
> > >
> > > parameter: LocationCode + "'" + LA + "'"
> > >
> > > I am getting the following error
> > >
> > > An error occurred whild executing the query
> > > An expression of non-boolean type specified in a context where a condition
> > > is expected, near @.where
> > >
> > > error:4145
> > >
> > > I am running the query in the report designed in the Data tab
> > >
> > > thanks
> > > --
> > > Lucas Dargis
> > >
> > >
> > > "akbikerboy" wrote:
> > >
> > > > I want to build a SQL WHERE (and possibly ORDER BY) clause
> > > > in a c# application and then pass them as parameters to a report that would
> > > > be used by a SQL Server stored procedure.
> > > >
> > > > can anyone tell me how to do that?
> > > > Syntax for building it?
> > > > maybe a link to an example?
> > > >
> > > > thanks
> > > > --
> > > > Lucas Dargis

Passing a list/array to an SQL Server stored procedure 2005

Hi, I m using sql 2005 as a back end in my application...

I am useing Store procedure..for my data in grid..

ALTERPROCEDURE [dbo].[ProductZoneSearct]

(

@.Productidchar(8),
@.Pronamechar(8),
@.radiusint,
@.modevarchar(5)='M',
@.Zonenvarchar(1000),

)

AS
SETNOCOUNTON;
Create Table #Product (ProductID int, TimeEntered datetime, DateAvailable datetime, Productname varchar(80), City varchar(50), State char(4),Miles decimal, Payment varchar(40),UserID int, Phone varchar(15))

Insert #Product
Select ProductID , TimeEntered, DateAvailable, Productname ,City,State,miles,Payment
,Miles, UserID, Daily, Phone
From [tblproduct]
Where city IN (@.Zone)

Select ProductID TimeEntered, DateAvailable, Productname City,State,miles,Payment
,Miles, U.Phone As phoneNumber, Company, , L.Phone As cmpPhone
From #Product As L
Left Join (Select UserID, Company, Phone, From [User]) As U On U.UserID = L.UserID
Order By DateAvailable

if i pass value in"where city in (@.Zone)" and@.Zone ='CA','AD','MH' then it can not get any result..but if writewhere city in ('CA','AD','MH') then it give me perfact result..

I tried to below syntax also but in no any user
Where city IN ('+@.Zone+')

In short if i pass value through varibale (@.Zone) then i cant get result...but if i put direct value in query then only getting result..can anybody tell me what is problem ?

Please Hel[p me !!!

Thank you !!!

Check out this blog post:

Passing lists to SQL Server 2005 with XML Parameters

|||

Hmmm... you may be better off writing an object data source and filtering the data after the select. Otherwise, have a look at sp_executesql or somesuch...

|||

Problem is If i pass only one value into variable then it gives me result but if i pass more than one value then it wount give me result.
Example..If i pass @.Zone='KS' then it works fine but if i pass @.Zone='KS','MS' Then it wong give me data...coz "," (comma) .seperated..may be it count after comma seprateion its a new value...or i dont know why i m not getting result

Please help me

Thank you & Regards.

|||

Sorry I am not getting you..:-((

|||

Instead of passing the parameters to the stored procedure, you can just select everything, and filter the data after. Look at FilterParameters and FilterExpression for SQL DataSource. I think you can set the FilterExpression="WHERE Region IN (@.Region)", and add @.Region as a FilterParameter, or something like that. Otherwise, you can make an ObjectDataSource that executes the stored procedure, and you can filter the resultset inside there however you want.

EDIT

Well, the whole Filter parameter thing with an "IN" clause isn't working for me... however, I think you can handle the datasource's filtering event to build the filter expression.