cdemott33
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
Thanks!
dynamicFilterExpression.txt
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()
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!
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
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}>)
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
ASKER
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_Conn ectionStri ng %>"
SelectCommand="SELECT * FROM [A_TEST]"
FilterExpression="(SALEDAT E > '{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.
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_Conn
SelectCommand="SELECT * FROM [A_TEST]"
FilterExpression="(SALEDAT
<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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SqlDataSource1.FilterExpre
instead of
SqlDataSource1.FilterExpre