Broken Search for Lookup

ASP.NET Maker General Discussion (ASP.NET Maker)
1

I have a lookup field (dropdown) in my table but when I enable searching (check allow sort/search) I receive an SQL error:

Invalid column name 'EV__EquipmentType'

I noticed the search was broken (incorrect syntax near from) for one of my pages. I traced it back in visual studio and I believe the error is sourced from:

// Get FROM clause for List page
public string SqlFromDerived
{
    get {
        if (!Empty(field))
            return field;
        string from = "(SELECT " + SqlSelectFields + ",  FROM [Import].[Equipment])";
        return from + " TMP_TABLE";
    }
    set {
        field = value;
    }
}

in the table.cs page (called when the table is using virtual fields).

I believe the comma before the FROM is extra/incorrect.

2

Double check your lookup field settings. From the demo project where Allow sort/search is enabled for the Trademark/Model fields, the generated codes look like:

public string SqlFromDerived
{
    get {
        if (!Empty(field))
            return field;
        string from = "(SELECT " + SqlSelectFields + ", (SELECT `Trademark` FROM trademarks TMP_LOOKUPTABLE WHERE TMP_LOOKUPTABLE.ID = cars.`Trademark` LIMIT 1) AS `EV__Trademark`, (SELECT `Model` FROM models TMP_LOOKUPTABLE WHERE TMP_LOOKUPTABLE.ID = cars.`Model` LIMIT 1) AS `EV___Model` FROM cars)";
        return from + " TMP_TABLE";
    }
    set {
        field = value;
    }
}