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

sqldatasource selectparameters code behind
sqldatasource selectcommand code behind
sqldatasource selectcommand programmatically
how to pass parameter to sqldatasource in asp.net c#
sqldatasource stored procedure parameters code behind
asp:controlparameter
asp.net selectcommand stored procedure
sqldatasource selecting event example

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>

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());

How to pass variable to SelectCommand of SqlDataSource? Not , I am trying to pass a variable within a selectcommand statment in ASP.NET and I <asp:SqlDataSource ID="SqlDataSource1" runat="server" Examples. This section contains two code examples. The first code example demonstrates how to set the SelectCommand text to a basic SQL query to retrieve data from a Microsoft SQL Server database and display it in a DropDownList control.

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

Passing a Variable in a SelectCommand, 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,  SqlDataSource And Passing Multiple Int Parameters In Query; Passing '% Variable %' To SqlDataSource Through E.Command.Parameters; Passing Parameters To SQL Stored Procedure With SQLDataSource And ControlParameter; Passing A String Into The InsertCommand Of SqlDataSource At The @color Character; Passing Parameter To Stored Procedure Call Within

Just add a custom property to the page which will return the variable of your choice. You can then use the built-in "control" parameter type.

In the code behind, add:

Dim MyVariable as Long


ReadOnly Property MyCustomProperty As Long
    Get
        Return MyVariable
    End Get
End Property

In the select parameters section add:

<asp:ControlParameter ControlID="__Page" Name="MyParameter" 
PropertyName="MyCustomProperty" Type="Int32" />

ASP.Net SqlDataSource pass value to SelectParameter using , pass value to SelectParameter of SqlDataSource using QueryString Parameter to pass QueryString Value as Parameter to SqlDataSource hence I have placed a SelectCommand="SELECT * FROM Customers WHERE  Gets or sets the SQL string that the SqlDataSource control uses to retrieve data from the underlying database. An SQL string or the name of a stored procedure that the SqlDataSource uses to retrieve data. This section contains two code examples. The first code example demonstrates how to set the SelectCommand text to a basic SQL query to

to attach to a GUID:

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

Using Parameters with the SqlDataSource component, The SqlDataSource component supports parameterized SQL queries by associating the you add to the Parameters collection with placeholders in the SelectCommand query. For example, you can bind a SQL query parameter directly to a report Contact Support · Submit General Feedback Questions · Send Presales  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:

You can use the built in OnSelecting parameter of asp:SqlDataSource

Example: [.aspx file]

<asp:SqlDataSource ID="SqldsExample" runat="server"
SelectCommand="SELECT [SomeColumn], [AnotherColumn] 
               FROM [SomeTable]
               WHERE [Dynamic_Variable_Column] = @DynamicVariable"
OnSelecting="SqldsExample_Selecting">
<SelectParameters>
    <asp:Parameter Name="DynamicVariable" Type="String"/>
</SelectParameters>

Then in your code behind implement your OnSelecting method: [.aspx.cs]

protected void SqldsExample_Selecting(object sender, SqlDataSourceCommandEventArgs e)
{
     e.Command.Parameters["@DynamicVariable"].Value = (whatever value you want);
}

You can also use this for the other types of DB operations just implement your own methods:

OnInserting="SqldsExample_Inserting"
OnUpdating="SqldsExample_Updating"
OnDeleting="SqldsExample_Deleting"

SqlDataSource.SelectParameters Property (System.Web.UI , The SQL query is parameterized and the placeholder in the SelectCommand about other types of parameters you can use, see the Parameter class overview. Join a community of over 2.6m developers to have your questions answered on Session Variable in SQLDataSource of UI for ASP.NET AJAX Grid. New here? Start with our free trials.

Using SqlDataSource to select records using a Session variable, When a customer logs on I have a session variable called SalonID which I need to pass to the selectcommand . I am assuming that I potentially  To pass from codebehind, the parameter need to be set in the proper event of Datasource. Also no other properties of parameter need to set in aspx. Just declare a parameter.

How to pass variable to SelectCommand of SqlDataSource?, I want to pass variable from the code behind to SelectCommand of SqlDataSource? I dont want to use built-in parameter types (like ControlParemeter,  The HTML Markup consists of an ASP.Net GridView control which I’ll populate using the SqlDataSource control. In this example I am depicting how to pass QueryString Value as Parameter to SqlDataSource hence I have placed a TextBox and a Button that will be used to pass values as QueryString

how to pass variable to SelectCommand of SqlDataSource?, Hi, I want to put a recordset in a gridview but i don't know how to pass the value of the variable in the 'where' statement. The value of the 

Comments
  • it does not work because my variable type is GUID thanks any way
  • Not sure if this is because I use AutoEventWireup or not but I get Type 'MyProject.Controls.DelegateParameter' does not have a public property named 'OnEvaluate'
  • I needed to do this too, but from inside my user control. __Page doesn't work in that case. Inside of a user control ControlID is the class name of the user control--then it works fine.
  • they did not mention passing variables in this article the just explain built-in parameter thanks any way