i trying create search function searches gridview value returns row. tested query in mysql workbench , works when query run in visual studio error:
you have error in sql syntax; check manual corresponds mysql server version right syntax use near '?parameter0' @ line 23
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;"); when debugging copied exact query being run , pasted workbench , works. don't know why won't run in visual studios.
the query gets run code:
datatable dtjobs = job.customfill(sql.tostring(), param); and parameters passed in "%testing%"
the query displays rows database:
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, (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 "; } } so search query puts having c.name "%prl%" '); after group query. append statements put prepared stmts @ end of query.
based on sql posted, problem double quotes:
having c.name "%prl%" the sql standard says double quotes used identifier names, aliases, columns, tables, schemas, procedures, views, or databases. if want string literals, need use single quotes here:
having c.name '%prl%' this 1 of many places mysql poor job of following standard (depends on how have ansi_quotes set), if want sure, try single quotes.
Comments
Post a Comment