Cannot sort ListGrid by clicking on a column when paging is turned on?

[user316]

Hi,

The paging is working normally when the listgrid first loads. However, when I click on column headers in my ListGrid to sort them, all but one of them result in a SQL error. An example of the SQL statement causing the error is below. After 'ORDER BY' there is a blank spot which should be the name of the field. This part of the SQL is auto-generated by SmartGWT as part of the paging. Do you know what could be causing it to be blank? This happens with all the fields of the datasource except one. Instead of being blank it should have RuleCheckName.

SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY ) AS rowID FROM (SELECT TOP 100 PERCENT
DRC_RuleCheckName as RuleCheckName,DRC_RuleCheckName,DRC_CellOrBlock as CellOrBlock,DRC_CellOrBlock,DRC_ResultCount as ResultCount,DRC_ResultCount,ISNULL(Rule_Categorization,'Uncategorized') as Rule_Categorization,
DRC_Number_Waived as Number_Waived,Block_Name,PK_Backend_Install,BI_Dir AS Backend_Install_Name,PK_Project,DRC_is_XOR_Run
FROM... [clipped]

RuleCheckName is the column I clicked on to enable sorting.

In the datasource it is defined as:

<field name="RuleCheckName" title="PV Check" tableName="checkTable" type="text" customSQL="true"/>

The SQL exception is the following, and like I mentioned is caused by the missing field after ORDER BY in the query.

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ')'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)

In my DSRequest RPC you can see how RuleCheckName is in the sortBy:

{
    dataSource:"BCG_PV_Runs", 
    operationType:"fetch", 
    operationId:"fetchDRC_BASEDetails", 
    componentId:"isc_BCMBackendAudit_19$1_0", 
    data:{
        operator:"and", 
        criteria:[
            {
                fieldName:"PK_Project", 
                operator:"equals", 
                value:720
            }, 
            {
                fieldName:"masterGridID", 
                operator:"equals", 
                value:"projectBEAudit"
            }, 
            {
            }, 
            {
                fieldName:"ServerGroupBy", 
                operator:"equals", 
                value:"[\n    \"Block_Name\", \n    \"ISNULL(Backend_Install_Name,'In Progress...')\"\n]"
            }, 
            {
                fieldName:"treeTitleField", 
                operator:"equals", 
                value:"ISNULL(Backend_Install_Name,'In Progress...')"
            }, 
            {
                fieldName:"Block_Name", 
                operator:"equals", 
                value:"dpd_top"
            }, 
            {
                fieldName:"Backend_Install_Name", 
                operator:"equals", 
                value:"2018_12_10.22_51"
            }, 
            {
                fieldName:"FK_Layout_Rundir", 
                operator:"equals", 
                value:"366144"
            }, 
            {
                fieldName:"WaiveState", 
                operator:"iNotEqual", 
                value:"Waived"
            }
        ]
    }, 
    startRow:0, 
    endRow:100, 
    sortBy:[
        "RuleCheckName"
    ], 
    textMatchStyle:"substring", 
    resultSet:[ResultSet ID:isc_ResultSet_3 (dataSource: BCG_PV_Runs, created by: isc_BCMBackendAudit_19$1_0)], 
    callback:{
        caller:[ResultSet ID:isc_ResultSet_3 (dataSource: BCG_PV_Runs, created by: isc_BCMBackendAudit_19$1_0)], 
        methodName:"fetchRemoteDataReply"
    }, 
    willHandleError:true, 
    showPrompt:false, 
    prompt:"Finding Records that match your criteria...", 
    oldValues:{
        summarySelection:[
        ], 
        summarySelectionGridID:null, 
        waiversAction:false
    }, 
    requestId:"BCG_PV_Runs_request112", 
    internalClientContext:{
        requestIndex:4
    }, 
    fallbackToEval:false, 
    promptStyle:"component", 
    dataPageSize:100, 
    dataFetchMode:"paged", 
    lastClientEventThreadCode:"TMR5", 
    bypassCache:true, 
    dataProtocol:"getParams"
}

[user316]

I figured out the problem. I needed to set customSQL="false". Also I had to set a customSelectExpression for this particular datasource field to get the query to work.