c# - Error with quotes in search query -

i displaying report using sql query. in report user can search customer. created search function. searchfield customer (selected dropdownlist) , search terms user enters textbox search:

stringbuilder sql = new stringbuilder(searchsql); if (searchfieldkey != null && searchfieldkey.length > 0) {   if (searchterms != null)   {     sql.append(" having ");     (int = 0; < searchfieldkey.length; i++)     {       if (searchfields.containskey(searchfieldkey[i]))       {         sql.append(searchfields[searchfieldkey[i]] + " ?parameter" + i.tostring());         param.add(new mysqlparameter("parameter" + i.tostring(),           "%" + searchterms[i] + "%"));          if (i != searchfieldkey.length - 1)           sql.append(" or ");       }       else         throw new exception("error: attempted search on invalid field. check searchfields argument.");     }   } }  sql.append(" '); "); sql.append ("prepare stmt @sql; execute stmt; deallocate prepare stmt;"); 

this function adds having query end of query displaying report (searchsql). problem quotes near like. end of query returns this:

where c.company_id = ', 135, ' group c.id  having c.name "%test%" '); 

but because quote @ end of statement couldn't read parameter being passed getting error:

you have error in sql syntax; check manual corresponds mysql server version right syntax use near '?parameter0' @ line 23

so need query this:

where c.company_id = ', 135, ' group c.id having c.name like', "%test%" ); 

see quote has moved after , comma has been added. need make query work. when try in current code causes error.

you have error in sql syntax; check manual corresponds mysql server version right syntax use near '%test%' @ line 23

so need in search function?

i pasted query mysql workbench clearer see whats happening.

the problem here:

see how "%prl%" green because included in quotes wasn't reading parameter.

now see how should be:

closing quote after like, parameter can read. problem code. need change work?

here full searchsql function:

private static string searchsql {     {     return @"set group_concat_max_len=10000000;              set @sql = null;              select group_concat(distinct                concat('max(case when pt.code = ''',                               pt.code ,                            ''' jp.advisedqty else 0 end) `',                            pt.code, '`')                      ) @sql                                                                                             customer c                  left join job_address ja on c.accountcode = ja.code , c.company_id = ja.company_id                  join  addresstype jat on ja.addresstype = jat.id , jat.description = 'debtor'                  left join job_new jn on ja.jobid = jn.id                  left join job_pieces jp on ja.jobid = jp.id                  left join piecestype pt on jp.typeid = pt.id                  c.company_id = ?compid;                   set @sql = concat('select c.name, count(distinct jn.id) jobs,                    sum((select count(id) jobstat status = ''del'' , jobid = jn.id)) delivered,                   sum((select count(id) jobstat status = ''pod'' , jobid = jn.id)) pod,                   (select count(job_debriefs.id) job_debriefs jobid = jn.jobno) debriefs,                   sum(jn.outturn) outturn,                   sum(jn.actualweight) grosswt,                   sum(jn.cbm) cbm,                   jn.department,                   (select name job_address addresstype =3 , jobid = jn.id) collectname,                   (select name job_address addresstype =2 , jobid = jn.id) delivername,                   ', @sql, '                    customer c                    left join job_address ja on c.accountcode = ja.code , c.company_id = ja.company_id                    join  addresstype jat on ja.addresstype = jat.id , jat.description = ''debtor''                    left join job_new jn on ja.jobid = jn.id                    left join job_pieces jp on ja.jobid = jp.id                    left join piecestype pt on jp.typeid = pt.id                    c.company_id = ', ?compid,                     ' group c.id";   } } 

here answer problem:

 stringbuilder sql = new stringbuilder(searchsql);             if (searchfieldkey != null && searchfieldkey.length > 0)             {                 if (searchterms != null)                 {                     sql.append(" having ");                     (int = 0; < searchfieldkey.length; i++)                     {                         if (searchfields.containskey(searchfieldkey[i]))                         {                              sql.append(searchfields[searchfieldkey[i]] + " ', ?parameter" + i.tostring());                             param.add(new mysqlparameter("parameter" + i.tostring(), "\'%" + searchterms[i] + "%\'"));                              if (i != searchfieldkey.length - 1)                                 sql.append("', or ");                          }                         else                             throw new exception("error: attempted search on invalid field. check searchfields argument.");                     }                 }              }             else             {                 sql.append("'");             }              sql.append("); ");             sql.append ("prepare stmt @sql; execute stmt; deallocate prepare stmt;"); 

i missing space after , added else close quote