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 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 Database Owner

I've got a GridView on my webform bound to a SQLDataSource (called sqlZKEWILL). Works fine as long as I hard-code the "dbo" database owner in the Select statement, as in:

select EXIDV, Carton_Count, Carton_Total, dbo.ZINSPECTION where EXIDV = @.CartonID

The problem is that I need the database owner name to be dynamic. This will be a parameter that I read from another SQL table. For example, I'm doing this:

Select USERID, DatabaseOwner from tblUsers where UserID = 'Kimm'

I need to take the DatabaseOwner from this second Select statement, and use it as part of that first select statement.

On the CodeBehind page, I tried replacing "dbo" with the Database Owner field (which I saved in a session variable) with the following code:

PrivateSub gvLineItems_DataBinding(ByVal senderAsObject,ByVal eAs System.EventArgs)Handles gvLineItems.DataBinding

sqlZKEWILL.SelectCommand = sqlZKEWILL.SelectCommand.Replace("dbo", Session("DatabaseOwner").ToString.Trim)

EndSub

But I am still getting the error message: "Invalid object name: dbo.ZINSPECTION". It doesn't seem to recognize that I've replaced "dbo" with a different value.

Any thoughts on getting this to work would be appreciated.

Hi,

From your description, it seems that you want to change the select command in the DataBinding event of your SqlDataSource, right?

Based on my knowledge, when the DataBinding event has fired, the select command has been in "ready for execute" status, so when you modify the select command in that event, it won't affect this time's execution. I suggest that you should modify your select command in some events of data bound control. Such as databinding event of GridView control.

Besides, since you are changing the database owner, please make sure that if the database owner matches the data tables, otherwise, you can't access the those data objects.

Thanks.

|||

Thank you for the advice. The solution I ended up with is similar to your suggestion. I ended up re-binding the GridView every time the user enters values in a text box, to allow me to dynamically change both the database owner and the where claus as needed. I appreciate your taking the time to help.