TSQL - IN clause

There is nothing that annoys me like writing large, nested SQL queries. After adding a 7-th RIGHT OUTER JOIN I want to scream in pain. But on the other hand a few things in programming give me this instant feeling of accomplishment when a well written query returns what was expected (and in a good time too!)
I recently found this hack that made my life easier. It's one of those things I didn't know, and probably should have. It goes like this:

Instead of writing:

(...) WHERE field IN (1,2,3,4,5,6,7,8,9)

OR:

(...) WHERE field BETWEEN @start AND @end

it can be also achieved by:

(...) WHERE CHARINDEX( ',' + field + ',', ',' + @listOfValues + ',' ) > 0

It simply checks whether the value of 'field' is in the coma-separated list of values, that can be supplied as a parameter to a stored procedure.

The context (simplified): the stored procedure previously took two parameters - @start and @end and returned all records where 'field' was between @start and @end. Pretty common scenario. But after a while my cliend reqested that instead of a range of values, he wants to be able to specify an arbitrary list of values. Can be 1 value, but can as well be a 100 of them.

By constructing a coma separated list of values and submitting that list as a parameter to the stored proc I was able to minimize the amount of changes to the stored proc - basically I just needed to remove the @end parameter, and change the @start to @listOfValues. The code hack above did the rest of the work for me.

Performance issues? Using CHARINDEX is similar to using LIKE. In my case - where the expected max number of values in the list did not exceed 100, it is negligible.

Preventing applications from stealing focus

I just found a great Windows tip. I was dreaming about something like for a long time!
Here is where I found it.

Preventing Applications from Stealing the Focus

To prevent applications from stealing the focus from the window you are working

1. Start Regedit

2. Go to HKEY_CURRENT_USER \ Control Panel \ Desktop

3. Edit the key ForegroundLockTimeout

4. Give it a value of 00030d40

Disabling Edit/Delete Button in a GridRow

This simple short piece of code is so easy, yet, when I don't use it everyday I forget it, and then I have to look it up again and again. So I will just put it here and will never have to search for it.

When there is a need to hide the delete/edit button in a grid, on a row level, in the ItemDataBound handler, place the following line:

CType(e.Item.Cells(index).Controls(0), System.Web.UI.WebControls.LinkButton).Visible = False

Sysprep

I need to make a presentation. A technical one. Somebody suggested that I make one about LINQ. And it is a great idea. This linq thing seems pretty interesting. Anyway, it requires some prep work.
First of all, my laptop is running Windows XP. And that's bad. Well, I could test linq using visual studio 2005 (http://aspalliance.com/859), but that's missing part of the fun. So I decided to set up a virtual machine with windows 2003 server.
1. I was lucky enough to find a preinstalled windows 2k3 vmware template. So the only thing I had to do is ti install vmware server, and load the template.
2. Next step was to install Visual Studio 2008, code name ORCAS, beta 2. That of course can be downloaded from: http://msdn2.microsoft.com/en-us/vstudio/aa700831.aspx
3. In order to not let my work and time spent on it waste, I wanted to make a reusable virtual machine template that would have the vs2008 preinstalled. To do that I had to download something called WindowsXP-SP2-DeployTools-ENU.cab. For example from here: https://www.microsoft.com.nsatc.net/downloads/details.aspx?FamilyId=3E90DC91-AC56-4665-949B-BEDA3080E0F6&displaylang=en
4. I had to extract it inside the virtual machine, and run the 'sysprep.exe' tool.
5. The option I had to choose was 'reseal'
6. After shutdown, the virtual machine becomes a template.

Hello

Hello! Welcome to my blog. This is supposed to be the place where I post programming tips and tricks, interesting code snippets etc.

Whenever I have a programming problem that just can't be resolved, no matter how hard I try, there is always some blog out there that suggest a solution.

I hope my posts will someday help someone solve their code problem.

Afterall Internet is now just a huge 'social network' isn't it?