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