How to pass variable to SelectCommand of SqlDataSource?

Question

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

I dont want to use built-in parameter types (like ControlParemeter, QueryStringParameter, etc)

I need to pass a vraiable?

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>

Answer 1

You could always do it like this in the code behind:

SqlDataSource1.SelectParameters.Add("@userId", userId);

EDIT

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"

This worked for me, this 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

You need to define a valid type of SelectParameter. This MSDN article describes the various types and how to use them.

Answer 3

See if it works if you just remove the DbType="Guid" from the markup.

Answer 4

to attach to a GUID:

 SqlDataSource1.SelectParameters.Add("userId",  System.Data.DbType.Guid, userID);

Answer 5

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;
}