FilterExpression Help Needed

cdemott33
cdemott33 used Ask the Experts™
on
I'm trying to dynamically create a FilterExpression in my SqlDataSource but it doesn't seam to be working.  Is this not possible?  Could someone please look at my code (attached txt file) and let me know if I'm doing something wrong.

Thanks!


dynamicFilterExpression.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use
SqlDataSource1.FilterExpression = sb.ToString()
instead of
SqlDataSource1.FilterExpression += sb.ToString()
Also you filtering expression might be incorrect in some scenarios. There is a possibility that the expression might start with 'AND' which could incorrect. Please place a break point and check the expression that is generated from sb.ToString()

Author

Commented:
Thanks SriVaddadi - Good Point!  I rewrote the code to address missing dates, but still it's not working.  Let me give you an example.  If I leave ALL the box empty and just type the word "Sam" in the Name textbox and hit submit it shows all the results and doesn't filter anything.

Any thoughts!
Partial Class sysex_FilterParameters
    Inherits System.Web.UI.Page


    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        ' Handle missing dates
        Dim strStartDate As String = tbStartDate.Text
        Dim strEndDate As String = tbEndDate.Text

        If strStartDate = "" Then
            strStartDate = "1/1/2009"
        End If

        If strEndDate = "" Then
            strEndDate = Now()
        End If

        Dim sb As StringBuilder = New StringBuilder

        sb.Append("(SALEDATE > '{0}' AND SALEDATE < '{1}'")

        If Not tbID.Text = "" Then
            sb.Append(" AND ID = '{2}'")
        End If

        If Not tbName.Text = "" Then
            sb.Append(" AND NAME LIKE '%{3}%'")
        End If

        sb.Append(")")

        SqlDataSource1.FilterExpression = sb.ToString()

    End Sub
End Class

Open in new window

Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

When you say '{0} amd '{1}' what do you mean? when are you passing them?

Where are you setting the values for {0},{1},{2},{3}

May be you want to use sb.AppendFormat and not sb.Append

like sb.AppendFormat("(SALEDATE > '{0}' AND SALEDATE < '{1}'",<value of {0}>,<value of {1}>)

Author

Commented:
AppendFormat doesn't work either.  I was following the instruction here:

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.filterexpression.aspx

I'm assuming the the way it work is the {0} matches up to the first ControlParameter property within my FilterParameters block.  So the match up (I believe) is as follows:

{0} = tbStartDate
{1} = tbEndDate
{2} = tbID
{3) = tbName

I should tell you this.  When I first tried using a FilterExpression I did everything in the ASPX page.  It woked but ONLY if every single field was filled out.  If any of the fields were blank then nothing happened.  It just showed all the records.

Here's what I need to do.  I need to allow the user to decide how they want to search.  Maybe they only want to enter a Name and leave all the other fields blank.  Maybe they want to enter and ID and a START and END date and leave the Name field blank or maybe they just want to enter the START and END dates and leave the NAME and ID blank.

My problem is the blank fields.  

So I thought if I could dynamacally build the FilterExpression based on the fields the user filled out it that would be best, however it doesn't seam to work.

Here's the original code, which ONLY works if you enter ALL the fields.  

        <asp:SqlDataSource ID="SqlDataSource1" runat="server"
            ConnectionString="<%$ ConnectionStrings:OMS_ConnectionString %>"            
            SelectCommand="SELECT * FROM [A_TEST]"
            FilterExpression="(SALEDATE > '{0}' AND SALEDATE < '{1}' AND ID = '{2}' AND NAME LIKE '%{3}%')">
                <FilterParameters>
                    <asp:ControlParameter ControlID="tbStartDate" Name="Start Date" PropertyName="Text" Type="DateTime" />
                    <asp:ControlParameter ControlID="tbEndDate" Name="End Date" PropertyName="Text" Type="dateTime" />
                    <asp:ControlParameter ControlID="tbID" Name="ID" PropertyName="Text" Type="String" />
                    <asp:ControlParameter ControlID="tbName" Name="Name" PropertyName="Text" Type="String" />
                </FilterParameters>  
        </asp:SqlDataSource>

Maybe there's a better way of doing this.  I don't know.  Any thoughts?  Maybe I just missing something or there is a way to handle the blank fields that I haven't thought about yet.  Any help would be appreciated.
Most Valuable Expert 2012
Top Expert 2014
Commented:
You already have control parameters then why are you manually building the filter?

Also, dont use space in column names in DB as it creates problems for you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial