Search code examples
c#sqlsqlparameter

SQLParameter incorrect syntax


I have 2 spots where I need to use SQLParameter to parse SQL. One works and one does not and I cannot figure out why the second one doesn't work.

The first one that works is the following:

   SqlCommand getShopDbNameCommand = new SqlCommand("SELECT TOP 1 [mappeddbName] FROM [ECM].[dbo].[EcmShop]" +
        "WHERE [LicencePassCode] = @licCode AND [iPadLocation] = @shopId", this.mainConnection);
    getShopDbNameCommand.Parameters.Add(new SqlParameter("licCode", currUser.LicCode));
    getShopDbNameCommand.Parameters.Add(new SqlParameter("shopId", currUser.ShopID));

That works. On top of that, the majority of the tutorials I've read all say that I do not have to have a @ in front of the parameter name inside of the new SqlParameter, only inside of the command text itself do I need a @ in front of the parameter name.

The second command I am trying to run is the following:

  string getAuthCommandText = "SELECT * FROM [" + shopDbName + "].[dbo].[MessageLink]" +
  "WHERE [objectname] LIKE %" + "@compareStringA"+ "% OR [objectname] LIKE %" + "@compareStringB" +"%";
  SqlCommand getAuthCommand = new SqlCommand(getAuthCommandText, this.mainConnection);
  getAuthCommand.Parameters.Add(new SqlParameter("compareStringA", "ABRAUTH"));
  getAuthCommand.Parameters.Add(new SqlParameter("compareStringB", "ABRSAUTH"));

This does not work and throws an invalid syntax error. Using breakpoints it still looks like the command is trying to pass the literal @compareString string to SQL and thats whats causing the issue. Ive seen other posts on SOF that say to use the literal parameter name when defining new SqlParameter objects (meaning include the @) but everywhere outside of SoF say otherwise.

Any reason why the second command would throw invalid syntax errors?


Solution

  • your LIKE statements must be inside single quotes

    SELECT * FROM Customers WHERE City LIKE '%s%';