Search code examples
sqlsql-serverentitydatasource

EntityDataSource CommandText Count Error

This simple piece of sql works perfect in sql server...

Select it.Id as Id, it.Status as Status,
    (SELECT COUNT (*) FROM PickLocations as pl WHERE pl.PickBatchItemId = bi.Id) AS Counts
FROM PickBatches AS it
INNER JOIN PickBatchItems as bi ON it.Id = bi.PickBatchId

Gives me...

Id, Status, Counts
-------------------
1    1       2
2    1       0
3    2       2

But after making a change so that it wont complain...

Select it.Id as Id, it.Status as Status,
    (SELECT COUNT (pl.Id) FROM PickLocations AS pl WHERE bi.Id == pc.PickBatchItemId) AS Count
FROM PickBatches AS it
INNER JOIN PickBatchItems as bi ON it.Id == bi.PickBatchId

when I use this in a EntityDataSource CommandText I get...

Id, Status, Counts
-------------------
1    1       System.Collections.Generic.List`1[System.Data.Common.DbDataRecord]
2    1       System.Collections.Generic.List`1[System.Data.Common.DbDataRecord]
3    2       System.Collections.Generic.List`1[System.Data.Common.DbDataRecord]

Obviously missing something simple...

UPDATE

Problem solved by Vikram.

Using select value count() and getting the index of the count with Count[0]

Solution

  • Adding VALUE to the nested SELECT returned a list of Int32 instead.

    Binding like this, returned the value:

    Count='<%# Eval("Count[0]") %>'