I have exactly the same problem as the user described in Thread 1476: I'm trying to use GetDBCount to set datagrid.VirtualItemCount, but it's not performing the correct DISTINCT on my typed list. That thread suggested using GetScalar instead, but TypedLists don't have that method. So, how am I supposed to get the correct row count for paging purposes??

Did you use the GetDbCount() overload which accepts a boolean for allowDuplicates? (and did you pass in false ?

Yes, I did. You can see the problem by looking at the sql generated (I left out the parms for clarity):

-- Fill the TypedList (using PageNumber and PageSize overloads)

CREATE TABLE #TempTable (
    [__rowcnt][int] IDENTITY (1,1) NOT NULL,
    [UserID][Int] NULL,
    [UserFirstName][VarChar](20) NULL,
    [UserMiddleName][VarChar](20) NULL,
    [UserLastName][VarChar](20) NULL);
INSERT INTO #TempTable SELECT DISTINCT [dbo].[Log].[UserID] AS [UserID],[dbo].[Log].[UserFirstName] AS [UserFirstName],[dbo].[Log].[UserMiddleName] AS [UserMiddleName],[dbo].[Log].[UserLastName] AS [UserLastName] FROM [dbo].[Log] WHERE ( [dbo].[Log].[ActionID] = @ActionID1) ORDER BY [dbo].[Log].[UserLastName] ASC,[dbo].[Log].[UserFirstName] ASC
SELECT * FROM #TempTable WHERE [__rowcnt] > 0 AND [__rowcnt] <= 10;

Query returns 4 rows

-- Use TypedList.GetDBCount()

SELECT DISTINCT TOP 1 COUNT(*) AS [UserID] 
FROM [dbo].[Log] 
WHERE ( [dbo].[Log].[ActionID] = @ActionID1) 

Query returns 5 as the count

The problem is that the table has an Identity primary key column. I get the correct DISTINCT result from the first query, which leaves that column out, but I get an extra row from the GetDBCount() query, because it's filtering on ALL rows instead of on the rows that are part of the TypedList. Technically, for custom DataGrid paging, things should work if I set

datagrid.VirtualItemCount = typedlist.Count

however, in some instances I found that TypedList.Count was returning incorrect results (for some reason if I set PageSize = 2 during fill, TypedList.Count came back as 2, even though there should have been 4 rows returned (maybe this is a different bug?).

Anyway, using TypedList.Count is working right now, but I don't feel that my paging is bullet-proof, and I hate to let things go out that way So, any help you can provide would be great.

I think it's a bug. It should use COUNT(DISTINCT *). I'll look into this tomorrow.

I appreciate the help. By the way, I goofed above when I said I could set

datagrid.VirtualItemCount = typedList.Count

When you use the PageSize overload in typedList.Fill, of course the count is always the page size, NOT the total number of items! D'oh!

So, all the more reason I need typedList.GetDBCount to work

heh

I think there is a slight problem. The thing is: COUNT(*) doesn't have a variant COUNT(DISTINCT *), at least not on SqlServer (and not on a lot of other databases as well).

There is another way to get the real count however. RelationCollection relations = myTypedList.BuildRelationSet(); ResultsetFields typedListFields= myTypedList.BuildResultset(); ResultsetFields aggregateFields = new ResultsetFields(1); // apply a countdistinct aggregate to the field you know is unique for every row, for example // field "CustomerID" fields["CustomerID"].AggregateFunctionToUse = AggregateFunction.CountDistinct; aggregateFields[0]=fields["CustomerID"];

TypedListDAO dao = DAOFactory.CreateTypedListDAO(); object scalarValue = dao.GetScalar(fields, null, filter, relations, null, true); int noRows = 0; if(scalarValue!=null) { noRows = Convert.ToInt32(scalarValue); }

(note: the GetDBCount() calls with allowDuplicate overload are a bit useless I now see. It was a last minute addition and as history already learned: don't add stuff at the last minute, sorry for that )

That did the trick: my paging is working just fine now. Thanks for the quick response.