CODEWITHSUNDEEP
datetimegridviewwebformssqldatasource
mike gold
mike gold

Reputation: 1611

FilterExpression Does Not work when one of the parameters is DateTime

I'm having an issue with getting FilterExpression to work properly in a SqlDataSource. The Date Column in the SqlDatabase, NewsDate, is a DateTime. The text column, NewsSubject, in the SqlDataBase is a varchar. Here is the FilterExpression:

FilterExpression="[NewsSubject] LIKE '%{0}%' AND [NewsDate] = '{1}'"

And the control parameters are shown below:

<FilterParameters>
           <asp:ControlParameter ControlID="txtNewsSubjectFilter" Name="NewsSubject" Type="String" ConvertEmptyStringToNull="False"    />
         <asp:ControlParameter ControlID="txtNewsDateFilter" Name="NewsDate" Type="DateTime"  PropertyName="Text"   />
         </FilterParameters>

The Expected Results are it to filter on both columns. It is only filtering on the date.

If I Take the Date ControlParameter out and change the filter expression to only filter on NewsSubject, it works fine on the NewsSubject (see filter expression below:)

 FilterExpression="[NewsSubject] LIKE '%{0}%'"

Anybody have any ideas? or is this a bug in Microsoft WebForms?

Upvotes: 0

Views: 1115

Answers (1)

deb
deb

Reputation: 11

We ended up setting the FilterExpression in the OnFiltering Event in the code behind of this page. We removed the part of the expression that dealt with the date and the parameter if the date filter textbox was not populated (see FormFilteringExpressionforDate).

        protected void NewsDataSource_OnFiltering(object sender, SqlDataSourceFilteringEventArgs e)
    {
        var newsDateClause = FormFilteringExpressionforDate(e, "NewsDate");
        var firstConnector = FormConjunction(newsDateClause);
        var newsSubjectClause = FormFilteringExpressionForString(e, "NewsSubject");

        var filterExpression = string.Format("{0}{1}{2}", newsDateClause, firstConnector, newsSubjectClause);

        var sqlDataSourceView = sender as SqlDataSourceView;
        if (sqlDataSourceView == null)
            return;

        sqlDataSourceView.FilterExpression = filterExpression;
    }

    private string FormConjunction(string clause)
    {
        return clause == string.Empty ? "" : " AND ";
    }

    private string FormFilteringExpressionForString(SqlDataSourceFilteringEventArgs e, string columnName)
    {
        return string.Format("{0} LIKE '%{1}%' ", columnName, e.ParameterValues[columnName]);
    }

    private string FormFilteringExpressionforDate(SqlDataSourceFilteringEventArgs e, string columnName)
    {
        var columnValue = e.ParameterValues[columnName];

        if (columnValue == null)
        {
            e.ParameterValues.Remove(columnName);
            return string.Empty;
        }

        var convertedColumnValue = ((DateTime)columnValue).ToString("MM/dd/yyyy");

        var dateClause = string.Format("{0} = '{1}'", columnName, convertedColumnValue);           
        return dateClause;
    }

Here's the markup code (we did have to keep the FilterExpression defined in the SqlDataSource as a placeholder... the convert did not work):

        <asp:SqlDataSource ID="NewsDataSource" runat="server"     OnFiltering="NewsDataSource_OnFiltering"
        ConflictDetection="CompareAllValues" 
        ConnectionString="<%$ ConnectionStrings:ConnectionString %>" 
        SelectCommand="SELECT * FROM [News]" 
        FilterExpression="CONVERT([NewsDate], 'System.String') LIKE '%{0}%' AND [NewsSubject] LIKE '%{1}%'"
        OldValuesParameterFormatString="original_{0}" 
        ProviderName="<%$ ConnectionStrings:ConnectionString.ProviderName %>" >
        <FilterParameters>
            <asp:ControlParameter ControlID="txtNewsDateFilter" Name="NewsDate" Type="DateTime" />
            <asp:ControlParameter ControlID="txtNewsSubjectFilter" Name="NewsSubject" Type="String" ConvertEmptyStringToNull="False" />
        </FilterParameters>

Upvotes: 1

Related Questions