inserted accent into SQL

Question

i have the probleme with accent (example: é,à,è, etc). when i inserted directly on SQL Server 2008 Management INSERT INTO LETTRE_VOIT (LIB_PORT) VALUES ('Payé') it work well. but when i load this syntax on C#, it not work.

 try
            {
                using (var connectionWrapper = new Connexion())
                {
                    var connectedConnection = connectionWrapper.GetConnected();


                    string sql_Syntax = Outils.LoadFileToString(HttpContext.Current.Server.MapPath("~/SQL/UpdateEtClotureList.sql"));
                    SqlCommand comm_Command = new SqlCommand(sql_Syntax, connectionWrapper.conn);
                    comm_Command.Parameters.AddWithValue("@CHAUFFEUR", CHAUFFEUR);
                    comm_Command.Parameters.AddWithValue("@Trans", ClotList);
                    string IdOrdre = "";
                    SqlDataReader readerOne = comm_Command.ExecuteReader();
                    while (readerOne.Read())
                    {
                        if (IdOrdre != "") IdOrdre += ",";
                        IdOrdre += readerOne["NO_ORDRE"].ToString();                    
                    }
                    return IdOrdre;
                }
            }
            catch (Exception excThrown)
            {
                throw new Exception(excThrown.Message);
            }

the content of UpdateEtClotureList.sql:

DECLARE @AfterUpdate table (
    NO_ORDRE_Temp int NOT NULL, 
    CODE_DEST_Temp varchar(10) NOT NULL,
    DATE_CLOTUR_Temp datetime NOT NULL, 
    CODE_CLIENT_Temp varchar(20) NOT NULL,
    MODAL_MODE_Temp char(1) NOT NULL,
    MODAL_PORT_Temp bit NOT NULL,
    LIVRS_EXPRS_Temp bit NOT NULL,
    ENLEV_UNITE_Temp int NOT NULL,
    NBR_COLIS_Temp int NOT NULL,
    POID_Temp decimal(10, 2) NOT NULL,
    ENLEV_CREMB_Temp decimal(10, 2)  NULL,
    ENLEV_DECL_Temp decimal(10, 2)  NULL

  )  

UPDATE ORDRE 
SET STATUT_ORDRE = 2, DATE_CLOTUR = GETDATE(), CHAUFFEUR = @CHAUFFEUR
OUTPUT inserted.NO_ORDRE, inserted.CODE_DEST, inserted.DATE_CLOTUR, inserted.CODE_CLIENT, inserted.MODAL_MODE, inserted.MODAL_PORT,inserted.LIVRS_EXPRS,inserted.ENLEV_UNITE,  inserted.NBR_COLIS,inserted.POID,inserted.ENLEV_CREMB,inserted.ENLEV_DECL INTO @AfterUpdate
WHERE STATUT_ORDRE = 1 AND (TRANSPORTEUR IN (SELECT ParsedString From dbo.ParseStringList(@Trans)))



INSERT INTO LETTRE_VOIT 

OUTPUT inserted.NO_ORDRE

select rsVoit.NOID, NO_ORDRE_Temp, rsOrdre.CODE_DEST_Temp,rsOrdre.DATE_CLOTUR_Temp, rsOrdre.CODE_CLIENT_Temp, rsOrdre.MODAL_MODE_Temp, rsOrdre.MODAL_PORT_Temp,
CASE rsOrdre.MODAL_PORT_Temp
            WHEN 'false' THEN 'Payé'
            ELSE 'Du'
        END, 
rsOrdre.LIVRS_EXPRS_Temp,
CASE rsOrdre.LIVRS_EXPRS_Temp
            WHEN 'false' THEN 'Long'
            ELSE 'Express'
        END, 
  rsOrdre.ENLEV_UNITE_Temp, LIBELLE, NBR_COLIS_Temp,POID_Temp,ENLEV_CREMB_Temp,ENLEV_DECL_Temp
