none
Querying a field that contains CR/LF returns no results RRS feed

  • Question

  • Using SqlCeCommand without parameter binding, I'm running a query against a field that contains a carriage return and line feed.  On the .NET side of things, the query I'm executing is passing "\r\n".  I get no results back.  Shouldn't this work?

    Thank you,


    mike
    Friday, May 13, 2011 7:14 PM

Answers

  • Hi mike,

    This issue is due to that "\r\n" characters in your application, which will be become CR/CF eventually submitted to SQL Server Compact Database. To resolve this issue, you can use \\r\\n to convert it to general characters.

    Here is a sample code:

     

    -- Create a test table in SyncCompactDB compact database
    CREATE TABLE MyTbl2
    (
    	Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    	[DESC] NVARCHAR(20)
    );
    
    INSERT INTO MyTbl2([DESC]) VALUES ('ABC_\r\n_DEF');
    INSERT INTO MyTbl2([DESC]) VALUES ('ABsC2_\r\n_DEsadfF');
    INSERT INTO MyTbl2([DESC]) VALUES ('ABC3_4\r\n_DEdF');
    
    SELECT * FROM MyTbl2
    

     

     

    // A C# Console test application for this
    
    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    
    using System.Data;
    using System.Data.SqlServerCe;
    
    namespace SQLCE
    {
     class Program
     {
      static void Main(string[] args)
      {
       // Create a connection to the SyncCompactDB 
       SqlCeConnection sqlCeConn = new SqlCeConnection(@"Data Source = 'D:\New_Workspace\VS_Projects\VS2010\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'");
    
       string strSql = "SELECT * FROM MyTbl2 WHERE [Desc] like '%\\r\\n%'";
    
       SqlCeCommand sqlCeCmd = new SqlCeCommand(strSql, sqlCeConn);
    
       sqlCeConn.Open();
       SqlCeResultSet rs = sqlCeCmd.ExecuteResultSet(ResultSetOptions.Scrollable);
    
       while (rs.Read())
       {
        Console.WriteLine("ID: {0}, [Desc]: {1} ", rs.GetInt32(0), rs.GetString(1).ToString());
       }
    
       Console.Read();
      }
     }
    }
    
    

     

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Monday, May 16, 2011 7:57 AM
    Moderator