locked
Linq to entities and SQL_Latin1_General_CP1_CI_AI RRS feed

  • Question

  • I'm developing an app where I need to insert names using special characters like áéíóúñ and I'm using linq to entities with Visual C#.

    My database server is SQL 2008 express and the default collation of my database is SQL_Latin1_General_CP1251_CI_AS, however I changed the collation of the fields to SQL_Latin1_General_CP1_CI_AI where I need to insert the special characters mentioned above.

    If I insert names with special characters using code similar to this:

    Cliente cli = new Cliente()
                {
                    Nombres = cliente.nombres,
                    ApellidoP = cliente.apellidoP,
                    ApellidoM = cliente.apellidoM,
                    FechaNac = cliente.fechaNacimiento
                };
                context.clientes.AddObject(cli);
                context.SaveChanges();
    

    The special characters are translated to their equivalent, but if I insert names with special characters directly in the database (management studio) I have no problems inserting those characters.

    What could be the problem?

     

    Thursday, October 20, 2011 8:02 PM

Answers

  • Hi jzapata,

    I also have SQL 2008 Express and have my collation set to SQL_Latin1_General_CP1_CI_AS and was able to insert the name you specified both directly into SQL using Mgmt Studio and also this the way below in EF and both times it didn't strip off the special characters:

     using (NorthwindEntities ctx = new NorthwindEntities())
                {
    
                    Table_1 tab = new Table_1();
    
                    tab.Name = "áéíóúñ";
                    tab.Id = System.Guid.NewGuid();
    
                    ctx.Table_1.AddObject(tab);
                    ctx.SaveChanges();
    
                }
    


    In my Table_1 in the database, the "Name" column is a varchar(50).  Are you definitely sure the special characters are present at the time you create the Cliente() object instance and when you add the object to the context?

     


    Tom Overton
    • Marked as answer by Alan_chen Friday, October 28, 2011 1:21 AM
    Friday, October 21, 2011 12:11 AM
  • Thanks both of you, I changed types to nvarchar and now its working. It seems like varchar type doesn't support the collation I was using.

     

    Regards

    Jorge

    • Marked as answer by jzapata Thursday, October 27, 2011 6:43 PM
    Thursday, October 27, 2011 6:43 PM

