FilterExpression with multiple columns in SQLDataSource


 

Update:

–***

By assigning default values to controlparameters, we can make FilterExpresssin work with multiple columns with this syntax:

FilterExpression="(UserName =’{0}’ or ‘{0}’ =’-1′) AND (LiveMonth={1} or {1}=-1) AND (LiveYear={2} or {2}=-1) "

along with a default value -1 for all controlparameters.

–****

It is pretty easy to use a FilterExpression with SqlDataSource if you choose to filter the data after dataset returned through SELECT command. Normally, I would recommend to filter data from database call with a SelectParameter. When you decide to use multiple filterparameters, it seems that it will not that hard to add another FilterParameter to FilterParameters collection. However, after you add another parameter to the FilterExpression, the filtering simply stops working.

There is no syntax error for this behavior. A work around for this is to use some code to handle the fileterparameters andFilterExpression of the SqlDataSource.

Here is a working sample with three dropdownlist. The code is hooked to dropdownlist’s SelectedIndexChanged event.

protected void ddls_SelectedIndexChanged(object sender, System.EventArgs e)
    {
        bool bUsers = string.IsNullOrEmpty(ddlUsers.SelectedValue);
        bool bMonth = string.IsNullOrEmpty(ddlMonth.SelectedValue);
        bool bYear = string.IsNullOrEmpty(ddlYear.SelectedValue);
        SqlDataSource1.FilterParameters.Clear();
        SqlDataSource1.FilterExpression = "";
        if (bUsers & bMonth & bYear){}
        else if (!bUsers & bMonth & bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterExpression = "UserName=’{0}’";
        }
        else if (bUsers & !bMonth & bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterExpression = "LiveMonth={0}";
        }

        else if (bUsers & bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlYear", "SelectedValue"));
            SqlDataSource1.FilterExpression = "LiveYear={0}";
        }

        else if (!bUsers & bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveYear", TypeCode.Int32, "ddlYear", "SelectedValue"));
            SqlDataSource1.FilterExpression = "UserName=’{0}’ AND LiveYear={1}";
        }

        else if (!bUsers & !bMonth & bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterExpression = "UserName=’{0}’ AND LiveMonth={1}";
        }
        else if (bUsers & !bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveYear", TypeCode.Int32, "ddlYear", "SelectedValue"));
            SqlDataSource1.FilterExpression = "LiveMonth={0} AND LiveYear={1}";
        }

        else if (!bUsers & !bMonth & !bYear)
        {
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("UserName", TypeCode.String, "ddlUsers", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveMonth", TypeCode.Int32, "ddlMonth", "SelectedValue"));
            SqlDataSource1.FilterParameters.Add(new System.Web.UI.WebControls.ControlParameter("LiveYear", TypeCode.Int32, "ddlYear", "SelectedValue"));

            SqlDataSource1.FilterExpression = "UserName=’{0}’ AND LiveMonth={1} AND LiveYear={2}";

        }
    }

The aspx page looks like this:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id"
          DataSourceID="SqlDataSource1">
          <Columns>
          <asp:BoundField DataField="LiveMonth" HeaderText="LiveMonth" SortExpression="LiveMonth" />
              <asp:BoundField DataField="dob" HeaderText="dob" SortExpression="dob" />
              <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                  SortExpression="id" />
              <asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />
          </Columns>
      </asp:GridView>
      <asp:SqlDataSource ID="SqlDataSource1" runat="server"
      ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
       CancelSelectOnNullParameter="true"
          SelectCommand="SELECT dob, name, LiveYear, LiveMonth, [id], UserName FROM [Dates4] " >

                      </asp:SqlDataSource>
      <asp:DropDownList ID="ddlUsers" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddls_SelectedIndexChanged">
      <asp:ListItem Value="">All</asp:ListItem>
      <asp:ListItem Value="aaa">aaa</asp:ListItem>
        <asp:ListItem Value="bbb">bbb</asp:ListItem>
          <asp:ListItem Value="ccc">ccc</asp:ListItem>
       <asp:ListItem Value="ddd">ddd</asp:ListItem>
       <asp:ListItem Value="xxx">xxx</asp:ListItem>
          </asp:DropDownList>
      <asp:DropDownList ID="ddlMonth" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddls_SelectedIndexChanged">
              <asp:ListItem Value="">All</asp:ListItem>
      <asp:ListItem Value="1">1</asp:ListItem>
        <asp:ListItem Value="2">2</asp:ListItem>
          <asp:ListItem Value="3">3</asp:ListItem>
             <asp:ListItem Value="4">4</asp:ListItem>
        <asp:ListItem Value="5">5</asp:ListItem>
          <asp:ListItem Value="6">6</asp:ListItem>
           <asp:ListItem Value="7">7</asp:ListItem>
            <asp:ListItem Value="8">8</asp:ListItem>
             <asp:ListItem Value="9">9</asp:ListItem>
              <asp:ListItem Value="10">10</asp:ListItem>
               <asp:ListItem Value="11">11</asp:ListItem>
                <asp:ListItem Value="12">12</asp:ListItem>
      </asp:DropDownList>
<asp:DropDownList ID="ddlYear" runat="server" AutoPostBack="true" OnSelectedIndexChanged="ddls_SelectedIndexChanged">
                     <asp:ListItem Value="">All</asp:ListItem>
      <asp:ListItem Value="2007">2007</asp:ListItem>
        <asp:ListItem Value="2008">2008</asp:ListItem>
          <asp:ListItem Value="2009">2009</asp:ListItem>
      </asp:DropDownList>

 

Edit:

Sample aspx page::

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="id"

DataSourceID="SqlDataSource1">

<Columns>

<asp:BoundField DataField="LiveMonth" HeaderText="LiveMonth" SortExpression="LiveMonth" />

<asp:BoundField DataField="dob" HeaderText="dob" SortExpression="dob" />

<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"

SortExpression="id" />

<asp:BoundField DataField="name" HeaderText="name" SortExpression="name" />

</Columns>

</asp:GridView>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"

SelectCommand="SELECT dob, name, LiveYear, LiveMonth, [id], UserName FROM [Dates4] "

FilterExpression="(UserName =’{0}’ or ‘{0}’ =’-1′) AND (LiveMonth={1} or {1}=-1) AND (LiveYear={2} or {2}=-1) " >

<

FilterParameters>

<

asp:ControlParameter ControlID="ddlUsers" Name="UserName" PropertyName="SelectedValue" Type="String" DefaultValue="-1" />

<

asp:ControlParameter ControlID="ddlMonth" Name="LiveMonth" PropertyName="SelectedValue" Type="Int32" DefaultValue="-1" />

<

asp:ControlParameter ControlID="ddlYear" Name="LiveYear" PropertyName="SelectedValue" Type="Int32" DefaultValue="-1" />

</

FilterParameters>

</asp:SqlDataSource>

<asp:DropDownList ID="ddlUsers" runat="server" AutoPostBack="true" >

<asp:ListItem Value="-1">All</asp:ListItem>

<asp:ListItem Value="aaa">aaa</asp:ListItem>

<asp:ListItem Value="bbb">bbb</asp:ListItem>

<asp:ListItem Value="ccc">ccc</asp:ListItem>

<asp:ListItem Value="ddd">ddd</asp:ListItem>

<asp:ListItem Value="xxx">xxx</asp:ListItem>

</asp:DropDownList>

<asp:DropDownList ID="ddlMonth" runat="server" AutoPostBack="true" >

<asp:ListItem Value="-1">All</asp:ListItem>

<asp:ListItem Value="1">1</asp:ListItem>

<asp:ListItem Value="2">2</asp:ListItem>

<asp:ListItem Value="3">3</asp:ListItem>

<asp:ListItem Value="4">4</asp:ListItem>

<asp:ListItem Value="5">5</asp:ListItem>

<asp:ListItem Value="6">6</asp:ListItem>

<asp:ListItem Value="7">7</asp:ListItem>

<asp:ListItem Value="8">8</asp:ListItem>

<asp:ListItem Value="9">9</asp:ListItem>

<asp:ListItem Value="10">10</asp:ListItem>

<asp:ListItem Value="11">11</asp:ListItem>

<asp:ListItem Value="12">12</asp:ListItem>

</asp:DropDownList>

<asp:DropDownList ID="ddlYear" runat="server" AutoPostBack="true" >

<asp:ListItem Value="-1">All</asp:ListItem>

<asp:ListItem Value="2007">2007</asp:ListItem>

<asp:ListItem Value="2008">2008</asp:ListItem>

<asp:ListItem Value="2009">2009</asp:ListItem>

</asp:DropDownList>

 

Advertisements

One Comment on “FilterExpression with multiple columns in SQLDataSource”

  1. gg says:

    tq very much …help me a lot …


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s