How to pass a variable to the SelectCommand of a SqlDataSource?

Question

I want to pass variable from the code behind to the SelectCommand of a SqlDataSource?

I don't want to use built-in parameter types (like ControlParameter, QueryStringParameter, etc)

I need to pass a variable, but the following example does not work:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:itematConnectionString %>" SelectCommand = "SELECT items.name, items.id FROM items INNER JOIN users_items ON items.id = users_items.id WHERE (users_items.user_id = @userId) ORDER BY users_items.date DESC" >     <SelectParameters>         <asp:Parameter  DefaultValue="<%= userId %>" Name="userId"  DbType="Guid" />     </SelectParameters> </asp:SqlDataSource> 

Answer 1

Try this instead, remove the SelectCommand property and SelectParameters:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"      ConnectionString="<%$ ConnectionStrings:itematConnectionString %>"> 

Then in the code behind do this:

SqlDataSource1.SelectParameters.Add("userId", userId.ToString());  SqlDataSource1.SelectCommand = "SELECT items.name, items.id FROM items INNER JOIN users_items ON items.id = users_items.id WHERE (users_items.user_id = @userId) ORDER BY users_items.date DESC" 

While this worked for me, the following code also works:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"      ConnectionString="<%$ ConnectionStrings:itematConnectionString %>"     SelectCommand = "SELECT items.name, items.id FROM items INNER JOIN users_items ON items.id = users_items.id WHERE (users_items.user_id = @userId) ORDER BY users_items.date DESC"></asp:SqlDataSource>   SqlDataSource1.SelectParameters.Add("userid", DbType.Guid, userId.ToString()); 

Answer 2

we had to do this so often that I made what I called a DelegateParameter class

using System; using System.Collections.Generic; using System.Text; using System.Web.UI.WebControls; using System.Reflection;  namespace MyControls {     public delegate object EvaluateParameterEventHandler(object sender, EventArgs e);      public class DelegateParameter : Parameter     {         private System.Web.UI.Control _parent;         public System.Web.UI.Control Parent         {             get { return _parent; }             set { _parent = value; }         }          private event EvaluateParameterEventHandler _evaluateParameter;         public event EvaluateParameterEventHandler EvaluateParameter         {             add { _evaluateParameter += value; }             remove { _evaluateParameter -= value; }         }          protected override object Evaluate(System.Web.HttpContext context, System.Web.UI.Control control)         {             return _evaluateParameter(this, EventArgs.Empty);         }     } } 

put this class either in your app_code (remove the namespace if you put it there) or in your custom control assembly. After the control is registered in the web.config you should be able to do this

<asp:SqlDataSource ID="SqlDataSource1" runat="server"      ConnectionString="<%$ ConnectionStrings:itematConnectionString %>"     SelectCommand = "SELECT items.name, items.id FROM items INNER JOIN users_items ON items.id = users_items.id WHERE (users_items.user_id = @userId) ORDER BY users_items.date DESC">     <SelectParameters>     <asp:DelegateParameter Name="userId"  DbType="Guid" OnEvaluate="GetUserID" />     </SelectParameters> </asp:SqlDataSource> 

then in the code behind you implement the GetUserID anyway you like.

protected object GetUserID(object sender, EventArgs e) {   return userId; }