http://aspnetgoodies.wordpress.com/category/multiple-columns-in-data-bound-drop-down-list/
I got this idea from the above link; but my problem was that i had to get an aggregate function value and a normal column value and show them in my list e.g.
New (0)
Pending (13)
Old (3)
Discarded (50)
the above are the status types (New, Pending, Old, Discarded) and then (xyz) is the total number of applications which lie in this status. so basically two queries are used. One for selecting the application statuses, and the other from selecting the count of applications in the respective status.
So this is what I did:
Created a UDF to get the counts:
CREATE FUNCTION GetCountsForStatusType
(
-- Add the parameters for the function here
@statusTypeID Int
)
RETURNS int
BEGIN
Declare @myCountVar Int
select @myCountVar= count(appStatusId) from tblApplications where appStatusId = @statusTypeID and (AppProvince <> 'Ontario' or ( appStatusId =@statusTypeID and AppProvince = 'Ontario' and appCreateDate < '6/30/2009'))
Return @myCountVar
END
Then; I created a View:
Create View [dbo].[statusTypesWithCount]
As
SELECT statusTypeId,
statusTypeName + ' (' + convert(varchar(80),dbo.GetCountsForStatusType(statusTypeId)) + ')' AS statusTypeName
FROM dbo.tbAppStatusTypes
Then finally; I created a stored procedure with this query:
select * from statusTypesWithCount order by statusTypeId asc
and then bound the dropdownlist with this datasource; and chose DataTextField="statusTypeName"
There you go :)
