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    lt asp SqlDataSource ID  SqlDataSource1  runat  server  ConnectionString   lt    ConnectionStrings itematConnectionString   gt   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   gt       lt SelectParameters gt           lt asp Parameter  DefaultValue   lt    userId   gt   Name  userId   DbType  Guid    gt       lt  SelectParameters gt   lt  asp SqlDataSource gt

User · Answer

to attach to a GUID    SqlDataSource1 SelectParameters Add  userId    System Data DbType Guid  userID

User · Answer

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

User · Answer

I love Al W s answer   There s a typo though    lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluate  GetUserID    gt    --- Should be ---   lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluateParameter  GetUserID    gt    Hopefully  this saves someone a few minutes

User · Answer

to attach to a GUID    SqlDataSource1 SelectParameters Add  userId    System Data DbType Guid  userID

User · Answer

Try this instead  remove the SelectCommand property and SelectParameters    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt   gt    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    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt  lt  asp SqlDataSource gt    SqlDataSource1 SelectParameters Add  userid   DbType Guid  userId ToString

User · Answer

Try this instead  remove the SelectCommand property and SelectParameters    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt   gt    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    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt  lt  asp SqlDataSource gt    SqlDataSource1 SelectParameters Add  userid   DbType Guid  userId ToString

User · Answer

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   lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt       lt SelectParameters gt       lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluate  GetUserID    gt       lt  SelectParameters gt   lt  asp SqlDataSource gt    then in the code behind you implement the GetUserID anyway you like   protected object GetUserID object sender  EventArgs e      return userId

User · Answer

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

User · Answer

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    lt asp ControlParameter ControlID    Page  Name  MyParameter   PropertyName  MyCustomProperty  Type  Int32    gt

User · Answer

You can use the built in OnSelecting parameter of asp SqlDataSource  Example    aspx file    lt asp SqlDataSource ID  SqldsExample  runat  server  SelectCommand  SELECT  SomeColumn    AnotherColumn                  FROM  SomeTable                 WHERE  Dynamic Variable Column     DynamicVariable  OnSelecting  SqldsExample Selecting  gt   lt SelectParameters gt       lt asp Parameter Name  DynamicVariable  Type  String   gt   lt  SelectParameters gt    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

User · Answer

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   lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt       lt SelectParameters gt       lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluate  GetUserID    gt       lt  SelectParameters gt   lt  asp SqlDataSource gt    then in the code behind you implement the GetUserID anyway you like   protected object GetUserID object sender  EventArgs e      return userId

User · Answer

to attach to a GUID    SqlDataSource1 SelectParameters Add  userId    System Data DbType Guid  userID

User · Answer

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

User · Answer

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

User · Answer

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   lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt       lt SelectParameters gt       lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluate  GetUserID    gt       lt  SelectParameters gt   lt  asp SqlDataSource gt    then in the code behind you implement the GetUserID anyway you like   protected object GetUserID object sender  EventArgs e      return userId

User · Answer

to attach to a GUID    SqlDataSource1 SelectParameters Add  userId    System Data DbType Guid  userID

User · Answer

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

User · Answer

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    lt asp ControlParameter ControlID    Page  Name  MyParameter   PropertyName  MyCustomProperty  Type  Int32    gt

User · Answer

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   lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt       lt SelectParameters gt       lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluate  GetUserID    gt       lt  SelectParameters gt   lt  asp SqlDataSource gt    then in the code behind you implement the GetUserID anyway you like   protected object GetUserID object sender  EventArgs e      return userId

User · Answer

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

User · Answer

You can use the built in OnSelecting parameter of asp SqlDataSource  Example    aspx file    lt asp SqlDataSource ID  SqldsExample  runat  server  SelectCommand  SELECT  SomeColumn    AnotherColumn                  FROM  SomeTable                 WHERE  Dynamic Variable Column     DynamicVariable  OnSelecting  SqldsExample Selecting  gt   lt SelectParameters gt       lt asp Parameter Name  DynamicVariable  Type  String   gt   lt  SelectParameters gt    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

User · Answer

I love Al W s answer   There s a typo though    lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluate  GetUserID    gt    --- Should be ---   lt asp DelegateParameter Name  userId   DbType  Guid  OnEvaluateParameter  GetUserID    gt    Hopefully  this saves someone a few minutes

User · Answer

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

User · Answer

Try this instead  remove the SelectCommand property and SelectParameters    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt   gt    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    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt  lt  asp SqlDataSource gt    SqlDataSource1 SelectParameters Add  userid   DbType Guid  userId ToString

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this instead  remove the SelectCommand property and SelectParameters    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt   gt    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    lt asp SqlDataSource ID  SqlDataSource1  runat  server       ConnectionString   lt    ConnectionStrings itematConnectionString   gt       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  gt  lt  asp SqlDataSource gt    SqlDataSource1 SelectParameters Add  userid   DbType Guid  userId ToString

[.net] How to pass a variable to the SelectCommand of a SqlDataSource?

Examples related to .net

Examples related to asp.net

Examples related to variables

Examples related to parameters

Examples related to sqldatasource