Sqldatasource has a filter property. Can I set it to part of a word?

Question

User-1422203677 posted

Suppose I have a gridview, plus a control that displays each letter of the alphabet.  When the user clicks one letter, for example "W", I want only names that start with W to be displayed. If he clicks 'All', I want all records to be displayed. 

I know how to do this by passing an extra argument to my sql procedure, but I noticed recently that sqldatasource has a 'filter' property.  You can say, for instance, that the first name has to equal "WALTER". 

My question is whether you can be more general, and have the filter specify that the first name starts with "W".

Also, how do you turn off the filter.

Thanks.

Answer 1

User-1902643333 posted

Just set to W*

For more, please see: https://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx

Wildcard Characters

Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison.If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]).If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]).A wildcard is allowed at the start and end of a pattern, or at the end of a pattern, or at the start of a pattern.For example:

"ItemName LIKE '*product*'"

"ItemName LIKE '*product'"

"ItemName LIKE 'product*'"

Wildcard characters are not allowed in the middle of a string.For example, 'te*xt' is not allowed.

</div> </div>

Answer 2

User36583972 posted

Hi  Gaston Leblanc,

My question is whether you can be more general, and have the filter specify that the first name starts with "W".

You can specify FilterExpression along with the SelectCommand, using which the records are filtered based on the value entered in the TextBox using the LIKE statement.

I have made a sample on my side. The following code for your reference.

HTML:

     <asp:TextBox ID="txtSearch" runat="server"></asp:TextBox>
            <hr />
            <asp:GridView ID="GridView1" HeaderStyle-BackColor="#3AC0F2" HeaderStyle-ForeColor="White"
                runat="server" AutoGenerateColumns="false" DataSourceID="GridDataSource" AllowPaging="true">
                <Columns>
                    <asp:BoundField DataField="Name" HeaderText="Name" ItemStyle-Width="150" />
                    <asp:BoundField DataField="City" HeaderText="City" ItemStyle-Width="150" />
                    <asp:BoundField DataField="Address" HeaderText="Country" ItemStyle-Width="150" />
                </Columns>
            </asp:GridView>
            <asp:SqlDataSource ID="GridDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ConStr %>"
                SelectCommand="SELECT Name, City, Address FROM TestTable" FilterExpression="Name LIKE '{0}%'">
                <FilterParameters>
                    <asp:ControlParameter Name="Name" ControlID="txtSearch" PropertyName="Text" />
                </FilterParameters>
            </asp:SqlDataSource>

Best Regards,

Yohann Lu