SQLParameter incorrect syntax

Question

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?

I see you do `this.mainConnection`, are you only opening one connection and reusing the connection throughout your code? `SqlConnection` was not designed to be used this way, it uses [Connection Pooling](http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx) and what should be done is just open and dispose many short lived connections. When you dispose a connection it leaves the connection open to the database for a little while, if it sees a new connection open using the same connection string it will re-use the old connection. — Scott Chamberlain, Dec 10 '14 at 19:52

score 4 · Accepted Answer · answered Dec 10 '14 at 19:37

4

your LIKE statements must be inside single quotes

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

answered Dec 10 '14 at 19:37

Joonas Koski

269
1
5

1

While true they also need to add the `%` to the value that they pass to the parameter for this to work. That or concatenate them in the query. – juharr Dec 10 '14 at 19:43

Scott Chamberlain · Answer 2 · 2014-12-10T19:48:29.630

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,

The C# code for SqlParameter does not care if you put a @ in the front or not when adding it to the Parameters collection, it will put a @ behind the scenes for you.

For your query that is not working the correct way to do it is you will actually have 3 strings you add together in sql, the two '%' and your parameter. I am also changing the way you add parameters to explicitly set the data type, it is better to do that with strings.

  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("@compareStringA", SqlDbType.VarChar, 20).Value = "ABRAUTH"; //I had to guess on your datatype, I just did varchar(20), change as appropriate.
  getAuthCommand.Parameters.Add("@compareStringB", SqlDbType.VarChar, 20).Value = "ABRSAUTH";

score 1 · Answer 3 · answered Dec 10 '14 at 19:37

Try this:

 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%"));

SQLParameter incorrect syntax

3 Answers3