Query Repeater With Effect - Invalid SQL query in property "wherecondition" error

profound Team asked on October 9, 2018 16:02

Hello,

I'm having a couple issues with my query repeater with effects where condition. When trying to use a macro to inject a formatted string im getting this error "Invalid SQL query in property 'wherecondition'"

My where condition is:

{%"FAQ_QuestionID IN (SELECT DocumentForeignKeyValue FROM [dbo].[CMS_Document] WHERE DocumentGUID IN ({" + FormatSQLIDList(CurrentDocument.GetValue("EU_FAQ1")) + "))"|(handlesqlinjection)false#%}

My custom macro "FormatSQLIDList" just takes a pipe seperated list and outputs it as '1','2','3' etc.. so its SQL Friendly. The output of my above macro FormatSQLIDList(CurrentDocument.GetValue("EU_FAQ1")) is '1050c015-a9bf-4f47-befd-13ffe62806a7','9a9c591e-f2f1-4594-97d5-4ebe27a9d2e6'

When I take that output and copy it in my where condition in place of my custom macro is works as expected.

Is there something im missing when using custom macros / using page fields in a where condition ?

Correct Answer

profound Team answered on October 9, 2018 18:14

I have solved this now.

My bad, I forgot to remove that bracket after trying something else, thank you for pointing that out that but that wasn't what was causing my issue.

To fix this I had to cast my GetValue result to a string, so "FormatSQLIDList(CurrentDocument.GetValue("EU_FAQ1").ToString())"

Thank you for taking a look :)

0 votesVote for this answer Unmark Correct answer

Recent Answers

Dragoljub Ilic answered on October 9, 2018 16:16 (last edited on October 9, 2018 16:18)

Hi,

I'm not sure if this is the main reason why issue occurs, but it looks like you have one open curly bracket '{' extra, right after round bracket '(':

...DocumentGUID IN ({" + FormatSQLIDList...

Best regards, Dragoljub

0 votesVote for this answer Mark as a Correct answer