Solved

How to make an accent insensitive filter in a sqldatasource?

Posted on 2008-10-02
17
2,628 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with error when uploading excel file 3 27
Alert on Server memory 2 21
ASP.NET MVC identity 6 26
DataTables + iCheck + pagination Issue 2 19
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

770 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