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>

score 29 · Answer 1 · edited May 22 '19 at 20:45

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

it does not work because my variable type is GUID thanks any way — ahmed, Jan 27 '09 at 23:32

score 12 · Answer 2 · answered Jan 28 '09 at 06:29

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

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'` — Victorio Berra, Oct 03 '17 at 17:18

score 10 · Answer 3 · answered Jun 29 '11 at 21:35

10

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" />

answered Jun 29 '11 at 21:35

Chabsin

101
1
2

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. – Jim Dec 30 '14 at 21:31

score 5 · Answer 4 · answered Jan 28 '09 at 03:38

5

to attach to a GUID:

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

answered Jan 28 '09 at 03:38

Glennular

17,827
9
58
77

score 4 · Answer 5 · answered Oct 01 '14 at 14:19

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"

score 3 · Answer 6 · answered Aug 11 '13 at 02:36

SqlDataSource1.SelectCommand = "select * from ta where name like '%'+@p+'%'";
if (SqlDataSource1.SelectParameters.Count == 0)
{
   SqlDataSource1.SelectParameters.Add("p", DbType.String, TextBox1.Text);
}
SqlDataSource1.SelectParameters["p"].DefaultValue = TextBox1.Text;

score 0 · Answer 7 · answered Aug 18 '12 at 18:20

try with this.

Protected Sub SqlDataSource_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource.Selecting

    e.Command.CommandText = "SELECT [ImageID],[ImagePath] FROM [TblImage] where IsActive = 1"

    End Sub

score 0 · Answer 8 · answered Jan 27 '09 at 23:29

0

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

answered Jan 27 '09 at 23:29

Mitch Wheat

295,962
43
465
541

they did not mention passing variables in this article the just explain built-in parameter thanks any way – ahmed Jan 27 '09 at 23:37

score 0 · Answer 9 · answered Aug 01 '18 at 22:41

I love Al W's answer. There's a typo though.

<asp:DelegateParameter Name="userId"  DbType="Guid" OnEvaluate="GetUserID" />

--- Should be ---

<asp:DelegateParameter Name="userId"  DbType="Guid" OnEvaluateParameter="GetUserID" />

Hopefully, this saves someone a few minutes.

score -1 · Answer 10 · answered Jan 28 '09 at 01:16

-1

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

answered Jan 28 '09 at 01:16

John Boker

82,559
17
97
130

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

10 Answers10

Linked