Problem with asp:SqlDataSource LIKE condition with acute caracters

[alain.hogue]

I have a table "tblCards" with a field "Titre" that contain french
words (Hébert, Gagné, Hélène, etc....) in an database with
SQLEXPRESS 2005.

I have used the asp:SqlDataSource to filter the SELECT statement
according to a Textbox the user can type into. When used with the LIKE
condition using any acute caracters the SELECT statement return
nothing, unless the acute caracters is replaced with an ampersand "%".
Even the underscore "_" does not work! I have tried my logic with SQL
2000 and it's working just fine. Here the setup I have:

<asp:SqlDataSource SelectCommand="SELECT * FROM [tblCards] WHERE
([Titre] LIKE '%' + @Search + '%')>
<SelectParameters>
<asp:ControlParameter ControlID="txtSearch" Name="Search"
PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Now if I type a word like "Hébert" in the "txtSearch" Textbox nothing
is returned. But if I type "H%bert" it's working. Also typing "H_bert"
does not work!?!

Is this standard behavior or am I doing something wrong? Please help!

Alain

 

[john smith]

I have a table "tblCards" with a field "Titre" that contain french
words (Hébert, Gagné, Hélène, etc....) in an database with
SQLEXPRESS 2005.

I have used the asp:SqlDataSource to filter the SELECT statement
according to a Textbox the user can type into. When used with the LIKE
condition using any acute caracters the SELECT statement return
nothing, unless the acute caracters is replaced with an ampersand "%".
Even the underscore "_" does not work! I have tried my logic with SQL
2000 and it's working just fine. Here the setup I have:

<asp:SqlDataSource SelectCommand="SELECT * FROM [tblCards] WHERE
([Titre] LIKE '%' + @Search + '%')>
<SelectParameters>
<asp:ControlParameter ControlID="txtSearch" Name="Search"
PropertyName="Text" Type="String" />
</SelectParameters>
</asp:SqlDataSource>

Now if I type a word like "Hébert" in the "txtSearch" Textbox nothing
is returned. But if I type "H%bert" it's working. Also typing "H_bert"
does not work!?!

Is this standard behavior or am I doing something wrong? Please help!

Alain

3 things:

1) What's with the LIKE '%' + @Search + '%' ? No need for odd string
concatenation... That should be LIKE '%@Search%' - I'm surprised that
doesn't even throw an exception...

2) é isn't a "normal" character in html (well, depending on encoding
type used), it's represented by é or &eacute; - you don't want that
as-is in your SQL query's parameter. You'll have to re-convert those
first (I'd look into ISO-8859-1/windows-1252/UTF-8 character encoding too).

3) No need to double post at such short interval...

 

[Alain Hogue]

1) What's with the LIKE '%' + @Search + '%' ? No need for odd string

concatenation... That should be LIKE '%@Search%' - I'm surprised that
doesn't even throw an exception...

That's what is being created automatically with the SqlDataSource Task
"Configure Data Source..." when you specify a WHERE condition based on an
existing control. I did not create this, only using it. In fact doing it
your way breaks it. No exceptions is thrown, it just return nothing all the
time.

2) é isn't a "normal" character in html (well, depending on encoding type
used), it's represented by é or &eacute; - you don't want that as-is
in your SQL query's parameter. You'll have to re-convert those first (I'd
look into ISO-8859-1/windows-1252/UTF-8 character encoding too).

After rebooting the server everything is working correctly now. I supposed I
should have rebooted after changing the field collation to "FRENCH_CI_AI"
instead of just restarting SQLEXPRESS.

3) No need to double post at such short interval...

Oups, sorry for that, it's my error and my eagerness...