from @AfterUpdate rsOrdre
inner join
(
  select CODE_DEST,MODAL_MODE, MODAL_PORT, LIVRS_EXPRS,ENLEV_UNITE, ROW_NUMBER() over (order by CODE_DEST) as NOID
  from ORDRE
  group by CODE_DEST,MODAL_MODE,MODAL_PORT,LIVRS_EXPRS,ENLEV_UNITE
) rsVoit on rsVoit.CODE_DEST = rsOrdre.CODE_DEST_Temp and rsVoit.MODAL_MODE = rsOrdre.MODAL_MODE_Temp
and rsVoit.MODAL_PORT = rsOrdre.MODAL_PORT_Temp and rsVoit.LIVRS_EXPRS = rsOrdre.LIVRS_EXPRS_Temp
and rsVoit.ENLEV_UNITE = rsOrdre.ENLEV_UNITE_Temp

LEFT JOIN T_UNITE ON rsOrdre.ENLEV_UNITE_Temp = T_UNITE.NOID

order by rsVoit.NOID, NO_ORDRE_Temp

the result is Pay*, instead of Payé , * is a square symbol.

Thanks you in advance.

PS: i use nvarchar instead of varchar type

when i change the syntax:

....
CASE rsOrdre.MODAL_PORT_Temp
            WHEN 'false' THEN N'Payé'
            ELSE 'Du'
        END, 
rsOrdre.LIVRS_EXPRS_Temp,
.....

i got: result

and when i look via SQL Profiler: enter image description here

(Since you've edited that your column is nvarchar already, I suspect my previous comment was the issue - I've posted it as an answer instead) — Bridge, Aug 10 '12 at 08:14
Could this be a font problem? Do you see the é if you select to text rather than grid? — podiluska, Aug 10 '12 at 08:36
What [collation](http://blog.sqlauthority.com/2007/08/30/sql-server-2005-find-database-collation-using-t-sql-and-ssms/) do you have set for your instance of SQL? — Davin Tryon, Aug 10 '12 at 08:37
Check what encoding you used when you saved the sql script file (ANSI vs Unicode). See my updated answer. — Davin Tryon, Aug 10 '12 at 08:49

score 4 · Answer 1 · edited May 23 '17 at 12:06

4

Could this be because you are using varchar insead of nvarchar? The nvarchar SQL type is what you want to use if you are storing characters outside of ASCII. nvarchar stores as unicode. This SO post covers the difference.

EDIT: Since the character in question is in the extended ASCII set, it probably isn't a problem to do with ASCII vs unicode (as pointed out by @podiluska).

EDIT 2: Have you checked the encoding on your sql script file (ANSI vs Unicode?). Check out this forum post. When saving the sql script file (UpdateEtClotureList.sql) choose "Save with Encoding" from the save button drop down and choose Unicode (Codepage 65001).

edited May 23 '17 at 12:06

Community

1
1

answered Aug 10 '12 at 07:47

Davin Tryon

66,517
15
143
132

I don't think so. An é is an ascii character so it is fine in a varchar. – podiluska Aug 10 '12 at 08:30
1

@podiluska yeah, you are [right](http://www.asciitable.com/). But, it definitely looks like an encoding problem. – Davin Tryon Aug 10 '12 at 08:34

Bridge · Answer 2 · 2012-08-10T09:02:58.613

2

INSERT INTO LETTRE_VOIT (LIB_PORT) VALUES ('Payé')

does work, but in general you should use:

INSERT INTO LETTRE_VOIT (LIB_PORT) VALUES (N'Payé')

with the N to indicate that it's a unicode string.

edited Aug 10 '12 at 09:02

answered Aug 10 '12 at 08:13

Bridge

29,818
9
60
82

@podiluska hmm - you're right with this particular character, I thought it was "just another unicode character". In general it holds true though. – Bridge Aug 10 '12 at 09:01
absolutely - that was my initial thought – podiluska Aug 10 '12 at 09:14

podiluska · Accepted Answer · 2012-08-10T08:54:22.430

2

Is LoadFileToString corrupting the SQL? What happens if you print that out? Do you still see the é character?

What if you change the SQL to

WHEN 'false' THEN 'Pay' +CHAR(233)

edited Aug 10 '12 at 08:54

answered Aug 10 '12 at 08:48

podiluska

50,950
7
98
104

score 2 · Answer 4 · answered Aug 10 '12 at 08:52

2

After checking NVARCHAR columns and N'' Unicode string literals, my guess is that Outils.LoadFileToString() does not use the correct Encoding to read the .sql file.

answered Aug 10 '12 at 08:52

devio

36,858
7
80
143

inserted accent into SQL

4 Answers4