CODEWITHSUNDEEP
c#sqlsql-server-2008non-ascii-characters
user609511
user609511

Reputation: 4261

inserted accent into SQL

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 nvarchar

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

Upvotes: 3

Views: 4502

Answers (4)

Bridge
Bridge

Reputation: 30721

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.

Upvotes: 2

Davin Tryon
Davin Tryon

Reputation: 67336

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

Upvotes: 4

podiluska
podiluska

Reputation: 51514

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)

Upvotes: 2

devio
devio

Reputation: 37225

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.

Upvotes: 2

Related Questions