Gridview filterexpression multiple integer parameters.

Question

User595996190 posted

I have added a second parameter for my filter expression. now the grid only returns values if both parameters are filled in.  I want the grid to be filtered by  parameter 1 or by parameter 2 if the other is not filled in.  I have been browsing this topic and it looks like I could use convert empty string to null if I wasn't filtering on an integer field.

I've tried changing the filter expression in the button click event. to no avail. If there are other events related to the data grid or data source where I should be putting the code I am not seeing them as possible selections in visual studio, probably do to my limited knowledge.

Code follows:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="PICA_number" HeaderText="PICA_number" SortExpression="PICA_number" />
<asp:BoundField DataField="Free_trade_zone_code" HeaderText="Free_trade_zone_code" SortExpression="Free_trade_zone_code" />
<asp:BoundField DataField="GWP_number" HeaderText="GWP_number" SortExpression="GWP_number" />
<asp:BoundField DataField="Task_description" HeaderText="Task_description" SortExpression="Task_description" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:PICACoreConnectionString %>" SelectCommand="Select PICA_number,Free_trade_zone_code,GWP_number,Task_description
from Permissions T1, Tasks T2 where t1.TaskID = t2.TaskID" FilterExpression="PICA_number = '{0}' and GWP_number = '{1}'">
<FilterParameters>
<asp:ControlParameter Name="PICA_number" ControlID="txtPICANum" PropertyName="Text" />
<asp:ControlParameter Name="GWP_number" ControlID="txtGWP" PropertyName="Text" />
</FilterParameters>
</asp:SqlDataSource>

the button click event:

protected void btnFilter_Click(object sender, EventArgs e)
{
SqlDataSource1.FilterExpression = "";
if (txtPICANum.Text == "" & txtGWP.Text != "")
{ SqlDataSource1.FilterExpression = "GWP_number = '{1}'"; }
else if (txtPICANum.Text != "" & txtGWP.Text == "")
{ SqlDataSource1.FilterExpression = "PICA_number = '{0}'"; }
else if (txtPICANum.Text != "" & txtGWP.Text != "")
{ SqlDataSource1.FilterExpression = "PICA_number = '{0}' and GWP_number = '{1}'"; }
}

Answer 1

User-330142929 posted

Hi Aramis1212, 

As far as I know, if you leave the any textbox empty.the value will be null, so we should assign its value is “” instead of Null.And there are two ways to meet this requirement.

1. Handle the null parameters in OnFiltering event.

Code behind.

 protected void SqlDataSource1_Filtering(object sender, SqlDataSourceFilteringEventArgs e)
        {
            for (int i = 0; i < e.ParameterValues.Count; i++)
            {
                if (e.ParameterValues[i] == null)
                {
                    e.ParameterValues[i] = "";
                }
            }

        }

 2. Assign the FilterParameters  “ConvertEmptyStringToNull=false”.

Aspx.

  <FilterParameters>
                    <asp:ControlParameter ControlID="txtName" Name="Name" PropertyName="Text" ConvertEmptyStringToNull="false"/>
                    <asp:ControlParameter ControlID="txtCity" Name="City" PropertyName="Text" ConvertEmptyStringToNull="false"/>
                </FilterParameters>

Gif Demo.

Please feel free to let me know if the problem still exists.

Best Regards,

Abraham