Solved

How to make an accent insensitive filter in a sqldatasource?

Posted on 2008-10-02
17
2,578 Views
Last Modified: 2013-11-07
Hi,

I have a textbox use to filter a Gridview.
The sql collation is set to French_CI_AI.

The search result when I write a select query in SQL server management studio works fine, it return all the records with accent or not. But the result using a sqldatasource with ASP.NET 2.0 is case sensitive.

I absolutly need to have a case insensitive search.

Any experts here who know the solution. It must be very simple, I just don't know where to look for.

Thanks
Protected Sub txtFilter_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles txtFilter.TextChanged

        FilterHiddenField.Value = "%" & txtFilter.Text & "%"

        bFilterChanged = True

        Session("bSelectionChanged") = True

        RaiseEvent SelectionChanged(Me, e)

        Session("CustID") = Nothing

        gvDisplayNames.SelectedIndex = -1

        gvDisplayNames.DataBind()

       

    End Sub

Open in new window

0
Comment
Question by:SergeGregoire
  • 10
  • 7
17 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22633426
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

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22633464
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%')"
0
 
LVL 1

Author Comment

by:SergeGregoire
ID: 22634371
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.

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22635035
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).
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22635055
Have you rebooted SQL Server after changing to French_CI_AI collation?  Try that.
0
 
LVL 1

Author Comment

by:SergeGregoire
ID: 22637504
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.

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22637741
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

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22637753
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 1

Author Comment

by:SergeGregoire
ID: 22660530
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.
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22660690
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.
0
 
LVL 1

Author Comment

by:SergeGregoire
ID: 22662068
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>

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22662185
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.
0
 
LVL 1

Author Comment

by:SergeGregoire
ID: 22667507
How could I trace through data?
0
 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 22667695
You would enable tracing in ASPX page or in Web.config; however, my intent was to get output of your query -- if you output to trace which is a web page also it may interpret the HTML entities and they look right on screen.

You can try debugging: http://www.beansoftware.com/ASP.NET-Tutorials/ASP.NET-Code-Debugging.aspx

Or writing the generated query to a log file.

With debugging, we are looking for the value of @Filter and/or the entire filter query to ensure that it is coming out to the SQL statement we expect.  
0
 
LVL 1

Author Comment

by:SergeGregoire
ID: 22670390
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
0
 
LVL 1

Accepted Solution

by:
SergeGregoire earned 0 total points
ID: 22671904
I found the problem.

I commented out the following code:
        'If txtFilter.Text.Length > 0 Then
        '    sqldsProfilesList.FilterExpression = "DisplayNameEn LIKE '*" & txtFilter.Text & "*' OR CustID LIKE '*" & txtFilter.Text & "*'"
        '    gvDisplayNames.DataBind()

        'End If

Was setting up a FilterExpression on the datasource. It was like that 2 level of filter was applied. This filter expression was not needed, since the Gridview pass the parameter from the hidden field and the store procedure return the filtered records.

Looks like the FilterExpression method does not support Case Insensitive collation.

Thanks anyway and I will give you the points, since you put me on the right track.
    Protected Sub dvProfileDetail_DataBinding(ByVal sender As Object, ByVal e As System.EventArgs) Handles dvProfileDetail.DataBinding

        If bFilterChanged Then

            sqldsProfileDetail.FilterExpression = "CustID='0000000'"

            gvDisplayNames.SelectedIndex = -1

            strCustID = Nothing

            Session("CustID") = ""

            'bFilterChanged = False

            Session("bSelectionChanged") = True
 
 

        Else

            strCustID = gvDisplayNames.SelectedValue

            Session("CustID") = strCustID

        End If

        'If txtFilter.Text.Length > 0 Then

        '    sqldsProfilesList.FilterExpression = "DisplayNameEn LIKE '*" & txtFilter.Text & "*' OR CustID LIKE '*" & txtFilter.Text & "*'"

        '    gvDisplayNames.DataBind()
 

        'End If
 
 

    End Sub

Open in new window

0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 22672785
Good find!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video discusses moving either the default database or any database to a new volume.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now