我有一个 SQL 查询,可以显示数据库中不同表的信息。然后,这个查询将在 DataGrid 中显示,并且我有一些选项在 DropDownList 中,可以搜索 DataGrid 中特定的值。问题是搜索功能不能正确地显示 CollectName 或 DeliverName 的信息。
DropDownList 的代码如下:
我有一个搜索功能,它需要从下拉列表中选择的值和文本框中输入的值: ```html
例如,当我搜索客户时,SQL查询会将以下行添加到查询语句的末尾:
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)" }
};
在这个SQL查询中,CollectName和DeliverName是内部选择语句,这就是导致问题的原因,因为对于Customer和Department的搜索工作正常。
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
我有一个搜索功能,它需要从下拉列表中选择的值和文本框中输入的值: ```html
我有一个搜索功能,它需要从下拉列表中选择的值和文本框中输入的值:
``` 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(") ");
}
例如,当我搜索客户时,SQL查询会将以下行添加到查询语句的末尾:
WHERE c.Company_ID = ?compid AND (c.Name LIKE ?parameter0)
当我搜索CollectName或DeliverName时,查询语句如下:
WHERE c.Company_ID = ?compid AND (SELECT Name FROM job_address WHERE AddressType = 3 AND JobID = jn.ID LIKE ?parameter0)
这个SQL查询是否存在问题,导致CollectName和DeliverName无法正常工作?
WHERE (Select stuff from table where something = 1 LIKE params)
不是一个有效的查询。 - Cyril Gandon