SQL query fails

Sathish Kannan asked on June 2, 2016 15:37

Hi,

I'm running below query in ASCX transformation, but it is failing and shows error - CS1026: ) expected - on my page.

<%# DataSet ds = ConnectionHelper.ExecuteQuery("select count(*) as Count, DocumentName from CMS_Documents where typeofdocument LIKE '%dinnermenu%' AND documentregion LIKE 'Malaysia' Order by DocumentName", null, QueryTypeEnum.SQLQuery) %>

Please help

Correct Answer

Brenden Kehren answered on June 2, 2016 16:23

Instead of that, you might want to create this function to return a dataset. A better solution might be to use a Custom Transformation Method.

<script runat="server">
    public System.Data.DataSet getDocumentsDataset()
    {
        System.Data.DataSet ds = new System.Data.DataSet();
        ds = CMS.DataEngine.ConnectionHelper.ExecuteQuery("select count(*) as Count, DocumentName from CMS_Documents where typeofdocument LIKE '%dinnermenu%' AND documentregion LIKE 'Malaysia' Order by DocumentName", null, CMS.DataEngine.QueryTypeEnum.SQLQuery);
        return ds;
    }
</script>

1 votesVote for this answer Unmark Correct answer

Recent Answers

Sathish Kannan answered on June 2, 2016 17:35 (last edited on June 2, 2016 18:03)

Hi Brenden,

Thanks much for your solution. When I run this I'm getting "Invalid object name" error.

Scenario is, I have a folder named "Documents" under root, this "Documents" folder has children folders named "Dinnermenu", "Lunchmenu",etc., under this I have PDF files and I need to get the count of these, Below is my structure

---root
       |--Documents
                     |-- DinnerMenu
                                 |--PDF1
                                 |--PDF2
                     |--LunchMenu
                                 |--PDF1
                                 |--PDF2
                                 |--PDF3

And these PDF should be counted based on a document type, for example: under lunch menu we have 3 PDF files, among these 2 are "Starters" document type and 1 is "Main food" document type. So if I write query to get "LunchMenu" with "Starters" document type it should give me "2". Will it be achievable through macro? Can you help me.

0 votesVote for this answer Mark as a Correct answer

Sathish Kannan answered on June 2, 2016 20:57

Can any one help me on this?

0 votesVote for this answer Mark as a Correct answer

Joshua Adams answered on June 2, 2016 21:08

Sounds like a custom macro method or transformation method would be best suited here.

https://docs.kentico.com/display/K9/Adding+custom+methods+to+transformations

You can use brendens code above as well to run the query and get the results. Shouldn't be difficult. Depends on if you need to reuse this in other parts, or if this is a one time deal. If its one time, you can go with Brendens, as it will function the same, but if you need to use this in multiple places, or just like having things in app code, I would say go with the custom transformation method. Just pass in the nodeid, or path if you want to your custom function and then run your query and retrieve the count.

0 votesVote for this answer Mark as a Correct answer

Sathish Kannan answered on June 2, 2016 22:19

Thanks Brenden Kehren! Your solution works perfect.

0 votesVote for this answer Mark as a Correct answer