Search code examples
sqlvb.netms-accessoledb

Why does it throw OleDbException when searched unicode text in mdb? How to search unicode text?

I am using MDB (Access 2007) and OleDbConnection, in my VB.Net app. When I search non-Unicode texts it searches well. But when I search with a Unicode text, it throws an exception. I searched a lot and found few examples in StackOverflow about Unicode search, they suggest to use N before ' character as below.

OleDbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DbOnlyforBathu.mdb"

'sql = "SELECT * FROM TableNotes WHERE NotesDetail like '%" & "hello" & "%'" 'Works fine for Non-Unicode
'sql = "SELECT * FROM TableNotes WHERE NotesDetail like '%commonWord%'" 'Works fine, returns all results inclu. unicode
'sql = "SELECT * FROM TableNotes WHERE NotesDetail LIKE 'N%வா%'" 'no errors; no results
sql = "SELECT * FROM TableNotes WHERE NotesDetail like N'%" & "வா" & "%'"  'throws error for Unicode
sql = "SELECT * FROM TableNotes WHERE NotesDetail like '*வா*'" 'no errors; no results

da = New OleDb.OleDbDataAdapter(sql, OleDbConn)
da.Fill(ds, TableResult)

But when I use N before the ' character it throws this error. What does this error mean & How to search an unicode text?

System.Data.OleDb.OleDbException: 'Syntax error (missing operator) in query expression 'NotesDetail like N'%வா%''.'

Please note: "வா" is a Tamil text.

Update: I converted mdb to accdb and used the appropriate connection string("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Db.accdb;"), but still the the same issue. however, it works well with non-Unicode texts.

Solution

  • N'string' notation is for T-SQL (MS SQL Server); it is not supported by Access SQL. Current versions of Access (since Access 2000) support Unicode strings so LIKE '%வா%' should work with an OLEDB connection.

    Module Module1

    Sub Main()
        Dim con As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\Users\Public\test\unicode.mdb")
        con.Open()
        Dim cmd As New OleDb.OleDbCommand()
        cmd.Connection = con
        cmd.CommandText = "SELECT COUNT(*) AS n FROM vocabulary"
        Dim n As Integer
        n = cmd.ExecuteScalar()
        Console.WriteLine(n)  ' 12
        cmd.CommandText = "SELECT COUNT(*) AS n FROM vocabulary WHERE comments LIKE '%வா%'"
        n = cmd.ExecuteScalar()
        Console.WriteLine(n)  ' 1
    End Sub

End Module

    If it doesn't work for you then your .mdb file may be suspect. (Perhaps it was upgraded from a pre-Unicode version of Access, i.e., Access 97 or earlier.)