Dynamic SELECT Command in SqlDataSource

I have a GridView (that uses SqlDataSource1) and a Dropdownlist. Depending upon the value selected on the DropDownList I need to select different stored procedures for the gridview. The problem is that I can do it without taking SqlDataSource1 by using DataSet or DataTable. But, I need to Use SQLDataSource1 for easy way of Header SORTING. So, is there any way to change the SQLDatasource1.SELECT Command dynamically. So that, I can use different queries for the Single DataGrid.

I have attached the sample code of the SqlDataSource1 I'm using. I need to change the Command i.e.SelectCommand="usp_reports_shortages" to"usp_reports_shortagesbyID" and"usp_reports_shortagesbyDate"

depending on the value selected in the dropdownlist. So, is there any way to do this??
<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ConnectionStrings:TESTDrivercommunication%>"

SelectCommand="usp_reports_shortages"SelectCommandType="StoredProcedure">

<SelectParameters>

<asp:ControlParameterControlID="lblDriver"Name="date1"PropertyName="Text"Type="DateTime"/>

<asp:ControlParameterControlID="lblTODate"Name="date2"PropertyName="Text"Type="DateTime"/>

<asp:ControlParameterControlID="DDlDriver"Name="driver"PropertyName="SelectedValue"

Type="Int32"/>

<asp:SessionParameterName="week"SessionField="s_week"Type="DateTime"/>

</SelectParameters>

</asp:SqlDataSource>

Numerous ways.

Change the value in the dropdown's selectedindex changed event.

Change it in SqlDatasource's selecting event.

Change the select command to an if. "IF @.DDlDriver=1 THEN EXECusp_reports_shortages @.date1,@.date2,@.weekIF @.DDlDriver=2 THEN EXEC usp_reports_shortagesbyID @.date1,@.date2,@.week IF @.DDlDriver=3 THEN EXECusp_reports_shortagesbyDate @.date1,@.date2,@.week".

Make a stored procedure that takes all 4 paramters and encapsulates the IF's.

sql

Dynamic create table, create index based upon a given database

Can I dynamically (from a stored procedure) generate
a create table script of all tables in a given database (with defaults etc)
a create view script of all views
a create function script of all functions
a create index script of all indexes.
(The result will be 4 scripts)

Arno de Jong,
The Netherlands.The following will create a script for each type of object:

EXEC XP_CMDSHELL '"C:\Program Files\Microsoft SQL
Server\MSSQL\Upgrade\SCPTXFR" /I /D DATABASE_NAME /S SERVER_NAME /F
C:\PATH_NAME'

--
David Portas
----
Please reply only to the newsgroup
--

"A.M. de Jong" <arnojo@.wxs.nl> wrote in message
news:bpkpq8$7vi$1@.reader10.wxs.nl...
> Can I dynamically (from a stored procedure) generate
> a create table script of all tables in a given database (with defaults
etc)
> a create view script of all views
> a create function script of all functions
> a create index script of all indexes.
> (The result will be 4 scripts)
> Arno de Jong,
> The Netherlands.

Dynamic connection string

Our Reporting Services environment uses Oracle as the data source. Based upon the user connecting to the database determines what rows they will see for various tables. How can we dynamically pass the username/password to the connection string? Background: Our users log into Active Directory and are assigned to a group. The AD group name is used to access a control table in Oracle that contains the database username/password for that group’s connection to Oracle. All subsequent connections to Oracle will use the group’s username/password from the control table. We have an ASP.NET application that works like this and stores the connection information in the session state. How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.

You can create a private (report-level) data source that uses an expression-based connection string as the ExpressionBasedConnection report in this download demonstrates.|||Thank you Teo for your prompt reply. I will take a look at this.|||

Teo,

I looked over the files, but I can't say that the answer stands out at me. Can you tell me specifically which part that I should look at?

|||The report needs to be set up for a private data source and the data source connection string is expression-based. Look at the way the dataset data source is configured.|||

Thanks Teo. I looked into this but it does not solve our unique situation. In our reports, we logged onto our database as one user. We have other companies that need to access our reports now, but do not want them to see the same information. We are attempting to get the user information and log into the database to retrieve the password for the group that that person belongs to. Then we would like to pass back the group user name and password in our connection string so that the person running the report only sees the information that they are allowed to. Another developer wrote a DLL to attempt to do this. It passes back in the dll but does not work when we try to display the connection string on the report. Do you have any samples to make this work or any suggestions on how we might do this?

|||

