FilterExpression Help Needed

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
cdemott33Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SriVaddadiCommented:
use
SqlDataSource1.FilterExpression = sb.ToString()
instead of
SqlDataSource1.FilterExpression += sb.ToString()
0
SriVaddadiCommented:
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()
0
cdemott33Author 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

0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

SriVaddadiCommented:
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}>)
0
cdemott33Author 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.
0
CodeCruiserCommented:
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.