i am trying to do a substring search from an access 2010 database in winforms.
command.Parameters.AddWithValue("@searchTerm", searchTerm);
command.CommandText = "SELECT [OA_Name] FROM [Operating_Authority_Table] WHERE [OA_Name] = [@%searchTerm%]";
I tried to do a full string search and able to do just that, but I can't get any successful searches when i change the term to be a substring.
am i implementing the wildcard character wrongly?
Dylan Smith :
Try something like this:\n\ncommand.Parameters.AddWithValue(\"@searchTerm\", searchTerm); \ncommand.CommandText = \"SELECT [OA_Name] FROM [Operating_Authority_Table] WHERE [OA_Name] = '%' + @searchTerm + '%'\";\n",
2011-11-04T05:37:20
onedaywhen :
The accepted answer:\n\n\"...WHERE [OA_Name] = '%' + @searchTerm + '%'\";\n\n\nThis will treat the % characters as text literals. \n\nI was slightly surprised because the word \"Wildcard\" in the question title suggests the intent is for pattern matching. If this is indeed the case, I would recommend replacing the = equality operator with the ALIKE operator e.g. \n\n\"...WHERE [OA_Name] ALIKE '%' + @searchTerm + '%'\";\n\n\nThe problem with the LIKE operator is that the Access database engine (ACE, Jet, whatever) uses different wildcard characters depending on so-called ANSI Query Mode. Presumably you are using ANSI-92 Query Mode by implication of using SqlOleDb (but would you have known for sure?)\n\nThe advantage of ALIKE is that is uses the same 'Standard' wildcard characters regardless of ANSI Query Mode, being % for multiple characters and _ for a single character.",
2011-11-04T08:37:11