搜索时SQL查询错误

I have an SQL query that displays information from different tables in the database. This query is then displayed in a DataGrid and I have some options in a DropDownList to search through the DataGrid for certain values. The problem is the search doesn't display the correct information for CollectName or DeliverName. Code for DropDownList:

我有一个SQL查询，它显示来自数据库中不同表的信息。然后在DataGrid中显示这个查询，我在下拉列表中有一些选项可以在DataGrid中搜索特定的值。问题是搜索没有显示CollectName或DeliverName的正确信息。DropDownList代码:

 private static readonly Dictionary<string, string> SearchFields = new Dictionary<string, string> {
            { "Customer", "c.Name" },
            { "Department", "jn.Department" },
            { "CollectName", "SELECT Name FROM job_address WHERE AddressType = 3 AND JobID = jn.ID" },
            { "DeliverName", "(SELECT Name FROM job_address WHERE AddressType = 2 AND JobID = jn.ID)" }
        };

In the SQL query CollectName and DeliverName are inner select statements and that's whats causing the problem here because the search for Customer and Department work fine. The SQL query:

在SQL查询中，CollectName和DeliverName是内部select语句，这就是这里出现问题的原因，因为对客户和部门的搜索工作良好。SQL查询:

SELECT  c.Name,
        COUNT(distinct jn.ID) as Jobs,
        sum(jn.OutTurn) as Outturn,
        SUM(jn.ActualWeight) as GrossWt,
        SUM(jn.CBM) as CBM,
        jn.Department,
        (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       
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
WHERE c.Company_ID = ?compid
GROUP BY c.ID

I have a search function that takes the value selected from the DropDownList and the value entered in the textbox:

我有一个搜索函数，它从下拉列表中选择的值和在文本框中输入的值:

 List<MySqlParameter> param = new List<MySqlParameter>{ new MySqlParameter("compid", CompanyID) };
            StringBuilder SQL = new StringBuilder(SearchSQL);
            if (SearchFieldKey != null && SearchFieldKey.Length > 0)
            {
                SQL.Append(" AND (");
                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(") ");
            }

So for example I search for a customer, the SQL query get this line added to end:

例如，我搜索一个客户，SQL查询将这一行添加到末尾:

WHERE c.Company_ID = ?compid AND (c.Name LIKE ?parameter0)

And when I search for CollectName or DeliverName the query is this:

当我搜索CollectName或DeliverName时，查询是这样的:

WHERE c.Company_ID = ?compid AND (SELECT Name FROM job_address WHERE AddressType = 3 AND JobID = jn.ID LIKE ?parameter0)

Is there a problem with this SQL query that causes CollectName and DeliverName not to work?

这个SQL查询有问题导致CollectName和DeliverName不工作吗?

1 个解决方案

#1

The parenthesis doesn't match, it should be

括号不匹配，应该是

WHERE c.Company_ID = ?compid 
AND (SELECT Name FROM job_address WHERE AddressType = 3 AND JobID = jn.ID) LIKE ?parameter0

To solve this, you can in your dictionary embed the statement:

要解决这个问题，你可以在字典中嵌入以下语句:

{ "CollectName", "(SELECT Name FROM job_address WHERE AddressType = 3 AND JobID = jn.ID)" },

Or in your method that build the SQL, embed automatically the subquery:

或者在构建SQL的方法中，自动嵌入子查询:

SQL.Append("(" + SearchFields[SearchFieldKey[i]] + ") LIKE ?parameter" + i.ToString());

Full correction : you should not try to concatenate string together if you are using a StringBuilder:

完全修正:如果您使用的是StringBuilder，则不应该尝试将字符串连接在一起:

var param = new List<MySqlParameter> { new MySqlParameter("compid", CompanyID) };
StringBuilder SQL = new StringBuilder(SearchSQL);
if (SearchFieldKey != null && SearchFieldKey.Length > 0)
{
    SQL.Append(" AND (");
    for (int i = 0; i < SearchFieldKey.Length; i++)
    {
        if (SearchFields.ContainsKey(SearchFieldKey[i]))
        {
            SQL.Append("(");
            SQL.Append(SearchFields[SearchFieldKey[i]]);
            SQL.Append(") LIKE ?parameter");
            SQL.Append(i);
            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(") ");
}

#1