Repeater with custom query

Dušan Neckař asked on August 15, 2017 13:15

Hi all,

I am using Kentico 10 for my site and I want use repeater with custom query WITH clause WHERE. I want select * FROM DB WHERE value1 = 5 and value2 = 1995, but if some value is null select all values from their collumns.

Example: Query: Select TagID FROM database
Content filter -> Where condition: TagID = '{?Category?}' AND(date='{?Year?}')

But if category or year is NULL then clause logically returns an error ERROR...

I have idea about used second and third select but its really necessary?

Thanks in advance, Dušan.

Correct Answer

Peter Mogilnitski answered on August 15, 2017 16:25

Trevor has a nice one, but if it gets more complex, you probably need to do more k# scripting. The idea is to get general WHERE condition so if you have ?tagId=Car,Phone&year=2016,2017 it should translate into SQL WHERE TagID IN ('Car','Phone') AND Year IN (2016,2017)

{%
sqlWhere ="";
cond1 = string.IsNullOrEmpty(QueryString["TagID"]) ? "" : "'" + Replace(QueryString["TagID"], ",", "','") + "'";
cond1 = string.IsNullOrEmpty(cond1)? "" : "TagID in (" + cond1 + ")";
cond2 = string.IsNullOrEmpty(QueryString["Year"]) ? "" : " Year IN (" + QueryString["Year"] + ")";

if (cond1 !="" && cond2 !="") { 
  sqlWhere = cond1 + " AND "  + cond2 
}
else {
  sqlWHere = (cond1 == "" ? cond2 : cond1);
}
return sqlWHere
|(handlesqlinjection)false|(identity)GlobalAdministrator%}

P.S. You need to add handlesqlinjection in order to make you quotes work

2 votesVote for this answer Unmark Correct answer

Recent Answers

Trevor Fayas answered on August 15, 2017 13:56

usually you need to do an or in the query to say "this equals that or that equals nothing"

(TagID = '{%Category%}' or '{%category%}' = '') AND(date='{%Year%}' or '{%Year%}' = '')

1 votesVote for this answer Mark as a Correct answer

Dušan Neckař answered on August 15, 2017 14:16

Thanks much for reply, but if you have two dropdown and select only ONE?

For Example: Category - Car, Phone... Year - 2016,2017...

And you choose only Car, you will get value1 - 'Car' and second Value NULL and i want SELECT all record WHERE category = Car, without year... Now i´v got 0 record´s. Cause zero record is matching...

Probably i must use second SELECT for find all years and then clause WHERE Year IN MyArray?

0 votesVote for this answer Mark as a Correct answer