All replies

  • I'm developing an app where I need to insert names using special characters like áéíóúñ and I'm using linq to entities with Visual C#.

    My database server is SQL 2008 express and the default collation of my database is SQL_Latin1_General_CP1251_CI_AS, however I changed the collation of the fields to SQL_Latin1_General_CP1_CI_AI where I need to insert the special characters mentioned above.

    If I insert names with special characters using code similar to this:

     Cliente cli = new Cliente()
               
    {
                   
    Nombres = cliente.nombres,
                   
    ApellidoP = cliente.apellidoP,
                   
    ApellidoM = cliente.apellidoM,
                   
    FechaNac = cliente.fechaNacimiento
               
    };
                context
    .clientes.AddObject(cli);
                context
    .SaveChanges();

    The special characters are translated to their equivalent, but if I insert names with special characters directly in the database (management studio) I have no problems inserting those characters.

    What could be the problem?

    • Merged by Dummy yoyo Wednesday, October 26, 2011 6:55 AM Duplicate
    Thursday, October 20, 2011 6:12 PM
  •  
    Someone in the forum may be able to help you.
     
    Msdn.en-US.linqprojectgeneral
     
     
    Thursday, October 20, 2011 7:21 PM
  • Hi jzapata,

    I also have SQL 2008 Express and have my collation set to SQL_Latin1_General_CP1_CI_AS and was able to insert the name you specified both directly into SQL using Mgmt Studio and also this the way below in EF and both times it didn't strip off the special characters:

     using (NorthwindEntities ctx = new NorthwindEntities())
                {
    
                    Table_1 tab = new Table_1();
    
                    tab.Name = "áéíóúñ";
                    tab.Id = System.Guid.NewGuid();
    
                    ctx.Table_1.AddObject(tab);
                    ctx.SaveChanges();
    
                }
    


    In my Table_1 in the database, the "Name" column is a varchar(50).  Are you definitely sure the special characters are present at the time you create the Cliente() object instance and when you add the object to the context?

     


    Tom Overton
    • Marked as answer by Alan_chen Friday, October 28, 2011 1:21 AM
    Friday, October 21, 2011 12:11 AM
  • Hi  Tom, thanks for your reply. I changed fields collation after creating my project in visual studio, maybe the problem is in the configuration files (.edmx), do you know if there is way to check this?

    And yes I'm sure my characters are present when i create Cliente() object, but don't know how to see wheter they're present after adding object to context.

     

     


    • Edited by jzapata Friday, October 21, 2011 3:23 PM
    Friday, October 21, 2011 3:00 PM
  • One thing you could try is refresh you edmx.  Maybe make a small change to it and then select "Update Model from Database" to see if that helps. 

    I'm actually kind of surprised the collation would affect anything at all, as far as special characters.  The collation mainly comes into play on how SQL Server sorts things.


    Tom Overton
    Friday, October 21, 2011 3:43 PM
  • After updating Model from Database the problem persists. The collation I chose is Accent Insensitive but as far as I kwow this is just for sorting, am I wrong? Is there a way to see in the debugger how the Cliente () object was actually added to context?
    Friday, October 21, 2011 4:33 PM
  • Hi jzapata,

    You can use SQL Profiler to watch if the import parameters are same with the Cliente() objects, I think you can try to insert the entity directly by ExcuteCommand: http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorecommand.aspx

    Have a nice day.

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, October 25, 2011 6:54 AM
  • Hi Alan_chen

    I found a similar app to SQL Profiler that runs on sql server express editions http://sites.google.com/site/sqlprofiler/ with this app i'm getting the following output about the insert command:

     

    insert [dbo].[clientes]([nombres], [apellido_paterno], [apellido_materno], [fecha_nacimiento], [sexo], [rfc], [curp], [estado_civil], [direccion], [codigo_postal], [id_delegacion], [telefonos], [celular], [email], [id_empresa])
    values (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14)
    

     


    But how do I know the value of these parameters? as you can see I'm new to SQL Profiler.

    Thanks

     

     


    • Edited by jzapata Tuesday, October 25, 2011 11:23 PM
    Tuesday, October 25, 2011 11:21 PM
  • Hi jzapata,

    There should be some assignment commands before the insert statement.  Maybe you didn't copy enough of the trace information?  I haven't used that trace tool before only the one that comes with sql so i'm not sure how the trace screen is formatted.

     


    Tom Overton
    Tuesday, October 25, 2011 11:39 PM
  • Thanks Tom I found the assignment you mentioned before an I realized that execute command inserts the values without special characters as seen below:

    exec sp_executesql N'insert [dbo].[clientes]([nombres], [apellido_paterno], [apellido_materno], [fecha_nacimiento], 
    [sexo], [rfc], [curp], [estado_civil], [direccion], [codigo_postal], [id_delegacion], [telefonos], [celular], [email], [id_empresa])
    values (@0, @1, @2, @3, @4, @5, @6, @7, @8, @9, @10, @11, @12, @13, @14)
    select [id_cliente]
    from [dbo].[clientes]
    where @@ROWCOUNT > 0 and [id_cliente] = scope_identity()',N'@0 varchar(50),@1 varchar(50),@2 varchar(50),@3 datetime2(7),@4 char(1),@5 char(13),@6 char(18),@7 varchar(15),@8 varchar(500),@9 char(5),@10 int,@11 varchar(50),@12 varchar(25),@13 varchar(50),@14 int',
    @0='ae',@1='io',@2='eu',@3='1956-10-26 00:00:00',@4='H',@5='             ',@6='                  ',@7='Soltero',@8='uo',@9='9191 ',@10=2,@11='6191919',@12='',@13='',@14=1
    
    


    Actually the parameters I tried to insert were @0='áé', @1='íó', @2='éú', the characters were translated to their equivalent without accent.

    Does this tell me that the problem is present in database layer or entity framework?

    Wednesday, October 26, 2011 5:11 PM
  • Hi jzapata,

    As @Tom says, I can insert "áéíóúñ" to database with nvarchar type, I think you can try to run Command Text directly in Entity Frameowork for insert:

    http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.executestorecommand.aspx, ExcuteStoreCommand

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 27, 2011 7:57 AM
  • Thanks both of you, I changed types to nvarchar and now its working. It seems like varchar type doesn't support the collation I was using.

     

    Regards

    Jorge

    • Marked as answer by jzapata Thursday, October 27, 2011 6:43 PM
    Thursday, October 27, 2011 6:43 PM