VB.NET to ACCDB using OLEDB fails when WHERE includes an asterisk-CodePudding

I'm converting an old VB6 program to VB.NET. I've converted my Access .MDB file to .ACCDB, and I found some code on the net to give my VB.NET version access to the database. Here's the preliminary code: (I know... there are plenty of things that could be made better like TRY/CATCH, but this is preliminary.)

Dim cnnOLEDB As New OleDbConnection
Dim cmdOLEDB As New OleDbCommand

Dim strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\Pim.accdb;Persist Security Info=False;"

cnnOLEDB.ConnectionString = strConnectionString
cnnOLEDB.Open()
cmdOLEDB.CommandText = "SELECT FullName FROM NameAddr_TBL WHERE Sort_key='adams j'"
cmdOLEDB.Connection = cnnOLEDB
Dim rdrOLEDB As OleDbDataReader = cmdOLEDB.ExecuteReader
If rdrOLEDB.Read = True Then
    Dim ss$ = rdrOLEDB.Item(0).ToString
    rdrOLEDB.Close()
End If
cnnOLEDB.Close()

When I step through the code using Sort_Key='adams j', rdrOLEDB.Read is true and all is well. If I change it to Sort_Key='adams *', rdrOLEDB.Read is false. I've tried all kinds of variations, but there's something about that asterisk that it doesn't like. Any ideas?

CodePudding user response：

If your database has a row with Sort_Key='adams *' (identical character by character), then it would return that row. If not, and you expected to match records where the name started with 'adams' and the last name started with anything (ie you wanted to use a wildcard search), you should have used WHERE Sort_Key LIKE 'adams *', not =.

CodePudding user response：

When you use ado VBA, or in fact .net ado.net, and the oleDB provider?

You are to use the % sign as the wild card.

So, two issues:

First, you can't in SQL use the wild card with "=", you have to use like.

2nd, you use % as the wild card, and not *.

So, the sql should look like this:

cmdOLEDB.CommandText = 
    "SELECT FullName FROM NameAddr_TBL WHERE Sort_key LIKE 'adams%' "

As noted, this applie to Access and VBA code when using ADO, and of course when using .net and oleDB. This syntax of % is the ANSI sql standard anyway, and this even applies when using the Access data engine.