What is wrong with this OleDBCommand?

Question

In .NET 4.5 I am trying to execute an OleDBCommand on an access database.

The code I have is

Dim rows As Integer
Dim dbCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand
Dim dbTransaction As OleDb.OleDbTransaction

Call gOleCn.Open()
dbTransaction = gOleCn.BeginTransaction
dbCommand = New OleDb.OleDbCommand("", gOleCn, dbTransaction)
dbCommand.CommandText = "UPDATE Tレイヤ管理 SET レイヤー=0 WHERE 名前 LIKE '水田_*';"
rows = dbCommand.ExecuteNonQuery()

The query executes fine in access itself, but in .NET it never affects any rows. It works if I change the CommandText to "UPDATE Tレイヤ管理 SET レイヤー=0 WHERE 名前='水田_" & Parameter & "'" and specify the name exactly using Parameter, but then I'd have to write a loop, whilst with LIKE it's all done in one line.

But how can this be made to work?

Answer 1

See the accepted answer to this SO question: Microsoft Jet wildcards: asterisk or percentage sign?

Since you're using OleDb, you are using ANSI-92 query mode, so must use % instead of * as the wild card character. The reason your UPDATE worked within an Access session is that it uses ANSI-89 query mode by default.

"UPDATE Tレイヤ管理 SET レイヤー=0 WHERE 名前 LIKE '水田_%';"

If you intended the _ (underscore) character to match the literal character (instead of the ANSI-92 wild card for any single character), make it a single-member character class in the pattern:

"UPDATE Tレイヤ管理 SET レイヤー=0 WHERE 名前 LIKE '水田[_]%';"

I'm not sure what happens with those unicode table and field names. From your question it sounds like they may not be causing problems. However, if nothing else works, I would try enclosing them in square brackets:

"UPDATE [Tレイヤ管理] SET [レイヤー]=0 WHERE [名前] LIKE '水田[_]%';"