Friday, September 30, 2011

ASPxComboBox: Add button to clear selection

The Devexpress ASPxComboBox contains a Buttons collection to which you can add a number of buttons. By default the button will contain ellipsis (...) as Text but you can change this by using the Text property. You can also add an image like in the code below. In order to remove the selection you will need to add the clientsideevent button click and check for the buttonindex. If the buttonindex is 0 (the button will create will be the first on displayed), then set the selectedindex to -1, thus removing selection.


                                                            
                                                            
                                                            


Thursday, September 29, 2011

Linq Error - Specified cast is not valid

I was getting this error in the selecting event.  The problem was that for whatever reason, a field in the the LinqToSql file had a different datatype from the actual table in the database.  The field in the table was of type bigint while the same field in the datacontext was of type int.  This could have happened if for example the database schema was altered after having dropped the table in the LinqToSQL file.  Removing the table from the datacontext and re-dropping it solved the issue.

Anyone know how to refresh the LinqToSQL file without having to remove the table and reinserting it?  That would be great!

Monday, September 19, 2011

T-SQL: Get multiple values in CSV format

There are some instances when you need to retrieve a number of values in one column seperated by a comman.  Consider this set of data:


Category
Product
Pedals
LL Mountain Pedal
Pedals
Touring Pedal
Road Bikes
Road-150 Red
Road Bikes
Road-150 Black
Pumps
Mini-pump

And you would like to display it in the following manner:

Category
Product
Pedals
LL Mountain Pedal, Touring Pedal
Road Bikes
Road-150 Red, Road-150 Black
Pumps
Mini-pump


I have found the following 2 methods to be useful:

Create a function


CREATE FUNCTION [dbo].[GetProductsBySubcategoryID]

(

    @ProductSubcategoryID int

)

RETURNS varchar(max)

AS

BEGIN

    declare @output varchar(max)

    select @output = COALESCE(@output + ', ', '') + Name

    from Production.Product

    where ProductSubcategoryID = @ProductSubcategoryID



    return @output

END


Calling the function


SELECT Name, dbo.[GetProductsBySubcategoryID](ProductSubcategoryID)

FROM Production.ProductSubcategory

GROUP BY Name,ProductSubcategoryID




Pros: Neat, Easy to read/run.
Cons: Slower, You need to create a function for each scenario

Using XMLPath


SELECT SC.Name,

(

SELECT Name +','

from Production.Product as P

WHERE P.ProductSubcategoryID = SC.ProductSubcategoryID

ORDER BY P.Name

FOR XML PATH('')

)

from Production.ProductSubcategory as SC


Pros: Faster, No need to create a function
Cons: Not easily understandable

Regarding performace, on average the XML method returned 37 rows in 8ms and the former in 6ms.