Why wouldn't this approach work in your situation? Can't you call the DLL from the connection string expression in the same way and get the user-specific connection string?

As a side note, the right way probably to address a similar requirement is to pass User!UserID to your database and let the database implememt row-level security based on the user identity. With this approach you don't have to deal with connection strings at all.

|||Our biggest problem is that we need to take the ID from Active Directory and find the groups that they are assigned to. So far we have been unsuccessful in making this work.

Dynamic connection string

Our Reporting Services environment uses Oracle as the data source. Based upon the user connecting to the database determines what rows they will see for various tables. How can we dynamically pass the username/password to the connection string? Background: Our users log into Active Directory and are assigned to a group. The AD group name is used to access a control table in Oracle that contains the database username/password for that group’s connection to Oracle. All subsequent connections to Oracle will use the group’s username/password from the control table. We have an ASP.NET application that works like this and stores the connection information in the session state. How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.

You can create a private (report-level) data source that uses an expression-based connection string as the ExpressionBasedConnection report in this download demonstrates.|||Thank you Teo for your prompt reply. I will take a look at this.|||

Teo,

I looked over the files, but I can't say that the answer stands out at me. Can you tell me specifically which part that I should look at?

|||The report needs to be set up for a private data source and the data source connection string is expression-based. Look at the way the dataset data source is configured.|||

Thanks Teo. I looked into this but it does not solve our unique situation. In our reports, we logged onto our database as one user. We have other companies that need to access our reports now, but do not want them to see the same information. We are attempting to get the user information and log into the database to retrieve the password for the group that that person belongs to. Then we would like to pass back the group user name and password in our connection string so that the person running the report only sees the information that they are allowed to. Another developer wrote a DLL to attempt to do this. It passes back in the dll but does not work when we try to display the connection string on the report. Do you have any samples to make this work or any suggestions on how we might do this?

|||

Why wouldn't this approach work in your situation? Can't you call the DLL from the connection string expression in the same way and get the user-specific connection string?

As a side note, the right way probably to address a similar requirement is to pass User!UserID to your database and let the database implememt row-level security based on the user identity. With this approach you don't have to deal with connection strings at all.

|||Our biggest problem is that we need to take the ID from Active Directory and find the groups that they are assigned to. So far we have been unsuccessful in making this work.

Dynamic and Group Parameters

I am using Parameter to determine the grouping of my table. Is it possible to
use a dynamic parameter based upon my grouping value. i.e. if the user
selects to group on students the dynamic parameter will be populated with all
student names, or if the user selects to group on subjects the dynamic
parameter will be populated with all subject names.http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx
--
This post is provided 'AS IS' with no warranties, and confers no rights. All
rights reserved. Some assembly required. Batteries not included. Your
mileage may vary. Objects in mirror may be closer than they appear. No user
serviceable parts inside. Opening cover voids warranty. Keep out of reach of
children under 3.
"SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
news:8E5BEA8E-C744-4FA2-9FAE-5D268BAEE170@.microsoft.com...
> I am using Parameter to determine the grouping of my table. Is it possible
to
> use a dynamic parameter based upon my grouping value. i.e. if the user
> selects to group on students the dynamic parameter will be populated with
all
> student names, or if the user selects to group on subjects the dynamic
> parameter will be populated with all subject names.|||Hi Chris:
I will try explaining my issue again. I have created a report which has
dynamic grouping based on parameter selection. e.g. the user selectes student
or subject from the parameter drop don box and the report is then grouped
accordingly. What I want to do now is have another parameter which is
populated based on the first parameters value. So if students are selected in
parameter 1 , parameter 2 will allow me to select which student to report on.
"Chris Hays [MSFT]" wrote:
> http://blogs.msdn.com/chrishays/archive/2004/07/15/184646.aspx
> --
> This post is provided 'AS IS' with no warranties, and confers no rights. All
> rights reserved. Some assembly required. Batteries not included. Your
> mileage may vary. Objects in mirror may be closer than they appear. No user
> serviceable parts inside. Opening cover voids warranty. Keep out of reach of
> children under 3.
> "SAcanuck" <SAcanuck@.discussions.microsoft.com> wrote in message
> news:8E5BEA8E-C744-4FA2-9FAE-5D268BAEE170@.microsoft.com...
> > I am using Parameter to determine the grouping of my table. Is it possible
> to
> > use a dynamic parameter based upon my grouping value. i.e. if the user
> > selects to group on students the dynamic parameter will be populated with
> all
> > student names, or if the user selects to group on subjects the dynamic
> > parameter will be populated with all subject names.
>
>