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 StringIf 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 IfFilterExpr = ""
If SearchLastName.Text <> "" Then
FilterExpr = FilterExpr & "LastNameLike'" & _
SearchLastName.Text &"%" &"' AND "
End IfIf SearchFirstName.Text <> "" Then
FilterExpr = FilterExpr & "FirstNameLike'" & _
SearchFirstName.Text &"%" &"' AND "
End If
If SearchEmail.Text <> "" Then
FilterExpr = FilterExpr & "Like'" & _
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 IfIf 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.ClientIDRemove all the <FilterParameters> and it should work fine.
No comments:
Post a Comment