Use multiple columns in a databound drop down list

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 :)