带有自定义查询的Kentico中继器 [英] Kentico Repeater with Custom Query

问题描述

好的，我们开始.
使用Kentico 11/Portal Engine(无修补程序)

有一个包含仅内容"页面类型的表.重要的一个字段是日期和时间字段.

我正在尝试从该表中获取与特定月份和年份条件匹配的行.例如，给我所有记录，其中Month=2 and Year=2018.这些参数将通过查询字符串传递

我有一个自定义的存储过程，我想接收两个int(或字符串)参数，然后返回所有匹配行的集合.


我正在使用RepeaterWithCustomQuery来调用该过程并处理结果行.如您在下面看到的，querystring参数分别命名为"year"和"monthnumber".

查询

OK Here we go.
Using Kentico 11/Portal Engine (no hot fixes)

Have a table that holds Content only page Types. One field of importance is a Date and time field.

I am trying to get rows out of this table that match a certain month and year criteria. For instance give me all records where Month=2 and Year=2018. These argument will be passed via the query string

I have a custom Stored proc that I would like to receive two int(or string) arguments then return a collection of all matching rows.


I am using a RepeaterWithCustomQuery to call the procedure and handle the resulting rows. As you can see below the querystring arguments are named "year" and "monthnumber".

The Query

Me.PR.PREDetailSelect

在此配置中设置我的Webpart时，出现以下错误:

在我的查询中，我尝试过:

Me.PR.PREDetailSelect

When my Webpart is set up in this configuration I get the following error:

In my Query, I have tried:

EXEC Proc_Custom_PRDetails @MonthNumber = ##ORDERBY##; @Year = ##WHERE##<br/>
EXEC Proc_Custom_PRDetails @MonthNumber = ##ORDERBY##, @Year = ##WHERE##<br/>
EXEC Proc_Custom_PRDetails @MonthNumber = ##ORDERBY## @Year = ##WHERE##<br/>

任何帮助将不胜感激(提前感谢Brendan).最后，当我尝试更改名称以保护无辜者时，不要太着迷于特定对象的名称.

Any help would be appreciated (Thanks in advance Brendan). Lastly, don't get too caught up in the names of specific objects as I tried to change names to protect the innocent.

推荐答案

那些用于查询的宏不应与stor proc一起使用.如果您不传递任何内容，系统将生成此错误条件1 = 1，以免破坏如下所示的sql语句:

Those macros for queries are not meant to be used with stor procs. The system generates this false condition 1=1 in case if you don't pass anything so it won't break the sql statement like the one below:

SELECT ##TOPN## ##COLUMNS##
FROM View_CMS_Tree_Joined AS V
INNER JOIN CONTENT_MenuItem AS C
ON V.DocumentForeignKeyValue = C.MenuItemID AND V.ClassName = N'CMS.MenuItem'
WHERE ##WHERE##
ORDER BY ##ORDERBY##

您需要将stor proc转换为SQL语句，然后才能使用这些 SQL宏或使用不带参数的stor proc

You need to convert you stor proc to SQL statement then you can use these SQL macros or use stor proc without parameters

如果查看上面的查询 top 和 where 不好，因为系统会进行调整，但是您可以使用 order by 和列，但是它们都必须存在(我认为它会原样通过它们):

If look at the query above top and where are not good because system will do adjustment, but you can use order by and columns, but they both must be present (I think it passes them as is):

exec proc_test ##ORDERBY##, ##COLUMNS##

老实说，我建议您不要这样做，而且通过调用stor proc不会给您带来什么好处.

Honestly I would advice against doing this, plus you won't gain much by calling stor proc.

这篇关于带有自定义查询的Kentico中继器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！