CODEWITHSUNDEEP
c#sqlsqlparameter
Giardino
Giardino

Reputation: 1387

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?

Upvotes: 0

Views: 634

Answers (3)

Scott Chamberlain
Scott Chamberlain

Reputation: 127543

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

Upvotes: 2

Jose M.
Jose M.

Reputation: 1316

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

Upvotes: 1

Joonas Koski
Joonas Koski

Reputation: 269

your LIKE statements must be inside single quotes 

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

Upvotes: 4

Related Questions