0

I have a database of gigs that I'm searching by Artist and trying to use LIKE in the sql query but failing.

I have tried:

string strSQL = "SELECT * FROM TB_CA_gigs WHERE Artist LIKE '[%" + Artist + "%]'";

which brings back no records

string strSQL = "SELECT * FROM TB_CA_gigs WHERE Artist LIKE '%[" + Artist + "]%'";

which brings back all records

string strSQL = "SELECT * FROM TB_CA_gigs WHERE Artist LIKE '" + Artist + "'";

which brings back no records

I have tried using CONTAINS in various ways but always get the same error:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'CONTAINS'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at WebService1.Service1.Search(String Artist) in C:\Users\strscac\Desktop\VS\WebService1\WebService1\WebService1\Service1.asmx.cs:line 36

crthompson
  • 15,653
  • 6
  • 58
  • 80
user3606041
  • 47
  • 1
  • 8

2 Answers2

2

Try something like this:

string strSQL = string.Format("SELECT * FROM TB_CA_gigs WHERE Artist LIKE '%{0}%'", Artist);

Though you should always parameterize your values (e.g. Artist should be a param) to protect from SQL injection attacks.

Dean
  • 2,084
  • 17
  • 23
  • ummm... that's not what it means to create a parameterized query. What if `Artist` was = `DROP TABLE Artist`? Your example is no different than the one the OP uses... [see here](http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i) – wahwahwah Dec 11 '14 at 23:02
  • 1
    Yes... and that is why I said "though you should" not "this is an example of". If the OP had asked how to create parameterized queries I would have pointed them in the right direction. As it is, I was including it as a note on a best practice (well, more like a minimum accepted practice). – Dean Dec 11 '14 at 23:03
  • 1
    That was not at all clear (and really not terribly clear now) from your pre-edit posts... but the condescension is definitely of value :) – wahwahwah Dec 11 '14 at 23:05
1

You missed one combination that will work:

string strSQL = "SELECT * FROM TB_CA_gigs WHERE Artist LIKE '%" + Artist + "%'";

And as wahwahwah said, as I'm sure others will too: as a good practice, learn and use parameterized queries to avoid SQL injection vulnerabilities.

For a simple example of a parameterized query, check out the MSDN documentation for System.Data.SqlClient.SqlCommand.Parameters .

Cᴏʀʏ
  • 105,112
  • 20
  • 162
  • 194
  • that worked thanks, just to answer your question - I was using the brackets as I saw it on here: http://stackoverflow.com/questions/6231744/how-to-search-for-in-sql-table-column – user3606041 Dec 11 '14 at 23:14
  • 1
    @user3606041 In that example, the `[]` are used as "escape characters" to signify that the query is looking for result containing `%` - such as if you had `5%` as a value in your database. – wahwahwah Dec 11 '14 at 23:17
  • @user3606041 please also read the link Cory posted. You should never use the statement here in production. – wahwahwah Dec 11 '14 at 23:20
  • @user3606041 The correct parameterized version of your query would be `"SELECT * FROM TB_CA_gigs WHERE Artist LIKE '%' + @Artist + '%'"` and then you would add a parameter called `@Artist` – Scott Chamberlain Dec 11 '14 at 23:30