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 query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

dynamic query

hi,

i'm using dynamic query in the dataset because i wanted the result to group by the particular parameter selected.

however, another parameter requires the data to be taken from another table.

is the following syntax acceptable?

=if (Parameters!rev_data = 'A')

begin

"select a,b,c.... from tableA"

end

else

begin

"select d,e,f.... from tableB"

end

i encounter this error : 'Expression expected'...

is the syntax wrong or this way is not possible?

thanks!

I use Stored Procedures to generate dynamic SQL used for reports. The conditional logic is handled in T-SQL to generate and execute the appropriate SQL based on the input parameters. For example, when users want to select the sort fields and sort order for the data in a report.|||Can you give us an example of how you made this work in the stored procedure?

dynamic creation of select statement

hello,

My basic requirement is i have five drop down list where data is selected and based on the selection the no. of colums to be selected have to be designed. ex : say i have following drop down lists:
1. community
2. gender
3. level
4. term
5. year

at runtime is it possible to design the crystal reports columns.

Pl. help out in directing to url or given sample source to dynamic creation of columns in the crystal reports.

thanks in advance
karunau need to add the formulas and the field object formatting.
u can do this by declaring an array -->
IFormulaFieldDefinitionPtr formulas[10];

then when adding formulas-->
ISectionPtr pSection = GetReportSection(sectionNum);
IFormulaFieldDefinitionsPtr pFormulaFields = 0;
m_Report->get_FormulaFields((IFormulaFieldDefinitions**) &pFormulaFields);

CString Recur = CString(L"WhileReadingRecords;" +
(CString)__toascii(13) +
(CString)__toascii(10));

// Add the formulas to the formula fields collection
CString CStrText = Recur + CString(L"Space(10)");
BSTR strText = CStrText.AllocSysString();
BSTR objName;
int i=0;
////assume u have stored the field names somewhere like a list or an array.

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
if(i==0)
{
objName = str.AllocSysString();
formulas[i]=pFormulaFields->Add(objName, strText);


}
else
{
SysReAllocString(&objName, str.AllocSysString());
formulas[i]=pFormulaFields->Add(objName, strText);

}

i++;
}

////////////////////////////////////////////////////////////////////////////////
then add the field objects-->

ISectionPtr pSection = GetReportSection(3);
IFieldObjectPtr pFieldObj = 0;

LocX = 10; // Horizontal offset
VARIANT var;
VariantInit (&var);
var.vt = VT_DISPATCH;
BSTR objName = NULL;

int i=0;

for(iter=lstRecs.begin();iter!=lstRecs.end();iter++)
{

CString str= *iter;
var.pdispVal = formulas[i];
pFieldObj = pSection->AddFieldObject(var, LocX, 0);
SysReAllocString(&objName,str.AllocSysString());
pFieldObj->put_Name(objName);
pFieldObj->put_Width(1600);
LocX += 1500;
}

////////////////////////////////////////////////////////////////////////////////////////////
thats it! hope this helps...