##WHERE## macro using NodeGUID causing error

Alistair DeJonge asked on February 24, 2017 18:46

Here is my SQL:

SELECT ##TOPN## c.ShowPromoContent, c.Promo_1, c.Promo_2, c.Promo_3, p.TitleTextLight,p.TitleTextBold,p.Description,p.Image,p.LinkURL,p.LinkText
FROM custom_Collections_Detail_Page c, custom_Promo p
WHERE ##WHERE##

Here is my Where condition on the web part:

{% Documents.Where("NodeGUID IN (" + CurrentDocument.GetValue("Promo_1") + "," + CurrentDocument.GetValue("Promo_2") + "," + CurrentDocument.GetValue("Promo_3") + ")")#%}

Here is the error in the Event Log:

Error while evaluating expression: Documents.Where("NodeGUID IN (" + CurrentDocument.GetValue("Promo_1") + "," + CurrentDocument.GetValue("Promo_2") + "," + CurrentDocument.GetValue("Promo_3") + ")")|(user)alistair|(hash)09a08b1f1957865214c885108909f79d5d567d5abfe58a63e941eeabddd009df

CMS.MacroEngine.EvaluationException: Exception occured while evaluation of the expression ' Documents.Where("NodeGUID IN (" + CurrentDocument.GetValue("Promo_1") + "," + CurrentDocument.GetValue("Promo_2") + "," + CurrentDocument.GetValue("Promo_3") + ")")|(user)alistair|(hash)09a08b1f1957865214c885108909f79d5d567d5abfe58a63e941eeabddd009df': An invalid WHERE condition was used.
at CMS.MacroEngine.ExpressionEvaluator.ExecuteMethod(IMacroMethod method, IEnumerable`1 parameters)
at CMS.MacroEngine.ExpressionEvaluator.EvaluateMethodCall(Boolean& match, Boolean& securityPassed)
at CMS.MacroEngine.ExpressionEvaluator.EvaluateInternal(Boolean& match, Boolean& securityPassed)
at CMS.MacroEngine.ExpressionEvaluator.Evaluate()
at CMS.MacroEngine.MacroResolver.ResolveMacroExpression(ResolveExpressionSettings settings)

Please help! Is it the quotes surrounding the Promo_1, etc.?

Correct Answer

Anton Grekhovodov answered on February 27, 2017 05:36

Try to use views instead of table names, because views have NodeGUID column (and all other columns for pages). So your query will look like:

SELECT ##TOPN## 
    c.ShowPromoContent, 
    c.Promo_1, 
    c.Promo_2, 
    c.Promo_3, 
    p.TitleTextLight,
    p.TitleTextBold,
    p.Description,
    p.Image,
    p.LinkURL,
    p.LinkText
--view name format is Views_(table name)_Joined and you can find it in DB
FROM View_custom_Collections_Detail_Page_Joined c, View_ustom_Promo_Joined p
WHERE ##WHERE##

And use where conditions which were mentioned by Peter.

0 votesVote for this answer Unmark Correct answer

Recent Answers

Brenden Kehren answered on February 24, 2017 18:58 (last edited on December 10, 2019 02:30)

Think the issue is `##WHERE## is actually rendering the "Documents.Where()" string. You don't need that. You need to have something like:

{%"NodeGUID IN (" + CurrentDocument.GetValue("Promo_1") + "," + CurrentDocument.GetValue("Promo_2") + "," + CurrentDocument.GetValue("Promo_3") + ")"|(identity)GlobalAdministrator%})

1 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on February 24, 2017 19:16

Tried both of those and I get the yellow screen of death:

Message: An invalid SQL query was used.

Exception type: System.Security.SecurityException
Stack Trace:
at CMS.PortalControls.CMSAbstractWebPart.GetValueInternal(String propertyName, Boolean resolveMacros)
at CMSWebParts_Viewers_Query_queryrepeater.get_WhereCondition() in f:\hudson-service\jobs\kentico_deploy_test\workspace\CMS\CMSWebParts\Viewers\Query\queryrepeater.ascx.cs:line 106
at CMSWebParts_Viewers_Query_queryrepeater.SetupControl() in f:\hudson-service\jobs\kentico_deploy_test\workspace\CMS\CMSWebParts\Viewers\Query\queryrepeater.ascx.cs:line 689
at CMSWebParts_Viewers_Query_queryrepeater.OnContentLoaded() in f:\hudson-service\jobs\kentico_deploy_test\workspace\CMS\CMSWebParts\Viewers\Query\queryrepeater.ascx.cs:line 660
at CMS.PortalControls.CMSWebPartZone.OnContentLoaded()
at CMS.PortalControls.CMSAbstractLayout.OnContentLoaded()
at CMS.PortalControls.CMSAbstractLayout.LoadWebPartsContent(Boolean reloadData)
at CMS.PortalControls.CMSAbstractLayout.LoadContent(PageInfo pageInfo, Boolean reloadData, Boolean allowOrphanedZones)
at CMS.PortalControls.CMSPagePlaceholder.LoadContent(PageInfo pageInfo, Boolean reloadData)
at CMS.PortalControls.CMSPagePlaceholder.LoadContent(PageInfo pageInfo, Boolean reloadData)
at CMS.PortalControls.CMSPortalManager.LoadPage(Boolean reloadData)
at CMS.PortalControls.CMSPortalManager.PageHelper_InitComplete(Object sender, EventArgs e)
at CMS.Helpers.PageContext.CurrentPage_InitComplete(Object sender, EventArgs e)
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Web.UI.Page.OnInitComplete(EventArgs e)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
at System.Web.UI.Page.ProcessRequest()
at System.Web.UI.Page.ProcessRequest(HttpContext context)
at System.Web.HttpApplication.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute()
at System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously)
0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on February 24, 2017 19:32 (last edited on February 24, 2017 20:10)

GUIDs are strings, you are missing quotes:

{% NodeGUID IN ('" + 
CurrentDocument.GetValue("Promo_1") + "','" + 
CurrentDocument.GetValue("Promo_2") + "','" + 
CurrentDocument.GetValue("Promo_3") + "')")|(handlesqlinjection)false@%}
0 votesVote for this answer Mark as a Correct answer

Jan Hermann answered on February 24, 2017 20:50

Move NodeGUID IN ( and ) outside the macro!

0 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on February 24, 2017 21:02

So I've tried all that and it now gives me an invalid column name error. Is there a JOIN I should be using, and ON what?

I'm using a Repeater with custom query to return the data from another page type, based on the flag "ShowPromoContent" and the 3 page selectors on the current page type.

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on February 24, 2017 21:40 (last edited on December 10, 2019 02:30)

Assuming you have

Image Text

I just tried it - it works

NodeGUID IN (
'{%CurrentDocument.GetValue("Promo_1")|(identity)GlobalAdministrator%}'
)

Image Text

0 votesVote for this answer Mark as a Correct answer

Alistair DeJonge answered on February 24, 2017 21:49

Ok, but what is your Query?

0 votesVote for this answer Mark as a Correct answer

Peter Mogilnitski answered on February 24, 2017 22:02 (last edited on February 24, 2017 22:14)

in my case i just put something as a test.

select top 10 * from view_cms_tree_Joined where published =1 and ##WHERE##

0 votesVote for this answer Mark as a Correct answer

   Please, sign in to be able to submit a new answer.