none
Exception using LIKE query on chinese windows-7 System + mdb-database RRS feed

  • Question

  • Hi,

    On a chinese windows-7 System I have a MdB-database with some 'special' Chinese characters.

     グ   or   ド

    If I query on that database with a .... LIKE ... query (SELECT id,qtext FROM tblQueryText where qtext like '%text%' ORDER BY id) than an exception is thrown.

    Running that Programm on an english System -> works fine.

    (I'm using: Provider=Microsoft.Jet.OLEDB.4.0)

    what is the problem on a chinese window-7 System ?

    / Reinhard

    ok, here is the C# code:

    static void Start(string[] args)
    {
        if (args.Length == 0)
        {
            Console.WriteLine("start app with database-file as parameter");
            return;
        }
        string s32 = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=";
        string sDatabase = args[0];
        string sConnection = s32 + sDatabase;
    
        Console.WriteLine("Connecting: {0}", sConnection);
        OleDbConnection dbConnection = new OleDbConnection(sConnection);
        dbConnection.Open();
    
        // that works
        ReadData(dbConnection, "SELECT id,qtext FROM tblQueryText ORDER BY id");
    
        // that does not work
        ReadData(dbConnection, "SELECT id,qtext FROM tblQueryText where qtext like '%text%' ORDER BY id" );
    
    
        dbConnection.Close();
        Console.WriteLine("dbConnection closed");
    
    }
    static void ReadData(OleDbConnection dbConnection, string sql)
    {
        Console.WriteLine("SQL: {0}", sql);
        OleDbCommand cmd = new OleDbCommand(sql, dbConnection);
        OleDbDataReader reader = cmd.ExecuteReader();
        /*
         * during reading / testing for EOF there will be an exception 
         * if the field qtext contains the character:  グ  ( 0xe3 0x82 0xb0 )
         * or the character:  ド
         * and the query contains a like statement
         * */
        try
        {
            while (reader.Read())
            {
                Console.WriteLine("start read data");
                Console.WriteLine("Result:{0} / {1}", reader.GetInt32(0), reader.GetString(1));
                Console.WriteLine("finished read data");
            }
        }
        catch (System.Exception ex)
        {
            Console.WriteLine("Exception:{0}", ex.Message);
        }
        Console.WriteLine("all data readed");
    
    }

    ----

    Here you can find Programm + MdB-file to reproduce the error:

    https://onedrive.live.com/?cid=827A4C5443E130AF&id=827A4C5443E130AF%21110

    The key is, that the database has to contain the "special character".

    / Reinhard

    • Edited by Reinhard L Tuesday, June 16, 2015 6:23 AM
    Thursday, June 11, 2015 7:13 AM

All replies

  • I don't see your code, but did you try using Parameters with a Command object? Embedding variables in a SQL statement is unsafe and prone to errors when special characters are included.

            Dim AccessConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=C:\Test Files\db1 XP.mdb")
    
            AccessConnection.Open()
    
            Dim AccessCommand As New OleDbCommand("SELECT * FROM Customers WHERE CustomerID = @CustomerID", AccessConnection)
            AccessCommand.Parameters.AddWithValue("@CustomerID", "ALFKI")
            Dim AccessDataReader As OleDbDataReader
            AccessDataReader = AccessCommand.ExecuteReader()
    
            If AccessDataReader.Read Then
                Label1.Text = AccessDataReader("CustomerID")
                Label2.Text = AccessDataReader("ContactName")
                Label3.Text = AccessDataReader("CompanyName")
                '...
                '...
            End If
    
            AccessDataReader.Close()


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Thursday, June 11, 2015 11:43 AM
  • Hello Reinhard L,

    >>If I query on that database with a .... LIKE ... query (SELECT id,qtext FROM tblQueryText where qtext like '%text%' ORDER BY id) than an exception is thrown.

    If possible, please share the exception message with us and from your description, I build a Chinese chinese window 8(currently, I do not have a win7 system.) However, I could run it successfully. And as Paul mentions, please using Parameters with a Command object and here is a C# version code according to code from yours:

    static void Start(string[] args)
    
    {
    
                    …
    
        ReadData(dbConnection, "SELECT id,qtext FROM tblQueryText where qtext like @text ORDER BY id" );
    
                    …
    
    }
    
    static void ReadData(OleDbConnection dbConnection, string sql)
    
    {
    
    …
    
    OleDbCommand cmd = new OleDbCommand(sql, dbConnection);
    
    cmd.Parameters.AddWithValue("@text ", "%グ%");
    
    …
    
    }

    Please try to see if works for you.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, June 12, 2015 3:29 AM
    Moderator
  • I've tried out with the "addParameters"  call - unfortunately the same exception.

    This is the exception that is thrown on:

    OleDbDataReader reader = cmd.ExecuteReader();

    Exception on Chinese System

    To reproduce the Error, you have to have a mdb-database with such Special chinese Characters.

    Here you can find Programm + MdB-file to reproduce the error:

    https://onedrive.live.com/?cid=827A4C5443E130AF&id=827A4C5443E130AF%21110

    /Reinhard



    • Edited by Reinhard L Monday, June 15, 2015 7:30 AM
    Monday, June 15, 2015 7:14 AM
  • Hello Reinhard,

    With your provided link, I downloaded it and tested it on a Chinese and English windows, and it gave the result you show us. I checked my above test that is used with an .accdb and with ACE12 engine, so that I got a different result, it is my mistake that I did not read your post carefully.

    For this issue, I suggest you could post this scenario to the ADO.NET team: https://connect.microsoft.com/VisualStudio

    Or save your .mdb file to an .accdb and use the ACE 12 engine. 

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, June 16, 2015 8:09 AM
    Moderator
  • Hello Fred,

    I've post a Feedback in the VisualStudio area on (june/16)

    https://connect.microsoft.com/VisualStudio/feedback/details/1438741/exception-using-like-query-on-chinese-windows-7-system-mdb-database

    but unfortunately I've got no answer till now (june/22).

    Can you help me getting an answer ?

    Regards

     Reinhard

    Monday, June 22, 2015 7:02 AM
  • Hello Reinhard,

    For further helping you, i am trying to invoke someone to help lool into this case, it may take sometime and as soon as we get any result, we will post it to this thread. Currently, you could consider using the AccessDatabaseEngine

    Thanks for your understanding and support.

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, June 23, 2015 4:39 AM
    Moderator