|
|
Member
|
hmoser-bostwicklaboratories
-
10/14/2009 2:03:33 PM
Exception received when using Where condition in Search Results webpart
I have a custom document called CMS.Job with a JobStatus field for Open or Closed. When I try to use JobStatus='Open' and I receive the following exception when I attempt to use the Where condition in the Search Results webpart. I tried commenting out the searchdocuments query for CMS.Root. I think something is wrong with my Where condition. Any help would be greatly appreciated.
Exception Details: System.Exception: [DataConnection.ExecuteQuery]: Query: SELECT View_CMS_Tree_Joined.*, SearchResultName = CASE View_CMS_Tree_Joined.DocumentName WHEN '' THEN '/' else View_CMS_Tree_Joined.DocumentName END
FROM View_CMS_Tree_Joined
WHERE
(
[DocumentName] LIKE N'%' + @Expression + N'%'
OR
[NodeName] LIKE N'%' + @Expression + N'%'
OR
[DocumentURLPath] LIKE N'%' + @Expression + N'%'
OR
[DocumentPageTitle] LIKE N'%' + @Expression + N'%'
OR
[DocumentPageKeyWords] LIKE N'%' + @Expression + N'%'
OR
[DocumentPageDescription] LIKE N'%' + @Expression + N'%'
OR
[DocumentMenuCaption] LIKE N'%' + @Expression + N'%'
OR
[DocumentContent] LIKE N'%' + @Expression + N'%'
)
AND ((ClassName = N'CMS.Job') AND (((((SiteName = N'BostwickLaboratories') AND (Published = 1)) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/Company/Careers/Job-Areas/%')) AND (JobStatus='Open'))) ORDER BY 1: caused exception: Invalid column name 'JobStatus'.
|
|
|
|
Kentico Developer
|
kentico_helenag
-
10/15/2009 7:02:58 AM
RE:Exception received when using Where condition in Search Results webpart
Hello,
the problem is that you may search different document types and they do not have the requested column.
I would recommend you to develop a custom web part (http://devnet.kentico.com/docs/devguide/developing_web_parts.htm). The web part would contain a textbox, a button and a repeater. On button press it configures the Where condition of repeater.
Should you need more details please let me know.
Best regards,
Helena Grulichova
|
|
|
|
|
Member
|
hmoser-bostwicklaboratories
-
10/15/2009 7:37:24 AM
RE:Exception received when using Where condition in Search Results webpart
Thank you Helena, but what if I am only searching one document type the CMS.Job document that I created?
|
|
|
|
|
Kentico Developer
|
kentico_helenag
-
10/15/2009 8:01:37 AM
RE:Exception received when using Where condition in Search Results webpart
Hello,
SQL search uses the generic query for all document types (it causes the error) and then the document type specific query to search the document type fields.
You may configure the repeater: ClassNames="CMS.Job" in the custom web part and it will work correctly.
Best regards,
Helena Grulichova
|
|
|
|
|
Member
|
hmoser-bostwicklaboratories
-
10/15/2009 8:26:41 AM
RE:Exception received when using Where condition in Search Results webpart
Thanks again. I am trying to avoid using a custom web part and ue the Search Results web part. I tried the following http://devnet.kentico.com/FAQs/API-and-Internals.aspx "I’m not able to use document type fields in WHERE condition of search result web part." But I still haveno been able to get this to work.
I added ClassNames='CMS.Job' AND JobStatus='Open' in the where condition of the web part and I get the following message now.
[DataConnection.ExecuteQuery]: Query: SELECT *, View_CONTENT_Job_Joined.DocumentName AS SearchResultName FROM View_CONTENT_Job_Joined WHERE (((((SiteName = N'BostwickLaboratories') AND (Published = 1)) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/Company/Careers/Job-Areas/%')) AND (ClassNames='CMS.Job' AND JobStatus='Open')) AND ([JobName] Like N'%'+ @Expression + N'%' OR [JobSummary] Like N'%'+ @Expression + N'%' OR [JobDescription] Like N'%'+ @Expression + N'%' OR [JobStatus] Like N'%'+ @Expression + N'%' OR [JobDepartment] Like N'%'+ @Expression + N'%' OR [JobLocationState] Like N'%'+ @Expression + N'%' OR [JobLocationCity] Like N'%'+ @Expression + N'%' OR [JobQualifications] Like N'%'+ @Expression + N'%') ORDER BY 1: caused exception: Invalid column name 'ClassNames'.
|
|
|
|
|
Kentico Developer
|
kentico_helenag
-
10/16/2009 3:10:52 AM
RE:Exception received when using Where condition in Search Results webpart
Hello,
thank you. Personally, I do not like this approach when you comment the basic queries. For example, it will not be able to search the attachments or generic document (node) properties.
However, problem in your case is that you should not use the WHERE condition: ClassNames='CMS.Job' AND JobStatus='Open'
but only JobStatus='Open'.
Please find the property of search results called Document types and add CMS.Job here.
Best regards,
Helena Grulichova
|
|
|
|
|
Member
|
hmoser-bostwicklaboratories
-
10/19/2009 8:34:18 AM
RE:Exception received when using Where condition in Search Results webpart
Thank you for your response. CMS.Job was added to the Document types and I still get the error. I think I followed the FAQ exactly, but I am still not getting the results I should be getting.
|
|
|
|
|
Kentico Developer
|
kentico_helenag
-
10/20/2009 7:33:44 AM
RE:Exception received when using Where condition in Search Results webpart
Hello,
could you please tell me:
1. what WHERE condition you use now
2. if the error message is still the same one
3. what queries you disable exactly.
Best regards,
Helena Grulichova
|
|
|
|
|
Member
|
hmoser-bostwicklaboratories
-
10/21/2009 12:49:56 PM
RE:Exception received when using Where condition in Search Results webpart
1. JobStatus='Open' and only one Document Type selected CMS.Job
2. Different error message when document>root>queries>searchdocuments is commented out. Error message below.
3. document>root>queries>searchdocuments
Error when document>root>queries>searchdocuments is commented out
[DataConnection.ExecuteQuery]: Query: /*
Uncomment the following query after you configure Kentico CMS for full-text search as described in Developers Guide -> Installation and deployment -> Configuration of full-text search in files
*/
/*
SELECT view_cms_tree_joined.*, view_cms_tree_joined.NodeName AS SearchResultName
FROM CMS_Attachment INNER JOIN view_cms_tree_joined
ON view_cms_tree_joined.DocumentID = CMS_Attachment.AttachmentDocumentID
WHERE (((((SiteName = N'BostwickLaboratories') AND (Published = 1)) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/Company/Careers/Job-Areas/%')) AND (JobStatus='Open') AND ClassName = 'CMS.Job') AND
([AttachmentName] Like N'%'+ @Expression + N'%' OR FREETEXT(attachmentbinary, @expression))
ORDER BY 1
*/
SELECT view_cms_tree_joined.*, view_cms_tree_joined.NodeName AS SearchResultName
FROM CMS_Attachment INNER JOIN view_cms_tree_joined
ON view_cms_tree_joined.DocumentID = CMS_Attachment.AttachmentDocumentID
WHERE (((((SiteName = N'BostwickLaboratories') AND (Published = 1)) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/Company/Careers/Job-Areas/%')) AND (JobStatus='Open') AND ClassName = 'CMS.Job') AND ([AttachmentName] Like N'%'+ @Expression + N'%') ORDER BY 1: caused exception: Invalid column name 'JobStatus'.
Error when document>root>queries>searchdocuments is not commented out
[DataConnection.ExecuteQuery]: Query: SELECT View_CMS_Tree_Joined.*, SearchResultName = CASE View_CMS_Tree_Joined.DocumentName WHEN '' THEN '/' else View_CMS_Tree_Joined.DocumentName END
FROM View_CMS_Tree_Joined
WHERE
(
[DocumentName] LIKE N'%' + @Expression + N'%'
OR
[NodeName] LIKE N'%' + @Expression + N'%'
OR
[DocumentURLPath] LIKE N'%' + @Expression + N'%'
OR
[DocumentPageTitle] LIKE N'%' + @Expression + N'%'
OR
[DocumentPageKeyWords] LIKE N'%' + @Expression + N'%'
OR
[DocumentPageDescription] LIKE N'%' + @Expression + N'%'
OR
[DocumentMenuCaption] LIKE N'%' + @Expression + N'%'
OR
[DocumentContent] LIKE N'%' + @Expression + N'%'
)
AND ((ClassName = N'CMS.Job') AND (((((SiteName = N'BostwickLaboratories') AND (Published = 1)) AND (DocumentCulture = N'en-US')) AND (NodeAliasPath LIKE N'/Company/Careers/Job-Areas/%')) AND (JobStatus='Open'))) ORDER BY 1: caused exception: Invalid column name 'JobStatus'.
|
|
|
|
|
Kentico Developer
|
kentico_helenag
-
10/22/2009 5:55:12 AM
RE:Exception received when using Where condition in Search Results webpart
Hello,
thank you for helpful information.
The error message comes from searchattachments query. Except for searchdocuments query you need to comment out the mentioned searchattachments query and searchtree query.
Please let me know if it helps to avoid the error messages.
Best regards,
Helena Grulichova
|
|
|
|
|
Member
|
hmoser-bostwicklaboratories
-
10/22/2009 8:10:23 AM
RE:Exception received when using Where condition in Search Results webpart
As recommened, commenting out searchattachments and searchtree worked perfectly for me. So the finally question that I have is, because I did this my site wide search wll no search attachments correct?
Is it possible that a better/built in solution for this feature be included in a future release. I was also thinking that maybe the FAQ section where I originally got this information might need to be updated as well.
Thanks again Helena for working this out for me.
|
|
|
|
|
Kentico Developer
|
kentico_helenag
-
10/27/2009 6:29:35 AM
RE:Exception received when using Where condition in Search Results webpart
Hello, you are right it will not search the attachments in this case. Except for the mentioned custom web part you may use Smart search (the new feature of 4.1 version) instead of SQL search – please see this chapter in Developer’s Guide: http://devnet.kentico.com/docs/devguide/smart_search_overview.htmThank you for your note about FAQ, we will fix it. You are welcome:-) Best regards, Helena Grulichova
|
|