Link to home
Start Free TrialLog in
Avatar of cdemott33
cdemott33Flag for United States of America

asked on

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
Avatar of Ravi Vaddadi
Ravi Vaddadi
Flag of United States of America image

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()
Avatar of cdemott33

ASKER

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

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}>)
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.
ASKER CERTIFIED SOLUTION
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial