Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2812
  • Last Modified:

How to make an accent insensitive filter in a sqldatasource?

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
Stephan Bourgeois
Asked:
Stephan Bourgeois
  • 10
  • 7
2 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Stephan BourgeoisIT SpecialistAuthor Commented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
Have you rebooted SQL Server after changing to French_CI_AI collation?  Try that.
0
 
Stephan BourgeoisIT SpecialistAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Stephan BourgeoisIT SpecialistAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Stephan BourgeoisIT SpecialistAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
Stephan BourgeoisIT SpecialistAuthor Commented:
How could I trace through data?
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
Stephan BourgeoisIT SpecialistAuthor Commented:
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
 
Stephan BourgeoisIT SpecialistAuthor Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
Good find!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now