c# sql search with LIKE

0
votes

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

2
c#sqlsql-like
why are you using brackets [] in your like? Try just doing like '%" + Artist + "%' – austin wernli
Also, it doesn't look like you are using parameterized queries. – wahwahwah

2 Answers

1
votes

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 .

2
votes

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.