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>
This question is related to
.net
asp.net
variables
parameters
sqldatasource
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" />
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"
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
See if it works if you just remove the DbType="Guid" from the markup.
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.
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;
}
to attach to a GUID:
SqlDataSource1.SelectParameters.Add("userId", System.Data.DbType.Guid, userID);
You need to define a valid type of SelectParameter. This MSDN article describes the various types and how to use them.
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" />
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"
to attach to a GUID:
SqlDataSource1.SelectParameters.Add("userId", System.Data.DbType.Guid, userID);
See if it works if you just remove the DbType="Guid" from the markup.
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;
You need to define a valid type of SelectParameter. This MSDN article describes the various types and how to use them.
See if it works if you just remove the DbType="Guid" from the markup.
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());
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;
}
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
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());
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.
to attach to a GUID:
SqlDataSource1.SelectParameters.Add("userId", System.Data.DbType.Guid, userID);
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());
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;
}
You need to define a valid type of SelectParameter. This MSDN article describes the various types and how to use them.
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;
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;
}
You need to define a valid type of SelectParameter. This MSDN article describes the various types and how to use them.
See if it works if you just remove the DbType="Guid" from the markup.
to attach to a GUID:
SqlDataSource1.SelectParameters.Add("userId", System.Data.DbType.Guid, userID);
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());
Source: Stackoverflow.com