How to make an accent insensitive filter in a sqldatasource?

Use LCase OR UCase to convert string to upper or lower case.  Alternatively, you can use txtFilter.Text.ToUpper or txtFilter.Text.ToLower.

Then in your code to implement the filter (i.e. where your SQL LIKE statement is, just convert the column to lower/upper for the comparison.  Just ensure you match whatever you do to filer.  Therefore, for the example below you would use lower(columnname).

e.g.
select 'tEsT', lower('tEsT'), upper('tEsT')

Protected Sub txtFilter_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtFilter.TextChanged
        FilterHiddenField.Value = LCase("%" & txtFilter.Text & "%")
        bFilterChanged = True
        Session("bSelectionChanged") = True
        RaiseEvent SelectionChanged(Me, e)
        Session("CustID") = Nothing
        gvDisplayNames.SelectedIndex = -1
        gvDisplayNames.DataBind()
       
    End Sub

Select allOpen in new window

And so for clarity...

If you are using the Filter Expression: http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.filterexpression(VS.85).aspx

You could do this:

FilterExpression="Lower(ColumnName) LIKE '%text%'"

This is assuming, you use LCase in VB code on text change of filter text.  Alternatively, you can convert both in the filter expression.

FilterExpression="Lower(ColumnName) LIKE Lower('%teXt%')"

I made a mistake in my explanation.
The following phrase:
result using a sqldatasource with ASP.NET 2.0 is case sensitive

Should read:
result using a sqldatasource with ASP.NET 2.0 is accent sensitive

When I write a query in SQL server management studio like:
SELECT * FROM tblProfile WHERE LastName LIKE "%Gregoire%
Will result : Gregoire and Grégoire.

The sqldatasource select query is set llike the following, where @Filter is bind to FilterHiddenField.Value:
SELECT CustID, DisplayNameEn FROM dbo.tblProfiles WHERE (DisplayNameEn LIKE @Filter) OR (CustID LIKE @Filter) ORDER BY DisplayNameEn

If FilterHiddenField.Value equal to %Grégoire%, it will return only the Grégoire with an accent. As per my database collation, it should be accent insensitive, but it is not.

You will have to set the French_CI_AI collation which AI sets accent-insensitivity for your SqlDataSource.  I have not done this myself before, but seems like that is the issue (or at least difference from SQL versus .NET coding).

Have you rebooted SQL Server after changing to French_CI_AI collation?  Try that.

The server was initialy install with a collation French_CI_AI collation and it as been rebooted many times since it is my own PC with SQL 2005 Developper Edition.

There is a collation setting in the SQLDATASOURCE web control?

The query works fine on the SQL server.

Try changing your query like this in the SqlDataSource:

SELECT CustID, DisplayNameEn FROM dbo.tblProfiles 
WHERE (DisplayNameEn COLLATE French_CI_AI LIKE @Filter) OR (CustID COLLATE French_CI_AI LIKE @Filter) 
ORDER BY DisplayNameEn

Select allOpen in new window

The other thought, is that HTML page is converting to entity representations and it is messing with the search as everything I have seen indicates that the SQL Server collation should have made this work as is, but hopefully that will help.

I change the SELECT statement for:
SELECT CustID, DisplayNameEn FROM dbo.tblProfiles WHERE (DisplayNameEn COLLATE French_CI_AI LIKE @Filter) OR (CustID LIKE @Filter) ORDER BY DisplayNameEn

and it did not fix the problem.

I am thinking writing a store procedure to return the record. Since it works on the SQL server, the store procedure should return the correct records, since it is executed on the server itself.

I will le you know of the result.

That should work.  For the above, not sure it will help, but since you are also looking at CustID it also needs to the COLLATE statement.  However, using stored procedure is probably best bet.

It's unbelievable; I created a store procedure bind to a hidden field control that contains the search text like "%Grégo %".
I tested the sqldatasource in Visual Studio using the Configure Datasource wizard and the test returned all the names with accent or not like Grégoire & Gregory.
I ran the page, using that same store procedure and the page is accent sensitive.
There is nothing easy. I will put parts of the code to help you find a solution.

Thanks for your help.

            <asp:TextBox ID="txtFilter" runat="server" AutoPostBack="True"></asp:TextBox>
            <asp:Button ID="btnFilter" runat="server" Text="Filter" />
            <asp:HiddenField ID="FilterHiddenField" runat="server" Value="%" />
 
 
<asp:SqlDataSource ID="sqldsProfilesList" runat="server" ConnectionString="<%$ ConnectionStrings:ITDBConnectionStringLocalHost %>"
    SelectCommand="spSearchProfiles" ProviderName="<%$ ConnectionStrings:ITDBConnectionStringLocalHost.ProviderName %>" SelectCommandType="StoredProcedure">
    <SelectParameters>
        <asp:ControlParameter ControlID="FilterHiddenField" DefaultValue="%" Name="SearchText"
            PropertyName="Value" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>
 
 
                        <asp:GridView ID="gvDisplayNames" runat="server" AllowPaging="True" AllowSorting="True"
                            AutoGenerateColumns="False" CellPadding="4" DataKeyNames="CustID" DataMember="DefaultView"
                            DataSourceID="sqldsProfilesList" ForeColor="#333333" GridLines="None" Font-Names="Verdana" Font-Size="Small">
                            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <Columns>
                                <asp:TemplateField ShowHeader="False">
                                    <ItemTemplate>
                                        <asp:Button ID="btnSelectProfile" runat="server" CausesValidation="False" CommandName="Select" CommandArgument='<%# Eval("CustID") %>'
                                            Text="Select" />
                                    </ItemTemplate>
                                </asp:TemplateField>
                                <asp:BoundField DataField="EntryTypeCode" HeaderText="EntryTypeCode" ReadOnly="True"
                                    SortExpression="EntryTypeCode" Visible="False" />
                                <asp:BoundField DataField="CustID" HeaderText="Cust ID" ReadOnly="True" SortExpression="CustID" />
                                <asp:BoundField DataField="DisplayNameEn" HeaderText="Display Name En" SortExpression="DisplayNameEn" />
                            </Columns>
                            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
                            <EditRowStyle BackColor="#999999" />
                            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
                            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
                            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                        </asp:GridView>

Select allOpen in new window

Try to trace through data and see if it is indeed converting to the HTML entity for those accented characters.  That may be causing the issue.  But otherwise, not sure why you are getting different responses to same procedure.

How could I trace through data?

The Gridview control bind 4 times before it display.
The first time, the control has 9 rows and it contains accented and not accented records.
The successive times, it contains only 3 rows with accented characters.

sqldsProfilesList sqldsProfilesList databound 156.397339047281 156.216986
aspx.page End Raise PostBackEvent 156.397491580634 0.000153
aspx.page Begin LoadComplete 156.397540469529 0.000049
aspx.page End LoadComplete 162.706179137293 6.308639
aspx.page Begin PreRender 162.706299823022 0.000121
gvDisplayNames_DataBinding Binding the sqlProfileList 162.758951766216 0.052652
sqldsProfilesList sqldsProfilesList databound 219.247446533009 56.488495
gvDisplayNames_DataBinding Binding the sqlProfileList 219.285741064856 0.038295
sqldsProfilesList sqldsProfilesList databound 225.929383203731 6.643642
gvDisplayNames_DataBinding Binding the sqlProfileList 225.977386232049 0.048003
sqldsProfilesList sqldsProfilesList databound 233.547221225044 7.569835
gvDisplayNames_DataBinding Binding the sqlProfileList 233.667565570485 0.120344
sqldsProfilesList sqldsProfilesList databound 235.660573899755 1.993008

Will try to do more experimentation later

Good find!