Datatable editor where condition

Pignattino

Hello I'm writing a controller with a where condition that links a column id from main query to another taken in where condition with an external query as shown:

Dim response = New Editor(db, "OrdiniGiornaliero", "IDOrdine").Model(Of JoinModel)() _
                                .Field(New Field("OrdiniGiornaliero.IDNodoPartenza") _
                                        .Validator(Validation.Numeric())) _
                                .Field(New Field("OrdiniGiornaliero.IDNodoArrivo") _
                                        .Validator(Validation.Numeric())) _
                                .Field(New Field("OrdiniGiornaliero.UDC") _
                                        .Validator(Validation.Numeric())) _
                                .Field(New Field("OrdiniGiornaliero.QLI") _
                                        .Validator(Validation.Numeric())) _
                                .Field(New Field("OrdiniGiornaliero.Priorita")) _
                                .Field(New Field("OrdiniGiornaliero.Note")) _
                                .Field(New Field("OrdiniGiornaliero.id_org")) _
                                .Field(New Field("OrdiniGiornaliero.IDstoricoordini")) _
                                .Field(New Field("OrdiniGiornaliero.ConsegnaEntro") _
                                        .GetFormatter(Format.DateSqlToFormat("dd/MM/yyyy HH:mm")) _
                                        .SetFormatter(Format.DateTime("dd/MM/yyyy HH:mm", "yyyy-MM-dd HH:mm:ss"))) _
                                .Field(New Field("OrdiniGiornaliero.RitiroDopo") _
                                        .GetFormatter(Format.DateSqlToFormat("dd/MM/yyyy HH:mm")) _
                                        .SetFormatter(Format.DateTime("dd/MM/yyyy HH:mm", "yyyy-MM-dd HH:mm:ss"))) _
                                .Field(New Field("OrdiniGiornaliero.CodiceSocieta")) _
                                .Field(New Field("OrdiniGiornaliero.CentroAddebito")) _
                                .Field(New Field("OrdiniGiornaliero.CentroDiCosto")) _
                                .Field(New Field("OrdiniGiornaliero.TrasportatoreRichiesto")) _
                                .LeftJoin("Nodi as NodoPartenza", "NodoPartenza.IDNodo", "=", "OrdiniGiornaliero.IDNodoPartenza") _
                                .LeftJoin("Nodi as NodoArrivo", "NodoArrivo.IDNodo", "=", "OrdiniGiornaliero.IDNodoArrivo") _
                                .Where("OrdiniGiornaliero.IDstoricoordini", "(SELECT TOP 1 CAST(idstoricoordini AS INT) FROM storicoordini WHERE stato_ordine='in elaborazione' ORDER BY idstoricoordini DESC)", "=") _
                                .Process(request) _
                                .Data()

                Return Json(response)

But I got this error:
"Conversion failed when converting the nvarchar value '(SELECT TOP 1 CAST(idstoricoordini AS INT) FROM storicoordini WHERE stato_ordine='in elaborazione' ORDER BY idstoricoordini DESC)' to data type int."

Why??

allan

If you want to use a sub-select you need to use a closure function - as described in the documentation here.

Allan

Pignattino

I've tried with a sub-query with IN enclosure but no way...

allan

Can you show me what you tried please and any errors you received if there were errors.

Allan

Pignattino

Where("OrdiniGiornaliero.IDstoricoordini", "(SELECT TOP 1 idstoricoordini FROM storicoordini WHERE stato_ordine='in elaborazione' ORDER BY idstoricoordini DESC)", "IN")

I don't have any boolean option as last parameter

The error is always the same as written before.. conversion failed to int...

allan

Can you show me the closure / lambda function you used? I hadn't realised you were using the .NET libraries - this is the relevant documentation.

Allan

Pignattino

Yes I'm using .net library... I wrote my code in VB.net... as shown in original post..
I started from manual's page

allan

Can you do an lambda in VB.NET? You absolutely need to do that as it gives you access to the Query.Where method which provides the forth parameter.

Allan

Pignattino

I've used an external parameter instead of inner query :)