locked
Select query using LIKE and % operator RRS feed

  • Question

  • User-1652394277 posted

    Hi folks

    My select query works without the % operator. When I try using the LIKE '%' @AccountID '%' it always fails with Data Type mismatch.

    Here is the code snippet.

     

             if (txtBoxAccountID.Text != "")
             {
                this.AccessDataSource2.SelectCommand = "SELECT AccountID, MemberLastName, MemberMiddleName, MemberFirstName, AccountRenewalYear,  FROM Accounts  WHERE (AccountID LIKE % @AccountID %) ORDER BY AccountID;";
                this.AccessDataSource2.SelectParameters.Clear();

                ControlParameter param = new ControlParameter();
                param.Name = "@AccountID";
                param.Type = TypeCode.Int32;
                param.ControlID = "txtBoxAccountID";
                param.PropertyName = "Text";

                param.DefaultValue = "0";

                this.AccessDataSource2.SelectParameters.Add(param);

             }

     Any suggestions ?

     

    Thanks

     

    ZK

    Thursday, October 21, 2010 3:52 PM

Answers

  • User-1199946673 posted

    But I still think it is a good idea to use placeholder instead of parameter name to access the Access DB:

    ...WHERE (AccountID LIKE '%'+ ? + '%') 

     

    Why? Although it isn't documented, named parameters are supported in both Jet (and Access). I always use named parameter, because in more complex queries it can happen that a parameter is used more than once. When using placeholders, you need to specify the same parameter multiple times, which doesn't make much sence. An example is when using optional search criteria:

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 21, 2010 5:31 PM

All replies

  • User77042963 posted

    this.AccessDataSource2.SelectCommand = "SELECT AccountID, MemberLastName, MemberMiddleName, MemberFirstName, AccountRenewalYear,  FROM Accounts  WHERE (AccountID LIKE '*'+ ?+ '*') ORDER BY AccountID;";
     

    Thursday, October 21, 2010 4:33 PM
  • User-1199946673 posted

    this.AccessDataSource2.SelectCommand = "SELECT AccountID, MemberLastName, MemberMiddleName, MemberFirstName, AccountRenewalYear,  FROM Accounts  WHERE (AccountID LIKE '*'+ ?+ '*') ORDER BY AccountID;";
     

     

    Limno, When using a mdb file in a web environment, you're not dealing with an access database, but with a Jet Database Engine. In Jet, the wildcard parameter is %, just like in SQL server, the wildcard character in Access is *.

    this.AccessDataSource2.SelectCommand = "SELECT AccountID, MemberLastName, MemberMiddleName, MemberFirstName, AccountRenewalYear,  FROM Accounts  WHERE (AccountID LIKE '%'+ @AccountID + '%') ORDER BY AccountID;";

     

    Thursday, October 21, 2010 4:52 PM
  • User77042963 posted

    You are right on this wildcard. Thanks.

    But I still think it is a good idea to use placeholder instead of parameter name to access the Access DB:

    ...WHERE (AccountID LIKE '%'+ ? + '%') 

    Thursday, October 21, 2010 5:19 PM
  • User-1199946673 posted

    But I still think it is a good idea to use placeholder instead of parameter name to access the Access DB:

    ...WHERE (AccountID LIKE '%'+ ? + '%') 

     

    Why? Although it isn't documented, named parameters are supported in both Jet (and Access). I always use named parameter, because in more complex queries it can happen that a parameter is used more than once. When using placeholders, you need to specify the same parameter multiple times, which doesn't make much sence. An example is when using optional search criteria:

    http://www.mikesdotnetting.com/Article/68/An-ASP.NET-Search-Engine-with-MS-Access-for-optional-search-criteria

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, October 21, 2010 5:31 PM