OleDbCommand doesn't slip away
I am using related parameters, but somehow this code will not miss the [character. What's happening?
string query = "SELECT * FROM Items LEFT JOIN [Categories] ON Items.[category]=[Categories].[ID] WHERE ";
query += " supplier LIKE @supplier";
using (OleDbCommand cmd = connection.CreateCommand())
{
// create command with placeholders
cmd.CommandText = query;
// add named parameters
cmd.Parameters.AddRange(new OleDbParameter[]
{
new OleDbParameter("@supplier", "%"+supplier+"%"),
});
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet dset = new DataSet();
da.Fill(dset);
return dset;
Passing a string with the character [results in an error with the message shown below
An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
More information: Invalid template string
I tried adding a [with a backslash, but the error doesn't just go away.
source to share
As per this document, characters [
and ]
when used with the LIKE operator denote a charlist, similar to those used in regular expression.
This means that one square bracket is misinterpreted and gives an error.
From the same doc, you can read
You can use parentheses to open special characters ([), question mark (?), Number sign (#), and asterisk (*) to match directly only if they are enclosed in parentheses.
So, if you need to find a single square bracket, you need to enclose it in opening and closing square brackets.
if(supplier.IndexOf('[') >= 0)
supplier = supplier.Replace("[", "[[]");
source to share