Dynamic Operator Help

Hi,

I'm trying to build a form that will allow users to choose their own parameters for the Select statement in the SqlDataSource. These results would then be displayed back to a GridView control. The only problem I am having is figuring out how to allow them to choose the operator (=, <, >, <>, etc) from a dropdown list. Does anyone have any suggestions on how to do this with a SqlDataSource control? It is probably something simple and any help would be much appreciated.

Thanks

You would just build the statement dynamically and set the SqlDataSource.SelectCommand propertyhttp://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selectcommand.aspx

But, be advise that using dynamic SQL is frought with danger due to sql injection attacks. You will really need to scrub your input. Seehttp://en.wikipedia.org/wiki/SQL_Injection or just google "sql injection" for many, many articles on this topic

|||

Dbland thanks for the reply. I do understand that the select command will be built dynamically and this is what I have done. Here is the Data Source.

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:UsersConnectionString %>"
ProviderName="<%$ ConnectionStrings:UsersConnectionString.ProviderName %>" SelectCommand="SELECT Username, Email, Active, BuildingNum, UserRole, UserID FROM UserInfo WHERE (BuildingNum = @.BuildingNum)">
<SelectParameters>
<asp:ControlParameter ControlID="BuildingDropDown" Name="BuildingNum" PropertyName="Text" />
</SelectParameters>

I would like to parameterize the operator to a dropdown control which would have the =, >, < values options. That way people can select any building less than, greater than or, equal to a building number.

So my question is how do I allow this part of the select statement, "=", to be determined and populated from a users form input. (BuildingNum = @.BuildingNum)">

Is this possible?

|||

In your code you will have to take the value of the drop down and build your statement, eg

string sql = .................

SqlDataSource.SelectCommand = sql;

I'm not exactly sure in what event you would do this but I'm sure there is one just before the SelectCommand gets executed

Is this what you were after?

|||

Can you give me or point me to an example of how to do this?

|||

Let's say you have a Submit button. When they click that you build up your sql statement and set the sqldatasource as follows:

SqlDataSource.SelectCommand = sql;

Or you can put it in one of the data datasource events, like the Selecting event which occurs just before the select is done (http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.selecting.aspx). I'm not sure which event gives you the flexibility you want, you'll have to play read about them and play around with it.

|||

Thanks for all your help dbland. I took your advise and set the select command upon the button click event. It seems to work out well. Thanks again.

dynamic datasource

Our application is used by multiple companies. Each company has its own SQL
Server 2k database. Each company has access to the same list of reports in
SRS. We would like to deploy one physical copy of each report in the report
database for simplest maintenance of RDL files. Our application is deployed
in a web browser as an ASP application.
When a user in company A accesses Report 2 we need to render the report with
datasource parameters that will connect him to the correct db. At the same
time, users in companies B, C and D may be running the same report, each with
the correct datasource parameters for their companies.
Hence, as a report is requested by each user, we need to set up (or connect
to) the correct datasource parameters dynamically and then render the report
for the user.
Is there a way to do this with SRS? If multiple ways I would appreciate
knowing some of the options. If there are good books that discuss these
options I would appreciate references to them as well.
TIAHi joe,
We do something similar. We have multiple clients who use their own SQL
Database. We are using a custom data source (using ADO.net dataset). There
are numerous articles on the web which show you how to create one. Its pretty
simple and straightforward. The connection string is dynamically formed based
on the SQL Database name passed in as a parameter. This way we are able to
support multiple clients using a single copy of the rdl files.
Thanks,
George Tharakan
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Are you using the Report Manager or are you integrating with your own
application using either URL integration or Web services?
Assuming you are using Report Manager you can do the following:
1. have a report parameter that is based on the query. For the query use
pass the User!userid to a stored procedure (or use in a query) to query a
table and find out what database they should be using. Have this parameter
hidden so it can not be seen or modified by the user.
2. Using the generic query designer have the query use an expression like
this:
= "Select * from " & Parameters!DBName & ".dbo.mytable"
I usually test all this out with a report that has a single text box set to
this expression so I can see the query string and be sure it is correct
before I set a dataset source to it.
But, one issue you will have (with both this and the other solution on
creating your own extension) is that the UserID global parameter is not
usable from subscriptions. Really any solution this will be a problem
because you have to set the subcription to be for someone or it is run as
someone.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:28360A4A-BB83-4B5B-B55E-83A9F3B378E9@.microsoft.com...
> Our application is used by multiple companies. Each company has its own
> SQL
> Server 2k database. Each company has access to the same list of reports
> in
> SRS. We would like to deploy one physical copy of each report in the
> report
> database for simplest maintenance of RDL files. Our application is
> deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report
> with
> datasource parameters that will connect him to the correct db. At the
> same
> time, users in companies B, C and D may be running the same report, each
> with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or
> connect
> to) the correct datasource parameters dynamically and then render the
> report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>|||Thanks very much for both of your responses.
I have been attempting to build an extension. It's hard to tell how far I
am from success because I don't know how to debug the code. Can you offer
advice on this?
I'm using the VS.NET Report Designer to build my reports. I can see the new
extension there, but cannot get a connection to work.
FYI - I'm using the ReportViewer control to embed the reports in my web app.|||Sorry, I have not done an extension. I suggest posting a new subject with
your questions for that so people that have done extensions know that is
what the question is about and can jump in and help you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Joe" <dexter@.newco.com> wrote in message
news:3537E448-CE86-4E68-87C7-C8719A62112B@.microsoft.com...
> Thanks very much for both of your responses.
> I have been attempting to build an extension. It's hard to tell how far I
> am from success because I don't know how to debug the code. Can you offer
> advice on this?
> I'm using the VS.NET Report Designer to build my reports. I can see the
> new
> extension there, but cannot get a connection to work.
> FYI - I'm using the ReportViewer control to embed the reports in my web
> app.|||We have struggled with the same issue.
We were able to implement the @.server parameter and use that parameter to
dynamically choose the sql server at runtime.
Have fun trying to figure out the authentication nightmare you will encounter.
"Joe" wrote:
> Our application is used by multiple companies. Each company has its own SQL
> Server 2k database. Each company has access to the same list of reports in
> SRS. We would like to deploy one physical copy of each report in the report
> database for simplest maintenance of RDL files. Our application is deployed
> in a web browser as an ASP application.
> When a user in company A accesses Report 2 we need to render the report with
> datasource parameters that will connect him to the correct db. At the same
> time, users in companies B, C and D may be running the same report, each with
> the correct datasource parameters for their companies.
> Hence, as a report is requested by each user, we need to set up (or connect
> to) the correct datasource parameters dynamically and then render the report
> for the user.
> Is there a way to do this with SRS? If multiple ways I would appreciate
> knowing some of the options. If there are good books that discuss these
> options I would appreciate references to them as well.
> TIA
>