Aug 23, 2011

Passing jQuery MultiSelect values to an SQL Server Parameter


The Problem

There are various jQuery plugins out there that allow you to convert a simple dropdown list into a multi-select one. They all return either an array of the selected choices, or a comma-separated string like 'opt1,opt3'. We generally take this value and put it into a hidden field we can access on the server side and try and pass it to our store procedure or query as a parameter. All goes well till you find that the query/procedure doesn't give you the expected results anymore if it has the keyword "IN", and you're trying to filter your results based on your dropdown's selection.
When you pass a string 'Miami,Berlin,London' as a parameter, say @Cities, to a query like

SELECT Population FROM Cities WHERE Name IN @Cities

It will only produce the correct results if you select only one checkbox from the multiselect. The reason is SQL server needs a list to come after the “IN” keyword, and 'Miami,Berlin,London' is not a list, but a single string literal. The query will try and look for a single record where the city name is 'Miami,Berlin,London' as it currently looks like this:

SELECT Population FROM Cities WHERE Name IN ('Miami,Berlin,London')

What we need to send the query is this:

SELECT Population FROM Cities WHERE Name IN ('Miami','Berlin','London')

The Solution

We can create a simple table valued function that takes our comma-separated string and returns a list that the “IN” keyword can work with. Here’s a sample:

CREATE FUNCTION [dbo].[SplitListWithDelimiter](
@InputString VARCHAR(8000) 
, @Delimiter VARCHAR(2) = ',' 
) RETURNS @List TABLE (Item VARCHAR(1000))
 
BEGIN
DECLARE @Item VARCHAR(1000)
WHILE CHARINDEX(@Delimiter ,@InputString ,0) <> 0
BEGIN
SELECT
@Item=RTRIM(LTRIM(SUBSTRING(@InputString ,1,
CHARINDEX(@Delimiter ,@InputString ,0)-1))),
@InputString =RTRIM(LTRIM(SUBSTRING(@InputString ,
CHARINDEX(@Delimiter,@InputString ,0)+
LEN(@Delimiter ),LEN(@InputString ))))
IF LEN(@Item) > 0
INSERT INTO @List SELECT @Item
END
 
IF LEN(@InputString ) > 0
INSERT INTO @List SELECT @InputString 
RETURN
END

Now in our procedure or query, all we need to do is call this function and pass it our string:

SELECT Population FROM Cities WHERE Name IN
(SELECT Item FROM dbo.SplitListWithDelimiter(@Cities,','))

This will give us a result like this:
Item____
'Miami'
'Berlin'
'London'



Jul 26, 2011

Creating filter links for an ASP.Net MVC 3 List View


I’ve started dabbling with ASP.NET MVC 3 recently and have been following Jon Galloway’s “ASP.NET MVC Music Store Tutorial”. It’s a great place to start if you have little or no experience with ASP.NET MVC (like me).
The tutorial shows you how to create a music store application and has an administrative section that lets us add albums and classify them by genre. The code samples come with a substantial amount of seed data as well so I was a littler overwhelmed when I first created the landing page of the administration and saw a sprawling table and a huge vertical scroll.



I wanted to be able to filter down the list based on Genre because that’s what I base my collection of music on. So as my first experiment with MVC, I decided to put filter links on top and I may even put in paging and search features later and write posts about it to keep you updated Dear Reader. As of now, this is what is was aiming for:


To give you a little background details on the current implementation, I have an Album, Genre, and Artist class in my Model. The Genre class has a GenreID which I plan to use in my URL to filter the results that are displayed.
E.g.: If Classical has GenreID 1, the URL will look something like: /StoreManager/Index/1 and initially when no filter is applied, it will look like: /StoreManager
MVC follows a lot of “Convention over Configuration” so when you create an app with default route settings in Global.asax, the controller takes Index as the default action. My Index action presently looks like:










Now to filter this list based on GenreID, I’m going to do the following things:
  • Send a list of Genre to the View (To display the filter links)
  • Add Filter links to the View
  • Pass a parameter ID to the Index method which I’ll keep as nullable
  • Filter the list based on supplied ID (if present)
There are multiple ways you can send data from the controller to the view, I’m gonna using the ViewBag.
Then in my view index.cshtml (I’m using the razor view engine), I loop through the list to display my filter links:

I’ve used the ActionLink Html helper to display the links which show the Genre Name as the link text and redirect the user to the Index view with GenreID being passed to the Action as a parameter named ID. This would render a link like
/StoreManager/Index/1
Now in my Index method, I add a nullable integer parameter and filter the list based on the supplied value. My Index method now looks like:














Note the parameter declaration, the ? specifies that id is a nullable. As we may view the list without any filters, we must first check if id has a value and only then filter the list. Now when I browse to the URL StoreManager/Index/1, I see only classical albums and when I browse to /StoreManager I see all Genres.

Sep 27, 2010

Look what i found - Outlook lost its memory

I ran into an interesting thing while using MS Outlook at work today. I use Outlook 2007 but it seems outlook is either a female lying bout her age or it has lost its memory. I color marked an email today and in the header it said (colored by Outlook 2003 Red flag).


Anybody else notice this before?

Sep 22, 2010

One Year - Itz Goood

So i completed one full year at Cybage today. It's pretty much been awesome except a few (bad, major) hiccups.. and lately i've learnt to understand its not all my fault (it's an excuse, seabiscuit never used any).. To all those who've been close to me, you know how much i love that story and that horse and how much i use him as an excuse..

So life changed, lotsa horses runnin in front of me.. ran fast.. came ahead.. but laggin behind again.. I like to make myself believe i'm goin easy right now and lettim em all go ahead.. i'll be back into my own and surge ahead again for a photo finish.. It's now that i'm kinda realising that i mostly just ease up and run hard just a fraction of the time..

A promise is worthless if it's just a promise of greatness.. A lot of people have had faith in me and it's time i start learning the words "consistency", "motivation", "faith" and "ACTUALLY DELIVERING AND DOING WHAT I'M SUPPOSED TO".

Now there's a good chance that i'm not the real deal.. probably just a one hit wonder or something.. But tonight i'm not ready to accept that.. not gonna believe that.. it's always good to be reminded that you've hit some kinda milestone.. it reminds you of the big picture.. makes you think ahead.. motivates or depresses you.. but in the end it's your choice.. I still don't have any clear goal or plan.. but my dear friends.. i'm here to stay.. and i'm gonna start the sprint now.. so if you think this is the final stretch and i'm already out of the race think again.. i'm just starting to warm up.. hold on tight!!