在中文windows-7 System + mdb-database上使用LIKE查询的异常 [英] Exception using LIKE query on chinese windows-7 System + mdb-database

问题描述

在中文windows-7系统上，我有一个带有一些"特殊"中文字符的MdB数据库。

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

 グ  或   ド

 グ   or   ド

如果我使用.... LIKE ...查询查询该数据库（SELECT id，qtext FROM tblQueryText，其中qtext如'％text％'ORDER BY id）抛出异常。

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.

（我正在使用：Provider = Microsoft.Jet.OLEDB.4.0）

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

中文窗口7系统有什么问题？

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

/ Reinhard

/ Reinhard

好的，这是C＃代码：

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");

}

----

在这里你可以找到Programm + MdB文件来重现错误：

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

/ Reinhard

推荐答案

我没有看到您的代码，但您是否尝试将Command与Command对象一起使用？在SQL语句中嵌入变量是不安全的，并且在包含特殊字符时容易出错。

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

这篇关于在中文windows-7 System + mdb-database上使用LIKE查询的异常的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！