How to use where clause in sql datasource RSS

• • Reply
  raushankarn

  Member

  10 Points

  33 Posts

  How to use where clause in sql datasource

  Jul 25, 2017 08:48 AM|raushankarn|LINK

  I want to configure sqldatasource as per dropdownlist ,i want to execute where clause. how to do that?

  ex..

  <td>

  <asp:DropDownList ID="ddlBName" runat="server" CssClass="input" DataTextField="BUName" DataValueField="BUId" Height="20px" Width="265px" DataSourceID="SqlDataSource1" OnDataBound="ddlBUName_DataBound" ValidationGroup="bum">
  </asp:DropDownList>
  <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBDocumentLibraryConnectionString %>" SelectCommand="SELECT [BUName], [BUId] FROM [VUserRoles] WHERE ([Role] = ?)">
  <SelectParameters>
  <asp:QueryStringParameter QueryStringField="Role" Name="Role" />
  
  </SelectParameters>
  </asp:SqlDataSource>
  </td>

   

  I want to exexute as simple sql query like "SELECT EmpId,Name FROM VUserRoles WHERE Role='BU Head' "

  Thanks.

• • Reply
  XIII

  All-Star

  191738 Points

  20952 Posts

  ASPInsiders

  Moderator

  MVP

  Re: How to use where clause in sql datasource

  Jul 25, 2017 08:59 AM|XIII|LINK

  Hi,

  you can make use of ControlParameter:

  <SelectParameters>
      <asp:ControlParameter ControlID="EmployeesDropDownList" PropertyName="SelectedValue"
          Name="EmpID" Type="Int32" DefaultValue="0" />
  </SelectParameters>
  

  Sample taken from https://msdn.microsoft.com/en-us/library/z72eefad.aspx.

  Kris.

  Read my blog | Twitter
  Working with Azure, chatbots, ASP.NET MVC, Web API, EF, MS SQL, ...
  Keep the forums clean: report to the moderation team!
• • Reply
  Eric Du

  Contributor

  6730 Points

  2715 Posts

  Re: How to use where clause in sql datasource

  Jul 26, 2017 02:03 AM|Eric Du|LINK

  Hi raushankarn,

  According to your description, as far as I know, you could use ControlParameter to filter the data, also you could use FilterParameters to filter the data. Here are three samples, please check:

  Using Parameterized Queries with the SqlDataSource (C#):

  https://docs.microsoft.com/en-us/aspnet/web-forms/overview/data-access/accessing-the-database-directly-from-an-aspnet-page/using-parameterized-queries-with-the-sqldatasource-cs 

  Using Parameters with Data Source Controls for Filtering:

  https://msdn.microsoft.com/en-us/library/xt50s8kz.aspx 

  Filter GridView with DropDownList using FilterExpression in SqlDataSource in ASP.Net:

  https://www.aspsnippets.com/Articles/Filter-GridView-with-DropDownList-using-FilterExpression-in-SqlDataSource-in-ASPNet.aspx 

  Best Regards,

  Eric Du

  MSDN Community Support
  Please remember to click "Mark as Answer" the responses that resolved your issue.
  If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.