Error in SQL query while searching
I have a SQL query that displays information from different tables in a database. This query is then displayed in the DataGrid and I have some options in the DropDownList to search the DataGrid for specific values. The problem is the search is not displaying the correct information for CollectName or DeliverName. Code for 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 internal selection operations and that the cause of this problem is that the customer and department lookup is working fine. SQL query:
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 a value selected from the DropDownList and a value entered into a 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'm looking for a customer, the SQL query returns this string to the end:
WHERE c.Company_ID = ?compid AND (c.Name LIKE ?parameter0)
And when I search for CollectName or DeliverName, the request is as follows:
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 is causing CollectName and DeliverName not to work?
source to share
The parenthesis does not match, it must be
WHERE c.Company_ID = ?compid
AND (SELECT Name FROM job_address WHERE AddressType = 3 AND JobID = jn.ID) LIKE ?parameter0
To solve this problem, you can include the instruction in your dictionary:
{ "CollectName", "(SELECT Name FROM job_address WHERE AddressType = 3 AND JobID = jn.ID)" },
Or in your method that builds SQL, automatically insert the subquery:
SQL.Append("(" + SearchFields[SearchFieldKey[i]] + ") LIKE ?parameter" + i.ToString());
Full correction: you shouldn't try to concatenate the string together if you are using a 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(") ");
}
source to share