Nested Query Repeater

lawrence whittemore asked on March 19, 2014 22:00

I seem to be getting 1=1 for my where condition on a nested query repeater.

My query looks like this

 SELECT *
  FROM LWS_Specialty
##WHERE##

My transfomration looks like this

<cms:QueryRepeater ID="Specialties" runat="server" QueryName="lws.specialty.SpecialtyListForProvider" TransformationName="lws.specialty.list" ></cms:QueryRepeater>

  <script runat="server">
  protected override void OnInit(EventArgs e)
    {
      base.OnInit(e);
      Specialties.WhereCondition= "WHERE ItemID in(" + Eval("Specialty") + ")";
      Specialties.ReloadData(true);
    }
</script>

nested query repeater

Recent Answers

lawrence whittemore answered on March 19, 2014 22:01

If it helps this is within a repeater. I am in version 7.0.79.

I also have on the same site a nested repeater within a query repeater and it works just fine.

0 votesVote for this answer Mark as a Correct answer

lawrence whittemore answered on March 19, 2014 22:03

If I remove the ##WHERE## it pulls all the data however, if I leave the ##WHERE## but remove the script i get the same 1=1 for the where condition.

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 19, 2014 22:27

Remove the WHERE from your statement. Specialties.WhereCondition= "ItemID in(" + Eval("Specialty") + ")";

0 votesVote for this answer Mark as a Correct answer

lawrence whittemore answered on March 19, 2014 23:12 (last edited on March 19, 2014 23:12)

Doesn't make a difference. I think it needs the where because it is a query repeater and not just a repeater.

0 votesVote for this answer Mark as a Correct answer

lawrence whittemore answered on March 19, 2014 23:20

If I change add this WhereContion="test" to this <cms:QueryRepeater ID="Specialties" runat="server" QueryName="lws.specialty.SpecialtyListForProvider" TransformationName="lws.specialty.list" ></cms:QueryRepeater>

it spits out (test) and (where itemid in(10))

0 votesVote for this answer Mark as a Correct answer

lawrence whittemore answered on March 19, 2014 23:28

I think it's a bug... I changed it to use Specialties.orderby and change the ##WHERE## to ##ORDERBY## and I have it working...

0 votesVote for this answer Mark as a Correct answer

lawrence whittemore answered on March 19, 2014 23:33

Full Transformation that works.

<div class="provider">
  <!-- Image -->
  <%# IfEmpty(Eval("Image"),"","<div class=\"provider-image\"><img src=\"" + Eval("Image") + "\" /></div>") %>
  <!-- /Image -->

  <div class="provider-info">
<h3><a href=" <%# GetDocumentUrl() %>"><i class="fa fa-caret-right"></i> <%# Eval("FullName") %></a></h3>
<%# IfEmpty(Eval("Title"),"","<h4>" + Eval("Title") + "</h4>") %>
<%# IfEmpty(Eval("Email"),"","<p><strong>Email:</strong> <a href=\"mailto:" + Eval("Email") + "\" >" + Eval("EMail") + "</a></p>") %>
<%# IfEmpty(Eval("WorkPhone"),"","<p><strong>Office Phone:</strong> " + Eval("WorkPhone") + "</p>") %>
<%# IfEmpty(Eval("MobilePhone"),"","<p>Mobile Phone:</strong> " + Eval("MobilePhone") + "</p>") %>
<%# IfEmpty(Eval("ShortBio"),"","<p>" + Eval("ShortBio") + "</p>") %>    
    <p><strong>Specialties:</strong> <cms:QueryRepeater ID="Specialties" ItemSeparator=", " runat="server" orderby="test" QueryName="lws.specialty.SpecialtyListForProvider" TransformationName="lws.specialty.list" ></cms:QueryRepeater></p>
    <div class="clearfix"></div>

  </div>
    <div class="clearfix"></div>
</div>
  <script runat="server">
  protected override void OnInit(EventArgs e)
    {
      base.OnInit(e);
      Specialties.OrderBy = "WHERE ItemID in(" + IfEmpty(Eval("Specialty"),"0",Eval<string>("Specialty").Replace("|",",")) + ")";
      Specialties.ReloadData(true);
    }
</script>

0 votesVote for this answer Mark as a Correct answer

lawrence whittemore answered on March 19, 2014 23:34

My query

SELECT *
  FROM LWS_Specialty
##ORDERBY##
order by Name ASC

0 votesVote for this answer Mark as a Correct answer

Brenden Kehren answered on March 20, 2014 13:26

I can guarantee you the "WHERE" statement is not needed in your string to set the WhereCondition property. Also, you might try placing your query on the same line like so: SELECT * FROM LWS_Specialty ##WHERE## ##ORDERBY## I know it sounds petty but it has caused problems for me in the past.

Have you tried running the debugger within Kentico to see what the actual query output is? That will give you your answer if this doesn't work.

0 votesVote for this answer Mark as a Correct answer