Monday 22 March 2010

SQL Server: Deleting duplicates using a Common Table Expression (CTE)

In a recent project I had to figure out how to delete a series of duplicates from a tablet to prevent a report becoming bloated with them.

There's more than one way to skin a cat, but I think I have found a pretty efficient way of deleting dupes. Of course it would be preferable to avoid creating the duplicate rows in the first place, but sometimes you just have to delete them after they have been created.

A recent development in the SQL Server world is that of Common Table Expressions (CTEs). These can be used to help you delete duplicates using a single command, rather than using a temporary table or too many joins.

The CTE is formed by selecting the table into a temporary result set which you can then instantly relate back to the original table to find which rows to delete. You could use syntax similar to the following:

WITH Duplicates_CTE
(FirstName, LastName, Email, Phone, id)
AS (
SELECT FirstName, LastName, Email, Phone, MIN(PersonId) id
FROM tblPerson GROUP BY FirstName, LastName, Email, Phone
)
DELETE FROM tblPerson WHERE PersonId NOT IN (
SELECT id FROM Duplicates_CTE
)


The first select statement gets a distinct result set (without duplicate rows) from the problem table by using the MIN function and a GROUP BY clause. Then you can join back to the original table from the CTE result set and delete every row that does not correlate to the CTE - which means that you're left without any duplicate rows.

Et voila, simples! No duplicates and it's all done in one command.

Tuesday 16 March 2010

ASP.NET Code Behind Intellisense Not Working

This has been infuriating.

So many times when developing web applications using Visual Studio 2008 Enterprise Edition or Visual Studio Web Developer Express 2008, the intellisense in the code behind would stop working, and when the project is deployed any reference to page controls would result in a null reference exception.

There are quite a few things to try:

  • Close Visual Studio (Or VSWDE) and delete the intellisense files which are normally stored in your Windows profile.
  • Open the IDE again and rebuild the project.
  • Check that your aspx page is properly wired up to your code behind in the page directive.
  • Delete the project dll file in the bin folder and rebuild your project.
  • This is the one that worked for me: Check that there is a designer file associated with that page. If there is not, then in Visual Studio, right click on the project in Solution Explorer and click Convert to Web Application. That should regenerate the designer file for you.

If your intellisense isn't working and your code behind just can't seem to reference the controls, there are a lot of things that could be wrong. But if you try all of those, then hopefully you'll be back coding again instead of angrily trying to figure out what's wrong.

Friday 5 March 2010

Styling GridView Controls with CSS

Doing a lot of ASP.NET development usually means that GridView controls will be a regular feature in your applications, unless you prefer to go for the manual approach or use a repeater.

One of the biggest pains I've had in the past with GridViews is styling them consistently. But it wasn't enough of a pain to motivate me to spend the time styling them with CSS, until I started an entirely new application which would feature dozens of them.

So I located this tutorial which was absolutely perfect - just what I was looking for. It has a screen shot of the end result, good instructions and easily duplicated code and CSS that will get you on your way to having a consistent look and feel for all your GridView controls throughout your application.

Tuesday 2 March 2010

CSS min-height and IE6

I've been working on a new application which has to be fully compatible with IE6 and IE8. Being quite a fan of using min-height to avoid nasty squished pages, I noticed that the min-height CSS property is rendered properly in IE8, but NOT in IE6 (shock).

A nice, easy workaround I found is this:

content {
min-height: 630px;
height: auto !important;
height: 630px;
}


This works in IE6, without messing up the formatting in IE8 and other browsers.