Showing posts with label filterexpression. Show all posts
Showing posts with label filterexpression. Show all posts

Monday, March 12, 2012

Issue with SQLDataSource and FilterExpression

I'm not sure exactly how the FilterExpression works. I have a sqldatasource, stored procedure, and GridView. My stored procedure basicly a select statement to populate my gridview, it included the fields I want to filter on. In my codebehind file I build a WHERE Clause based on the entries a user makes. Then I add the my FilterExpr variable to the SqlDataSource1.FilterExpression = FilterExpr. My SqlDataSource has a number of control parameters that match the textboxes a users enters into.

My question, I guess is does my stored procedure need the variables matching my controlparameters for my sqldatasource? Or how does this work? My GridView is returning all rows no matter what I enter into the filter textboxes (first, last, etc...)

MY SQLSDATASOURCE

 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString%>" SelectCommand="ClientSearch" SelectCommandType="StoredProcedure"> <FilterParameters> <asp:ControlParameter ControlID="SearchLastName" Name="LastName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchFirstName" Name="FirstName" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchEmail" Name="Email" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchAddress" Name="Address" PropertyName="Text" ConvertEmptyStringToNull="true" /> <asp:ControlParameter ControlID="SearchComment" Name="Comment" PropertyName="Text" ConvertEmptyStringToNull="true" /> </FilterParameters> </asp:SqlDataSource>
MY CODEBEHIND

Dim FilterExprAs String

If SearchLastName.Text =""And _
SearchFirstName.Text =""And _
SearchEmail.Text =""And _
SearchComment.Text =""And _
SearchAddress.Text =""Then
lblMessage.Text ="You didn't enter any search parameters. Please try Again."
Me.GridViewSearch.DataSourceID = ""
Exit Sub
Else
Me.GridViewSearch.DataSourceID = "SqlDataSource1"
End If

FilterExpr = ""
If SearchLastName.Text <> "" Then
FilterExpr = FilterExpr & "LastNameLike'" & _
SearchLastName.Text &"%" &"' AND "
End If

If SearchFirstName.Text <> "" Then
FilterExpr = FilterExpr & "FirstNameLike'" & _
SearchFirstName.Text &"%" &"' AND "
End If
If SearchEmail.Text <> "" Then
FilterExpr = FilterExpr & "EmailLike'" & _
SearchEmail.Text &"%" &"' AND "
End If
If SearchComment.Text <> "" Then
FilterExpr = FilterExpr & "[Comments]Like'" & "%" & _
SearchComment.Text &"%" &"' AND "
End If
If SearchAddress.Text <> "" Then
FilterExpr = FilterExpr & "[Address]Like'" & "%" & _
SearchAddress.Text &"%" &"' AND "
End If

If Right(FilterExpr, 4) = "AND "Then
FilterExpr = Left(FilterExpr, Len(FilterExpr) - 4)
End If
Try
Me.SqlDataSource1.FilterExpression = FilterExpr
Me.SqlDataSource1.DataBind()
Me.GridViewSearch.DataBind()
Me.lblMessage.Text =Me.SqlDataSource1.FilterExpression
Catch objExceptionAs SqlException
Dim objErrorAs SqlError
For Each objErrorIn objException.Errors
Response.Write(objError.Message)
Next
End Try
End Sub

MY SPROC

GOALTER PROCEDURE [dbo].[ClientSearch]ASSELECT C.ClientID, C.FirstName, C.LastName, A.Address, C.Comments, C.EMailFROM tblClient CINNERJOIN tblClientAddresses AON C.ClientID = A.ClientID
Remove all the <FilterParameters> and it should work fine.