SQL查询错误

本文关键字：错误 查询 SQL | 更新日期: 2023-09-27 18:02:22

我试图创建一个搜索函数，搜索GridView的某个值，然后返回该行。我在MySQL工作台测试了这个查询，它工作，但当查询在visual studio中运行时，我得到这个错误:

你的SQL语法有错误;查看手册对应于MySQL服务器版本，以便使用正确的语法附近的吗?Parameter0 ' at line 23

      StringBuilder SQL = new StringBuilder(SearchSQL);
                if (SearchFieldKey != null && SearchFieldKey.Length > 0)
                {
                    if (SearchTerms != null)
                    {
                        SQL.Append(" HAVING ");
                        for (int i = 0; i < SearchFieldKey.Length; i++)
                        {
                            if (SearchFields.ContainsKey(SearchFieldKey[i]))
                            {
                                SQL.Append(SearchFields[SearchFieldKey[i]] + " LIKE ?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 to search on invalid field. Check SearchFields Argument.");
                        }
                    }
                }
 SQL.Append(" '); ");
 SQL.Append ("prepare stmt from @sql; execute stmt; deallocate prepare stmt;");

调试时，我复制了正在运行的确切查询并将其粘贴到Workbench中，它可以工作。所以我不知道为什么它不能在视觉工作室里运行。

查询从以下代码运行:

 DataTable dtJobs = Job.CustomFill(SQL.ToString(), param);

传入的参数是"%TESTING%"

显示数据库中的行:

private static string SearchSQL
    {
        get
        {
            return @" 
                                                            SET group_concat_max_len=10000000;
                                                            set @sql = null;
                                                                select
                                                                  group_concat(distinct
                                                                    concat(
                                                                       'MAX(CASE WHEN pt.Code = ''', 
                                                                        pt.Code ,
                                                                        ''' THEN jp.AdvisedQty ELSE 0 END) AS `',
                                                                        pt.Code, '`'
                                                                    )
                                                                  ) into @sql
                                                                 FROM customer c
                                                        LEFT JOIN job_address ja ON c.AccountCode = ja.Code AND c.Company_ID = ja.Company_ID
                                                        JOIN  AddressType jat ON ja.AddressType = jat.ID and 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
                                                    WHERE c.Company_ID = ?compid;
                                        set @sql = concat('select 
                                                            c.Name,
                                                            COUNT(distinct jn.ID) as Jobs,
                                                            (SELECT Name FROM job_address WHERE AddressType =3 AND JobID = jn.ID) as CollectName,
                                                            (SELECT Name FROM job_address WHERE AddressType =2 AND JobID = jn.ID) as DeliverName,
                                                            ', @sql, ' 
                                                        FROM customer c
                                                        LEFT JOIN job_address ja ON c.AccountCode = ja.Code AND c.Company_ID = ja.Company_ID
                                                        JOIN  AddressType jat ON ja.AddressType = jat.ID and 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
                                                   WHERE c.Company_ID = ', ?compid,
                                                    ' GROUP BY c.ID

                                                                            ";
        }
    }

因此，我的搜索查询将HAVING c.Name LIKE "%PRL%" ');放在查询中的GROUP BY之后。然后，Append语句将准备好的stmts放在查询的末尾。

SQL查询错误

根据您发布的SQL，问题是双引号:

HAVING c.Name LIKE "%PRL%"

SQL标准规定双引号用于标识符名称，如别名、列、表、模式、过程、视图或数据库。如果需要字符串字面值，则需要在这里使用单引号:

HAVING c.Name LIKE '%PRL%'

这是MySql在遵循标准方面做得很差的地方之一(取决于您如何设置ANSI_QUOTES)，但如果您想要确定，请尝试使用单引号。

相关文章：