Custom Query with JOIN

Greg Laycock asked on April 18, 2018 01:21

I don't typically write custom SQL queries, so please forgive the inefficiencies in the following test code. I'm trying to retrieve data from both a page type (table) and a custom table via a QueryRepeater. The following works correctly in SQL Management Studio and returns the data that I need, which is TireName, RebateType, RebateValue, and RebateDescription:

SELECT DISTINCT 
    Tires.TireName, 
    Rebates.RebateType,
    Rebates.RebateValue,
    Rebates.RebateDescription
FROM Tire_RegionalPromotionsRebates AS Rebates
INNER JOIN TireUSConsumer2017_Tire AS Tires ON Rebates.Tires = Tires.TireName
INNER JOIN Tire_RegionalPromotionsRegions AS Regions ON Rebates.Region = Regions.Region
WHERE PostalCode = '55555'

I added this query to the custom table (Tire.RegionalPromotionsRebates) in the CMS as follows:

SELECT DISTINCT
    Tires.TireName, 
    Rebates.RebateType,
    Rebates.RebateValue,
    Rebates.RebateDescription
FROM Tire_RegionalPromotionsRebates AS Rebates
INNER JOIN TireUSConsumer2017_Tire AS Tires ON Rebates.Tires = Tires.TireName
INNER JOIN Tire_RegionalPromotionsRegions AS Regions ON Rebates.Region = Regions.Region
WHERE ##WHERE##
ORDER BY ##ORDERBY##

And I'm calling this with the following QueryRepeater (which I'm binding in the code-behind):

<cms:CustomTableDataSource runat="server" ID="RegionalPromotionsTires" CustomTable="Tire.RegionalPromotionsRebates" />
<cms:QueryRepeater runat="server" ID="RegionalPromotionsTiresRepeater" QueryName="Tire.RegionalPromotionsRebates.RegionalRebateTires" WhereCondition="PostalCode='55555'">
  <ItemTemplate>  
    <%# Eval("TireName") %>
    <%# Eval("RebateType") %>
    <%# Eval("RebateValue") %>
    <%# Eval("RebateDescription") %>
  </ItemTemplate> 
</cms:QueryRepeater>

I'm having two problems with this. Most notably, this throws the error 'System.Data.DataRowView' does not contain a property with the name 'TireName'.

Secondarily, if I remove "TireName," the data (sans TireName) loads, but changing the WhereCondition does absolutely nothing. I can put numbers, letters, or anything there, or even remove it, and the QueryRepeater still returns the same values. Even hard-coding the WHERE in the custom query seemingly does nothing.

Your help is most appreciated.

Kentico 8.2.48 ASPX + Portal

Correct Answer

Peter Mogilnitski answered on April 18, 2018 03:45

Why do you have CustomTableDataSource? Repeater with custom query is enough.
Try to give names to your return columns i.e.:

SELECT Tires.TireName [TireName], Rebates.RebateType [RebateType], Rebates.RebateValue [RebateValue], Rebates.RebateDescription [RebateDescription] FROM ...

Is there any caching turned on by any chance? on data source or repeater?

0 votesVote for this answer Unmark Correct answer

Recent Answers

Greg Laycock answered on April 18, 2018 15:08

This is absolutely correct, Peter. I started with a CustomTableDataSource initially and switched to a custom query later, but I didn't think to remove the source. Once I removed that, everything worked correctly. I'm glad it was something simple. Thank you!

0 votesVote for this answer Mark as a Correct answer